Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations JAE on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Find and Replace with wildcards 1

Status
Not open for further replies.

gouveia

Mechanical
Joined
Jul 21, 2003
Messages
61
Location
US
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
 
="0" & A1 [or the cell that contains your data]
 
Don't use find and replace. Use string manipulation functions in VBA instead.



 
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"@

 
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
 
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
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top