Find and Replace with wildcards 1

[gouveia]

I have a simple task of adding the number zero "0" infront of a long list of labels (text) in excel. The example is as follows:

5AAA5 --> 05AAA5
5BBB5 --> 05BBB5
5CCC5 --> 05CCC5

Of course I can do a simple find and replace for the number '5' but I don't want to replace the last 5 in the series. I can also do a find and replace for '5????' but when I put '05???' in the replace box it replaces everything with ???. Any ideas? Got a macro to do this?

Thanks for your help in advance, Mark

 

[melone]

="0" & A1 [or the cell that contains your data]

 

[MintJulep]

Don't use find and replace. Use string manipulation functions in VBA instead.

 

[ab123456]

You could create a custom format to avoid having to create a new column on your sheet.

Select all cells you want to change, select format >> cells >> custom and enter 0#;0#;;"0"@

 

[gouveia]

Thanks everyone for their responses. To make life simple I took Melone's '="0" & A1' and then did a copy / paste as value back over the old values. Done deal.

Thanks again, Mark

 

[jhardy1]

OK, I am posting this suggestion after you already found a workable solution, but I am going to post my standard answer to so many of these requests:

Install a free copy of ASAP Utilities from

http://www.asap-utilities.com

This brilliant free Excel plug-in has dozens (hundreds?) of useful add-in tools, including "ASAP Utilities | Text | Insert before current value". Simply select the block you are interested in, find the appropriate ASAP Utility, enter the character(s) you want to insert, and click on OK. Done!

Hope this helps!