Sorting ignoring numbers

  • Hey i dont know if i even need code to do this, or if im just tired and the answer is obvious...
    i need some help sorting addresses.
    so for example
    123 Arlington
    56 Blair st
    i would like it to ignore the number and just sort by the text. is that possible?
    well i guess anything is possible to you guys.
    thx.
    K-

  • Re: Sorting ignoring numbers


    Newt,


    I don't think you'll be able to directly use Sort on that data in Excel. Assuming your data is in cell A1 down, you can create formulas in column B on which to sort, using the formula


    =IF(ISERROR(VALUE(LEFT(A1,1))),A1,MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1)))


    Hope this helps.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Sorting ignoring numbers


    Newt, what he's saying is to use that formula to create a cell/column that has just the street name in it. Then, when you sort, you use that column instead of A1.

  • Re: Sorting ignoring numbers


    but it doesn't just have the street name in it..
    sorry wait column b has just the street name...sorta but column a still has the whole address...so how am i supposed to sort that?

  • Re: Sorting ignoring numbers


    Okay, slow down Newt.


    The formula that Batman provided has one purpose: To strip off the leading number value. What's left is the street name and everything after it right?


    Now, when you sort, you select both columns and sort on column B. That will automatically move the corresponding cells in A.


    Make sense?

  • Re: Sorting ignoring numbers


    I wasn't trying to seem snappy...and i wasn't trying to be mean.
    I can't write code like that at all and i appreciate when you guys help me...
    its just that the formula doesn't seem to be working for my addresses because there is no space between some of the street numbers and street names.
    i understand what you are saying though about the sorting...

  • Re: Sorting ignoring numbers


    Didn't think you were Newt. Just didn't want you to get too frustrated :) :)


    The issue with some of your data not fitting the format is a problem. There's an old addage in DB parlance: GIGO - Garbage In Garbage Out. Basically, it means that if your data isn't accurate that you can't depend on any output that you produce from it.


    If you can't change the data to be more consistent with the way addresses should be written, there may be a way of modifying Batman's formula to make it work.


    Can you change the data?

  • Re: Sorting ignoring numbers


    okay its probably me thats making it this difficult.
    i have a set of data with about 19,000 addresses given to me by another department. their data was all screwed up and i needed to compare it to the data that we have to see if any of the addresses matched up.
    our data has separate columns for street number and street name and their data is all together in one column. so i concatenated my data in order to match it up with theirs, hence why the number is right beside the street name. there is about 40,000 rows of my data.
    So what i want to do is compare the data together and this was my best idea.
    any good ideas?
    ps i want to strangle the other department...hahah :rambo:

  • Re: Sorting ignoring numbers


    I would suggest you work to make thier data look like yours as it will make the comparison part much easier. If you can reformat the data using the method that Batman suggested to strip off the numeric part, that should be a good start. Then you can just create a new sheet with the data formatted the way you need. Once there, sorting and comparison should be relatively easy.

  • Re: Sorting ignoring numbers


    Well, it's not very elegant but .....


    B is Batman's formula. Notice, as you have, the errors that are encountered when there is no space.


    C is simply counting until it finds a non numeric character and places that value in the cell. That should be the character position where the first letter of the street name starts.


    D is the extraction of the number.


    E is the extraction of the name.

  • Re: Sorting ignoring numbers


    Sorry to have left this to you guys for so long.


    It's not very elegant, but the following will extract the street name in a single formula:


    =TRIM(MID(A1,MIN(IF(ISERROR(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},A1)),256,FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},A1))),256))


    Hope this helps.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Sorting ignoring numbers


    VBA solution