How to Correctly Sort Irregular Column

**Diesel10** · 11-03-2011, 12:13 PM

I am having a problem sorting an irregular column.

I am using Excel 2003. There is a header row

My column is as follows (not incl the header row)

LX1
LX10
LX21
LX27
LX33
LX34
LX35
LX4
LX6

I sorted using the cells and as you can see, it sorted it incorrectly.

Should be

LX1
LX4
LX6
LX10
etc

I tried
LX-1
LX-10
LX-4

but that made no difference.

I believe that I have to define the cells in the column as custom but I am unsure how to do this so the sort will be done correctly. By the way, there are even occasionally going to be some entries in the column such as LX10a or LX10Aa. The main thing is to get the sort generally correct and I can put up if these rare extra suffixes are not sorted correctly.

I am relative new to Excel. Thanks for assistance! Jim

**Marcol** · 11-03-2011, 12:41 PM

One way.

With your data in A2 down
In B2

=SUBSTITUTE(A2,LOOKUP(99^99,--("0"&MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW($1:$100)))),TEXT(LOOKUP(99^99,--("0"&MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW($1:$100)))),"00"))

Then sort on Column B

If the number in your string can exceed 99 then increase the number of zeros in the TEXT() arguement. e.g. "000" will handle up to 999.

Hope this helps.

**Diesel10** · 11-03-2011, 01:26 PM

Originally Posted by Marcol

One way.

With your data in A2 down
In B2

=SUBSTITUTE(A2,LOOKUP(99^99,--("0"&MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW($1:$100)))),TEXT(LOOKUP(99^99,--("0"&MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW($1:$100)))),"00"))

Then sort on Column B

If the number in your string can exceed 99 then increase the number of zeros in the TEXT() arguement. e.g. "000" will handle up to 999.

Hope this helps.

I tried Marcol but I must be doing something wrong. With my data starting on row 2 column A (row 1 is the headers), If I put the formula in B2, won't that delete the value in that cell? Anyway, I pasted that formula into B2 cell and sorted and it didn't seem to make any difference. I think you are assuming I know a lot about Excel, but unfortunately I don't. Please spell it out a bit more clearly. Thanks so much for your help!

Here is a complete copy of the rows and columns to be sorted :
(It is my stamp collection). First sort by column A and then by Column B
State Cat #
AK L19
AK L20b
AK W5
AK W5
AZ B13
AZ B19a
AZ B21
AZ B23
AZ B24
AZ B26
AZ B28
AZ B29
AZ B4
AZ B6
AZ B8
AZ FE1
AZ FE4
AZ FE5
AZ FE6
AZ FE7
AZ LX1
AZ LX10
AZ LX21
AZ LX27
AZ LX33
AZ LX34
AZ LX35
AZ LX4
AZ LX6

**Marcol** · 11-03-2011, 01:41 PM

Please don't quote whole posts in your replies, the moderators don't like it ...

You would be best to post a sample of your workbook so we can see where to put the helper column, and not have to guess as to what your typical data looks like.

Did you download and try the workbook I posted in Post #2?

From Forum FAQs
How do I attach a file to a post?

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

**Diesel10** · 11-03-2011, 02:02 PM

I solved the problem by manually changing LX4 to LX004 and LX10 to LX010 etc. I need three digits because some catalog numbers are 100 and over.

Doesn't look nearly as nice but at least the stamps are sorted in the correct order.

**Marcol** · 11-03-2011, 02:11 PM

The formula I gave you does what you did manually!

Just put it in a spare column drag it down and then copy and paste values to Column A if that is what you are happly with.

=SUBSTITUTE(A2,LOOKUP(99^99,--("0"&MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW($1:$100)))),TEXT(LOOKUP(99^99,--("0"&MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW($1:$100)))),"000"))

will handle numbers up to 999.

**Diesel10** · 11-03-2011, 02:33 PM

You are very kind to help me. I only had 30 stamps enteredt so far so it wasn't difficult to manually change them and from here on out, I'm with the program in entering them that way...

Thanks again so much! It was your example that showed me the placeholder concept. Bravo !