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-
Sorting ignoring numbers
-
-
-
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. -
Re: Sorting ignoring numbers
that only seems to remove the numbers...i just want it to not sort first by the number buy by the letters
like all the 'A' streets together regarless of street number?? -
Re: Sorting ignoring numbers
never mind...its too big
-
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 here is a small sample of the data.
maybe this will help -
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
can i change the formula to just get the number part aswell?
-
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. -
-
Re: Sorting ignoring numbers
Yes everything helped.
Thanks ShosMeister and Batman for all your help.
I can always count on this site and its awesome!!
whoot! -
Re: Sorting ignoring numbers
VBA solution
Code
Display MoreSub test() Dim a, x, i a = Range("a1").CurrentRegion.Value ReDim Preserve a(1 To UBound(a, 1), 1 To 2) For i = LBound(a, 1) To UBound(a, 1) x = Val(a(i, 1)) x = Replace(a(i, 1), x, vbNullString) a(i, 2) = UCase(Trim(x)) Next QuicksortA a, LBound(a, 1), UBound(a, 1), 2 Range("a1").Resize(UBound(a, 1)) = a End Sub Sub QuicksortA(ary, LB, UB, ref) Dim M As Variant, temp Dim i As Long, ii As Long, iii As Integer i = UB ii = LB M = ary(Int((LB + UB) / 2), ref) Do While ii <= i Do While ary(ii, ref) < M ii = ii + 1 Loop Do While ary(i, ref) > M i = i - 1 Loop If ii <= i Then For iii = LBound(ary, 2) To UBound(ary, 2) temp = ary(ii, iii): ary(ii, iii) = ary(i, iii) ary(i, iii) = temp Next ii = ii + 1: i = i - 1 End If Loop If LB < i Then QuicksortA ary, LB, i, ref If ii < UB Then QuicksortA ary, ii, UB, ref End Sub
-
Re: Sorting ignoring numbers
that works excellent as well!
Now i have lots of options!
thx guys!
kristen
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!