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.
--
Lisa W.

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. wrote:
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 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 W.


"Jason" wrote:

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. wrote:
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.

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.

EditPaste SpecialValuesOKEsc

Delete Column A.


Gord Dibben MS Excel MVP

On Tue, 4 Aug 2009 08:23:01 -0700, Lisa W.
wrote:

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 W.


"Jason" wrote:

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. wrote:
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.