Sorting numeric text as numbers in Excel

I have a column of real estate case identifiers in a spreadsheet.  The format of the case id's can differ, but they all mix numeric and alpha characters.  A sample column of id's might look like the following:

12-3
12-4
12-34
12-5
12-45
2013CA235
12-672CO

As a first step, I want to eliminate the alpha characters from each id and then sort the list as numbers.  Any ideas?
jeff20850Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tel2Commented:
Hi Jeff,

When you say "...eliminate the alpha characters...", do you mean "...eliminate the non-numeric characters..."?  Alpha (alphabetics) are just letters (a-z & A-Z), but I see you have '-'s also.

Also, what version of Excel are you using?

tel7
0
aikimarkCommented:
This function will do a text to number conversion.
Public Function Text2Num(ByVal parmText As String)
    Static oRE As Object
    Static strTemp As String
    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
        oRE.Pattern = "\D"
    End If
    strTemp = parmText
    If oRE.test(strTemp) Then
        strTemp = oRE.Replace(strTemp, "")
    End If
    Text2Num = strTemp
End Function

Open in new window


Put it in a module in your VB project.  Then you can invoke the function as you would any other function.
Example:
=text2num(A1)

Open in new window

Your sample data is in column A and the function result in column B.
12-3        	123
12-4        	124
12-34      	1234
12-5        	125
12-45      	1245
2013CA235	2013235
12-672CO	12672

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamCommented:
do it with no UDF just with array formulas

extract numeric from alphanumerics and then sort with formula.

see attached file. very useful array formulas
C--Users-Jamil-Desktop-Book1.xlsx
0
jeff20850Author Commented:
Answered my question exactly. This function is really the first step in my overall question, so I'll correcpond with the replier to ask some follow-up questions.
0
aikimarkCommented:
post a new question, including a link to this thread and then post a comment in this thread that lets us know the URL of the new related question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.