Excel Help

October 30, 2012 12:00 AM

Excelfilter: How do I find and replace (e.g. replace 17 for 18) a set of numbers in the format: 17-01-06 without it being recognised as a date?

Currently the set of numbers are formatted as text (but other formattings didnt help)

posted by freddymetz to Computers & Internet (4 answers total)

=REPLACE(17,1,18) will work if you are always changing the left most characters.

Alternatively if your still stuck you can use "text to columns" to break the date string into three new variables representing day month and year. You can then use any combination of sort, find and replace to help clean the day variable.
posted by roofus at 12:13 AM on October 30, 2012

Best answer: Extremely easy and somewhat hacky: In the find and replace field, append a ' (single quote) before the 18 (So you're finding 17 and replacing with '18). That'll force excel to keep it as text and not convert it to a date. This only works because you're trying to find and replace a string at the beginning of the string.

Quick and easy: Say your data is in column A and starts at row 2. Make a new column B and place the following formula in it: ="18"&RIGHT(A2, 6) . Drag the formula to the bottom, then copy and paste as value into column A. Delete column B. Done.
posted by Mons Veneris at 12:16 AM on October 30, 2012

Copy paste the column into a plain text editor, find+replace, copy paste back into Excel.
posted by viggorlijah at 12:40 AM on October 30, 2012 [1 favorite]

Response by poster: Thanks guys! For my particular sheet (every number had a barcode generated by a formula directly above it) the "hack" with the single quote did the job most efficiently!
posted by freddymetz at 1:20 AM on October 30, 2012