Find and Replace

[Lisa W.]

I'm working on a spreadsheet with APN#'s. I need to change it from seven
38-21-200 characters to ten, something like this 038-21-20000. Any
suggestions on a wildcard to add a zero before and zeros after? Thanks.

 

[Jason]

Hi Lisa,

Assuming your APN# is in cell A1 the following will change it to 10
digits, as requested.

="0"&A1&"00"

Simply concatenate a zero to the beginning of the text string and two
zeros to the end.

HTH....Jay.

 

[Lisa W.]

Hi Jason,
Thanks for your help. So if I understand it right, this would be the
replace. How would I type in the "find" to get all of these replaced? Thanks
for your help.

 

[Gord Dibben]

Lisa

Not a find/replace function.

Jason posted a formula that would be entered in a helper column.

Enter ="0"&A1&"00" in B1

Double-click on the fill handle of B1 to copy down.

When happy, select column B and copy.

Edit>Paste Special>Values>OK>Esc

Delete Column A.


Gord Dibben MS Excel MVP