Solved: Sorting Alphanumerics and Extracting numbers from them.

[Prema]

Hi All,
I have some immediate requirements.
Can anyone please tell me how to sort alphanumerics range in Excel. The sort should be, first alphabets to be sorted followed by accordinly the numbers should
be sorted.
For Example,
If I have a alphanumeric range in Excel like below
A
ABC-0010 ABC-0015
CAT-000009
ZIP-00000050
CAT-0000014
MAX-0000003800
ABC-0001
ZIP-00000039
ABC-0007
CAT-000002
MAX-0000003600
ABC-0006
MAX-0000003550
ZIP-00000001
MAX-0000003750
ZIP-00000074
CAT-000021

B
ABC-0015
CAT-0000015
ZIP-00000075
CAT-000020
MAX-0000003900
ABC-0005
ZIP-00000045
ABC-0009
CAT-0000008
MAX-0000003700
ABC-0006
MAX-0000003599
ZIP-00000038
MAX-0000003799
ZIP-00000080
CAT-000030



The above columns should be sorted just like shown below,Where column A contains start of the range and column B contains End of the range. And both of the columns should be
sorted at a time.

A

ABC-0001 ABC-0005
ABC-0006
ABC-0007
ABC-0010
CAT-000002
CAT-000009
CAT-000014
CAT-000021
MAX-0000003550
MAX-0000003600
MAX-0000003750
MAX-0000003800
ZIP-00000001
ZIP-00000039
ZIP-00000050
ZIP-00000074

B

ABC-0005
ABC-0006
ABC-0009
ABC-0015
CAT-000008
CAT-000015
CAT-000020
CAT-000030
MAX-0000003599
MAX-0000003700
MAX-0000003799
MAX-0000003900
ZIP-00000038
ZIP-00000045
ZIP-00000075
ZIP-00000080








If there are spaces, that range should be added at the end of all.And if tcere are decimal numbers it should consider though.

2. How do I extract numbers from alphanumerics, including decimals.

3. How do I return more than one value using functions in VBA?


Using Excel 2007 .

Your immediate reply is highly appeciated.

Thanks,
Prema

[Aussiebear]

Are the first three values in the string always going to be alphabetical?

[Prema]

N

[Prema]

No, Aalphabets can be of any range.

Many Thanks,
Prema

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim rng As Range
Dim cell As Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = .Range(.Range("A1"), .Range("A1").End(xlDown))
Do While rng.Cells(rng.Rows.Count, 1).Row < LastRow

Set cell = rng.Cells(1, 1)
rng.Sort key1:=cell, order1:=xlAscending, Header:=xlNo
If rng.Cells(rng.Rows.Count, 1).Row < LastRow Then

Set rng = rng.Offset(rng.Rows.Count + 2).Cells(1, 1)
Set rng = .Range(rng, rng.End(xlDown))
End If
Loop
End With

End Sub
[/vba]

[Prema]

Thanks a lot.
Can you also tell me how do I solve my next two problems?
It will be great help.

[Bob Phillips]

Extract numbers, use this formula

=MID(A1,FIND("-",A1)+1,99)

[Bob Phillips]

On the third point, tell us what the data looks like, what the function will do, and so on.

[Prema]

I am sorry ...

[Bob Phillips]

What are you doing that in jects all of this html rubbish in your posts? It is unreadable.

[Prema]

Sorry and Thanks again,

What if the range doen not contains '-'. How do I extract then?
Is there any way to subtract alphanumerics?
I need to extract numbers from alphanumerics and have to perform some
numeric calculation on them. This must be done for each different
types of ranges independently. First for ABC ... followed by CAT...
and so on.

[Bob Phillips]

You can use this formula

=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

[Prema]

Thanks

[Prema]

I have one more immediate question.
I have tried sort Code. But this sorts only Column A but not not column B accordingly.

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 Original and sorted lists should look like:
Original
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

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.
Prema

[Bob Phillips]

Try this

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim rng As Range
Dim cell As Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = .Range(.Range("A1"), .Range("A1").End(xlDown)).Resize(, 2)
Do While rng.Cells(rng.Rows.Count, 1).Row <= LastRow

Set cell = rng.Cells(1, 1)
rng.Sort key1:=cell, order1:=xlAscending, Header:=xlNo
If rng.Cells(rng.Rows.Count, 1).Row <= LastRow Then

Set rng = rng.Offset(rng.Rows.Count).Cells(1, 1)
Do While rng.Cells(1, 1).Value2 = "" And rng.Cells(rng.Rows.Count, 1).Row <= LastRow

Set rng = rng.Offset(1).Cells(1, 1)
Loop

Set rng = .Range(rng, rng.End(xlDown))
End If
Loop
End With

End Sub
[/vba]

[Prema]

It works fine.
Thanks a lot.

[Prema]

It works fine.
Thanks a lot

[Prema]

How do I attach document files here?

[GTO]

Greetings Prema,

Instead of using the Quick Reply box, click the Go Advaced button. Scroll down a bit, and you will see the Manage Attachments button. From there it is pretty self-explanatory.

If you have a couple of files to attach, zip them first, as only one attachment is allowed per post, and of course, the zips save space for the site.

Hope that helps,

Mark

[Prema]

Thank You.