Format serie of numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Norm Gregoire - 10 Feb 2007 13:38 GMT

I need the following number to look as follows:
Is: A1 D01-3
Want: A01 D01-3

I need to add a zero after the A to 3000 entries without changing the rest.
Can it be done?
Thanks
Norm

Reply to this Message

Mike - 10 Feb 2007 14:02 GMT

Assuming your first number is in A1 enter this in B1 and drag down

=LEFT(A1,1)&"0"&+MID(A1,2,7)

> I need the following number to look as follows:
> Is: A1 D01-3
[quoted text clipped - 4 lines]
> Thanks
> Norm

Reply to this Message

andy62 - 10 Feb 2007 14:05 GMT

If it's the only "A" in the text you could just use the Replace function, and
replace "A" with "A0"

> I need the following number to look as follows:
> Is: A1 D01-3
[quoted text clipped - 4 lines]
> Thanks
> Norm

Reply to this Message

daddylonglegs - 10 Feb 2007 14:47 GMT

With a formula in another cell.....assuming your data in cell B2

=REPLACE(B2,2,0,0)

> If it's the only "A" in the text you could just use the Replace function, and
> replace "A" with "A0"
[quoted text clipped - 7 lines]
> > Thanks
> > Norm

Reply to this Message

Teethless mama - 10 Feb 2007 14:44 GMT

=SUBSTITUTE(A1,"A","A0")

copy down as far as needed

> I need the following number to look as follows:
> Is: A1 D01-3
[quoted text clipped - 4 lines]
> Thanks
> Norm

Reply to this Message

Norm Gregoire - 12 Feb 2007 16:18 GMT

Thanks to all,
I used the REPLACE function for this problem. Back in business!

> I need the following number to look as follows:
> Is: A1 D01-3
[quoted text clipped - 4 lines]
> Thanks
> Norm

Reply to this Message