Doing a search and replace is

[laredotornado]

Hi,

I'm using Excel 14.4.4 on Mac 10.9.5. I have an .xls file and one of the columns has cell values like so ...

dob_062601

I want to remove the "dob_" in front of each value, but at the same time, preserve any leading zeroes that may be left over. When I do a search and replace for the "dob_" string, replacing it with an empty string (""), the leading zeroes disappear. Any idea how I can preserve them?

 

[Rick Rothstein]

dob_062601

I want to remove the "dob_" in front of each value, but at the same time, preserve any leading zeroes that may be left over. When I do a search and replace for the "dob_" string, replacing it with an empty string (""), the leading zeroes disappear. Any idea how I can preserve them?

Try using an apostrophe (') in the "Replace with" field instead of the empty string.

 

[laredotornado]

Try using an apostrophe (') in the "Replace with" field instead of the empty string.

Hi,

I think I'm not explaining what I want to do well. I have cells taht look like

dob_060201

and I want the final value to be

060201

How do I do that? I don't want extra apostrophes in there, I just want that "dob_" string removed and the leading zeroes preserved.

 

[kuldokk]

Hi,

I think I'm not explaining what I want to do well. I have cells taht look like

dob_060201

and I want the final value to be

060201

How do I do that? I don't want extra apostrophes in there, I just want that "dob_" string removed and the leading zeroes preserved.

You can try the following formula if your data is in Cell A4:

=RIGHT(A4;LEN(A4)-FIND("_";A4))

If you have difficulty applying the formula, try to replace ";" with "," in the formula

=RIGHT(A4,LEN(A4)-FIND("_",A4))

The FIND function find the index of "_" character in your text, which is located in cell A4. The RIGHT function will take a substring from the right with a number of characters.

Hope that work for you.

 

[laredotornado]

Thanks for this. What if I want to apply this to an entire column's worth of data? Also, I'm submitting the XLS file to an application that processes it, and I'd like taht application to read the values in the cell as "######" as opposed to read a formula. Would applying the formula you suggsted achieve that?

 

[Rick Rothstein]

I don't want extra apostrophes in there

You won't see it and Excel will ignore it in any formulas that use the cell... the leading apostrophe will be a signal to Excel to treat the value as Text even though the cell is formatted as General.

 

[kuldokk]

Thanks for this. What if I want to apply this to an entire column's worth of data? Also, I'm submitting the XLS file to an application that processes it, and I'd like taht application to read the values in the cell as "######" as opposed to read a formula. Would applying the formula you suggsted achieve that?

Try this instead:

=IFERROR(RIGHT(A4;LEN(A4)-FIND("_";A4));"error")