Sorting problem

**10036760** · 10-01-2007, 08:25 PM

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

**vinaysri11** · 10-02-2007, 01:01 AM

Hi,

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

**10036760** · 10-02-2007, 03:06 PM

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

Mark

**shg** · 10-02-2007, 03:52 PM

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.

**10036760** · 10-02-2007, 04:46 PM

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.

Could you clarify your suggestion?

Thanks.
Mark

**shg** · 10-02-2007, 05:53 PM

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?

**10036760** · 10-02-2007, 06:40 PM

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

**shg** · 10-02-2007, 06:51 PM

You're welcome.

... instead of "A"1

My suggestion was "A"0

**vinaysri11** · 10-04-2007, 01:44 PM

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.

Sorting problem

LinkBack

Thread Tools

Rate This Thread

Display

Sorting problem

Sorting Problem

Sorting Problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions