# Sorting problem

1. ## Sorting problem

How can I sort the data on the left to become on the right? Do I have to manually fill in "0" for A1-A9 to become A01-A09?

A1 A1
A10 A2
A11 A3
A2 A4
A3 A5
A4 A6
A5 A7
A6 A8
A7 A9
A8 A10
A9 A11  Register To Reply

2. ## Sorting Problem

Hi,

Yes, you are right. you need to change "A1 - A9" to "A01 - A09". Then only we can sort these as required.  Register To Reply

3. Thank you, Vinay. Is there a way to insert "0" using formula?

Mark  Register To Reply

4. If the data is as simple as you show, then you can custom format the cells as "A"0 and not worry about the leading zeros; Excel will only see the numbers for sorting purposes.  Register To Reply

5. Hi shg,

The example is an abbreviated version. The actual data contains from A0 through A123. There are a lot of "0" and "00" I need to add.

Thanks.
Mark  Register To Reply

6. Sure. Select the cells of interest, then do:

Format > Cells > Number > Custom, and enter "A"0 under Type.

Then in some cell in an out-of-the-way column (starting in, say, K1),

=VALUE(MID(A1,2,100) replacing A1 in the formula with the cell reference where the "A1", "A2", ... appear, and copy this formula down to capture all of the "A" numbers.

Then copy the cells in the new column, and do Edit > PasteSpecial > Values over the top of the "A1", "A2", ... values. Then delete or clear the extra column.

Now those values are just numbers with an "A" in the front, and they will sort in normal fashion.

Capiche?  Register To Reply

7. Hi shg,

Got it! Only one minor deviation. When formatting the cells, I need to key in "A"#, instead of "A"1.

Thank you very much.
Mark  Register To Reply

8. You're welcome.
My suggestion was "A"0  Register To Reply

9. ## Sorting Problem

Hi 10036760,

Please try this also. Suppose you have data in A coumn. Enter number of maximum digits in E1 and enter the below formula in B column to get the result as shown below:

=LEFT(A1,1)&REPT("0",E\$1-LEN(A1))&RIGHT(A1,LEN(A1)-1)

A1 A0001 Maximum Digits 5
A2 A0002
A3 A0003
A4 A0004
A5 A0005
A6 A0006
A7 A0007
A8 A0008
A9 A0009
A10 A0010

This will insert all the required 0.  Register To Reply