Find and Replace

  • Thread starter Thread starter Lisa W.
  • Start date Start date
L

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.
 
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.
 
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.
 
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
 
Back
Top