Changing A1-1-1 to A01-01-01 so on and so on

[jamobe]

Hello

I've got a spreadsheet and I've entered many cells with data in this format A1-1-1, A10-2-10, B11-11-2 etc etc.

These are locations on shelves so A1-1-1 to A1-1-12 represents one shelf, my problem is that when I sort the column it ends up like this.

A1-1-1
A1-1-10
A1-1-11
A1-1-12
A2-1-2
.
.
A2-1-9


Is there a formula or some kind of format I can use so I can put a zero at the beginning of the single numbers? Also I would prefer the format to be A1-1-1 but I just need Excel to know that a 1 is a 01 and not 10.

Any help would be much appreciated

 

[Special-K99]

This is an Excel solution

Presumably this is a one-off to make each number consist of two digits (if they don't already).
Then try this.

Assuming data is in column A

1. in B1 =LEFT(A1,1) and copy down the column
2. in C1 =RIGHT(A1,LEN(A1)-1) and copy down the column
3. Select the entire column C
4. Copy and paste Special - Values over the top of column C
5. Select column C
6. Use text to columns with a delimiter of -
You now have the 3 numbers separated into columns C D and E
7. in F1
=B1&TEXT(C1,"00")&"-"&TEXT(D1,"00")&"-"&TEXT(E1,"00")
and copy down the column.
8. Select column F
9. Copy and Paste values over the top of column F
10. Delete columns A-E.
Column F is now in column A and is formatted with each number consisting of 2 digits.

 

[james_lankford]

if this
A1-1-1
is cell A1

then put this
=SUBSTITUTE(A1,"-","-0")
in B1

and put this
=SUBSTITUTE(B1,"00","0")
in C1

and put this
=LEFT(C1,6) & RIGHT(C1,2)
in D1

I'm sure there is a MUCH easier way of doing it, but this seems to work

 

[Special-K99]

if this
A1-1-1
is cell A1

then put this
=SUBSTITUTE(A1,"-","-0")
in B1

and put this
=SUBSTITUTE(B1,"00","0")
in C1

and put this
=LEFT(C1,6) & RIGHT(C1,2)
in D1

I'm sure there is a MUCH easier way of doing it, but this seems to work

This doesn't change the first digit (next to the A), e.g. A1-1-1
I'm assuming the OP means change that one as well since the quote was

"so I can put a zero at the beginning of the single numbers"
and that's obviously a single number

However, if the OP doesn't want that digit changed then that's a much quicker solution!

 

[jamobe]

Thank you so much!!

That worked perfectly special-K99, didn't try your way James but thanks for the reply as I did need A1 to be changed to A01. This has saved me manually changing 1000's of these rows and I can't thank you guys enough!!

 

[Rick Rothstein]

Thank you so much!!

That worked perfectly special-K99, didn't try your way James but thanks for the reply as I did need A1 to be changed to A01. This has saved me manually changing 1000's of these rows and I can't thank you guys enough!!

I think this formula outputs what is wanted...

=SUBSTITUTE(SUBSTITUTE(IF(MID(A1,3,1)="-",REPLACE(A1,2,0,0),A1),"-","-0"),"00","0")

 

[Tetra201]

Assuming your numeric fields do not exceed 59, try this:

=LEFT(A1)&TEXT(MID(SUBSTITUTE(A1,"-",":"),2,99),"[hh]-mm-ss")

 

[Rfigueroa2009]

Hello

I've got a spreadsheet and I've entered many cells with data in this format A1-1-1, A10-2-10, B11-11-2 etc etc.

These are locations on shelves so A1-1-1 to A1-1-12 represents one shelf, my problem is that when I sort the column it ends up like this.

A1-1-1
A1-1-10
A1-1-11
A1-1-12
A2-1-2
.
.
A2-1-9


Is there a formula or some kind of format I can use so I can put a zero at the beginning of the single numbers? Also I would prefer the format to be A1-1-1 but I just need Excel to know that a 1 is a 01 and not 10.

Any help would be much appreciated

Hello im having the same issue but with mine its only a1-1 and i need double digit for numbers . Can you help me me ?

 

[Sanjeev1976]

Hello im having the same issue but with mine its only a1-1 and i need double digit for numbers . Can you help me me ?

You can use Rick's solution mentioned in post#6 i.e.

=SUBSTITUTE(SUBSTITUTE(IF(MID(A1,3,1)="-",REPLACE(A1,2,0,0),A1),"-","-0"),"00","0")

 

[Rfigueroa2009]

You can use Rick's solution mentioned in post#6 i.e.

=SUBSTITUTE(SUBSTITUTE(IF(MID(A1,3,1)="-",REPLACE(A1,2,0,0),A1),"-","-0"),"00","0")

Im not good At all with excel . Will this solution work even if my format is shorter . ? A1-1
If not what do i remove from solution