Adding zero prefix to no's

[Guest]

I have 500 cells typed as 1234567/1.
But I need them to read as 01234567/01
(8 digits at the beginning, but the first digit must be a zero - followed by
a slash - followed by 2 digits).

Any suggestions how I can do this automatically?

 

[Max]

One way ..

Assuming the data is in A1 down

Put in B1:

=0&LEFT(A1,SEARCH("/",A1)-1)&"/"&0&MID(A1,SEARCH
("/",A1)+1,99)

Copy down

 

[Dave Peterson]

I'd use a helper column with a formula (dragged down):
Are they all 7 characters in the first portion?

if yes:
="0"&SUBSTITUTE(A1,"/","/0")

If no:
=RIGHT(REPT("0",8)&SUBSTITUTE(A1,"/","/0"),11)

You can always copy|paste special|values to convert to values, then delete the
original column.

 

[Guest]

Thanks Max
I can get the zero at the beginning to work, but not the 2 digit number at
the end (after the slash) - any suggestions on just a formula for this?

Regards
C

 

[Max]

But it seems to work ok here under testing ?

It's presumed that the source data will have a single digit after the slash

So if you have in A1:A3

1234567/1
1234567/2
1234567/3

B1:B3 will return as:

01234567/01
01234567/02
01234567/03

Perhaps you could clarify the understanding above, and/or paste some samples
of the source data and the expected results ?