Numerical sorts with mixed characters

  • Thread starter Thread starter Walt
  • Start date Start date
W

Walt

I need to separate the numeric and alpha parts of the street address
number, but I am not sure how to use the Excel functions to accomplish
this. Most street numbers have no alpha part of their street number.
Then the format for A, B, C, D, … can be entered by many people with
different formats; example 101 Any Main Street that has the apartment of
business could have 101A, 101-B, and 101 C Any Main Street. Now add
that the alpha can be anything; one to five or six characters, and some
of these characters can be numbers. In the data base, there are street
numbers in the units, hundreds and 10,000’s, and these need to be sorted
in numerical value so that 7A is first, 101 C is second, 10475 is last.
Are there any functions that identify a numeric character, or id an
alpha character? What suggestions can to help with? Thank you.


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
One way to extract the leading digits:

=--LEFT(A1,-1+MATCH(TRUE,ISERROR(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

But I'm not sure that goes far enough to actually help!
 
Back
Top