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

 

[Sanjeev1976]

Like this :

Book7
	A	B
1	A1-1	A01-01
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(SUBSTITUTE(IF(MID(A1,3,1)="-",REPLACE(A1,2,0,0),A1),"-","-0"),"00","0")

 

[Rfigueroa2009]

Like this :

Book7
	A	B
1	A1-1	A01-01
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(SUBSTITUTE(IF(MID(A1,3,1)="-",REPLACE(A1,2,0,0),A1),"-","-0"),"00","0")

Hello that works only problem im running to now that once i get to a1-10 it formats it to A01-010. And i will like to keep it to 2 digits all the way down . And i will end my numbers at a01-99

 

[Rick Rothstein]

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

Give this formula a try...

=LEFT(A1)&TEXT(MID(A1,2,2-(FIND("-",A1)=3)),"00-")&TEXT(MID(A1,FIND("-",A1)+1,9),"-00")

 

[Rfigueroa2009]

Give this formula a try...

=LEFT(A1)&TEXT(MID(A1,2,2-(FIND("-",A1)=3)),"00-")&TEXT(MID(A1,FIND("-",A1)+1,9),"-00")

Gives me a #value . Error

 

[Rfigueroa2009]

Gives me a #value . Error

I got it !!!!! Thank you !!

 

[Rfigueroa2009]

Give this formula a try...

=LEFT(A1)&TEXT(MID(A1,2,2-(FIND("-",A1)=3)),"00-")&TEXT(MID(A1,FIND("-",A1)+1,9),"-00")

Thank you it worked perfect