Solved: Sorting Alphanumerics and Extracting numbers from them.

Hi All,:hi:
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

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

N

No, Aalphabets can be of any range.

Many Thanks,
Prema

[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]

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

Extract numbers, use this formula

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

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

I am sorry ...

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

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.

You can use this formula

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

Thanks

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

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]

It works fine.
Thanks a lot.

It works fine.
Thanks a lot

How do I attach document files here?

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

Thank You.

Hi again,
Here have attached a sample requirements,
Can anyone please review this and help me to arrive at some solutions.

It will be great.

Many Thanks & Regards,
Prema

Cross-posted at XtremeVBTalk http://www.xtremevbtalk.com/showthread.php?t=313866.

And I thought we had solved it?

Sorry, but I cannot see the solution hint.
Can you please re - post the solution for above attachment?
It helps a lot.
Many Thanks,
Prema

Prema,

If you feel the need to post the same question on other forums, please post the link to the other forum. Show some respect to those people who have been assisting you here. Members here have been going out of the way to assist you here in this forum and this is how you want to treat them.....

If you have any other forums working on this issue, now's a good time to post the cross link to them.

I think Colin Legg, the moderator on the other forum, encapsulated it nicely.

Having reviewed the threads on both sites it seems to me that you have been given all the information you need to complete your assignment. If you want further help then you are going to have to demonstrate some effort on your part rather than demanding immediate, tailored solutions: explain what you have tried, post the relevant code you wrote and give a full description of the problems you encountered.

Plus of course, the sheer bad manners of getting multiple people working separately on your problems, as if the OP is the only one that matters.

Very nicely put by Mr Legg

Sorry for the confusion.

I am new to VBA and to forums. Here onwards If I need help from others I will post the cross link to them.

Sorry again,
Prema