Adding zero prefix to no's

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?

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Cheryl W" <Cheryl [email protected]> wrote in message
news:[email protected]...
> 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?

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.



Cheryl W wrote:
>
> 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?

--

Dave Peterson

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


"Cheryl W" wrote:

> 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?

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 ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Cheryl W" <Cheryl [email protected]> wrote in message
news:[email protected]...
> 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?