Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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 <atyahoo<dotcom ---- "Cheryl W" <Cheryl wrote in message ... 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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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 <atyahoo<dotcom ---- "Cheryl W" <Cheryl wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUM or COUNT and adding a row | Excel Worksheet Functions | |||
how do i prefix a column with letters | Excel Discussion (Misc queries) | |||
Adding a prefix to a cell by using a formula | Excel Discussion (Misc queries) | |||
Adding to a range in a reference? | New Users to Excel | |||
How do I enter a prefix number to a colum of numbers | Excel Worksheet Functions |