Excel 2007 : Sorting Alphanumerics and Extracting numbers from them.

**Prema Prabhu** · 02-15-2010, 04:51 AM

Hi All,
Can anyone please tell me is there any way to sort alphanumerics range in Excel. The sort should be, first alphabets to be sorted followed by accordinly the numerics should be sorted.

For Example,
If I have a alphanumeric range in Excel like below
A B
ABC-0010 ABC-0015
CAT-000009 CAT-000015
ZIP-00000050 ZIP-00000083
CAT-000014 CAT-000020
MAX-0000003800 MAX-0000003900
ABC-0001 ABC-0005
ZIP-00000039 ZIP-00000045
ABC-0007 ABC-0009
CAT-000002 CAT-000008
MAX-0000003600 MAX-0000003700
ABC-0006 ABC-0006
MAX-0000003550 MAX-0000003599
ZIP-00000001 ZIP-00000038
MAX-0000003750 MAX-0000003799
ZIP-00000074 ZIP-00000080
CAT-000021 CAT-000030
ABC-0016 ABC-0020
ZIP-00000085 ZIP-00000090
MAX-0000003450 MAX-0000003550
CAT-000035 CAT-000040
ABC-0007 ABC-0021
ZIP-00000080 ZIP-00000095

Two columns A & B are related and they should be sorted at a time. For example After Sorting if ABC-0001 at the 1st row of column A then ABC-0005 should be at in 1st row of column B and so on. column A is always start range and column B is end of the range.
So my sorted lists should look like:
And the sorted lists
ABC-0001 ABC-0005
ABC-0006 ABC-0006
ABC-0007 ABC-0009
ABC-0007 ABC-0021
ABC-0010 ABC-0015
ABC-0016 ABC-0020
CAT-000002 CAT-000008
CAT-000009 CAT-000015
CAT-000014 CAT-000020
CAT-000021 CAT-000030
CAT-000035 CAT-000040
MAX-0000003450 MAX-0000003550
MAX-0000003550 MAX-0000003599
MAX-0000003600 MAX-0000003700
MAX-0000003750 MAX-0000003799
MAX-0000003800 MAX-0000003900
ZIP-00000001 ZIP-00000038
ZIP-00000039 ZIP-00000045
ZIP-00000050 ZIP-00000083
ZIP-00000074 ZIP-00000080
ZIP-00000080 ZIP-00000095
ZIP-00000085 ZIP-00000090

Column A should be sorted first. Then column B should be sorted according to A. For more detail highlighted examples.
This is immediate requirement , So your quick reply is highly appreciated.
Thanks,
Prema

**DonkeyOte** · 02-15-2010, 05:23 AM

Why can't you just sort both columns by Column A & Column B ? (in that order)

Doing that with your sample data would generate the desired results you outline (with exception of ZIP-00000083/80 which appear to be the wrong way around in your desired results (col B))

**Prema Prabhu** · 02-15-2010, 08:17 AM

Thanks a lot.
I have some more requirements.
I need to extract numbers from alphanumerics and have to perform some numeric calculation on them. This must be done for each rows in a different types of ranges independently. First for ABC ... followed by CAT... and so on.
In my below example first I need to extract numbers from range ABC-. This alphabets can be of any range (ABC is just an example) and it may include spaces and decimals like ABCGH - 0001 OR
ABC-0001(No space) OR ABCGH -0001.6 etc. After extracting the numbers how do I proceed If i need to find the repeated ranges like ABC-0007 to ABC-0009,
ABC-0007 to ABC-0021.This range may OR may not include '_'. I also need to find the difference between the ranges like ABC-0006 AND ABC-0007etc.The same procedure applies for next alphanumerics. I also need to display beginning and ending range for all alphanumerics i.e ABC-0001 TO ABC-0020 for range ABC AND CAT-000002 TO
CAT-000040 for CAT etc
ABC-0001 ABC-0005
ABC-0006 ABC-0006
ABC-0007 ABC-0009
ABC-0007 ABC-0021
ABC-0010 ABC-0015
ABC-0016 ABC-0020
CAT-000002 CAT-000008
CAT-000009 CAT-000015
CAT-000014 CAT-000020
CAT-000021 CAT-000030
CAT-000035 CAT-000040

Can anyone help me in this regard.
It will be a great help.
Prema

**DonkeyOte** · 02-15-2010, 08:34 AM

Your post is I'm afraid hard to follow (at least for me).

Post a sample file outlining all the possible variations of your strings and also outline the desired results based on those sample values (calculated manually of course).

To attach a file, in your reply click the paperclip icon - if you can not see it click "Go Advanced" and proceed from there.

**Prema Prabhu** · 02-15-2010, 10:56 AM

Attachment 65514Here have attached my sample requirements.
Can anyone please help in this regard.
Many Thanks & Regards,

**DonkeyOte** · 02-15-2010, 01:59 PM

I see no "expected results" in your sample file.

**Prema Prabhu** · 02-16-2010, 04:55 AM

I have attached modified sample requirements.
Can anyone please review this and help me to arrive at some solutions.
It will be great.
Thanks,

**dominicb** · 02-19-2010, 09:23 AM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

http://www.xtremevbtalk.com/showthread.php?t=313866