I have several thousand rows address data, concatenated in a single field. When i try to sort i get: 103 LOWREY AVE S 104 CHURCHILL DR 104 LOWELL ST N 104 MELROSE ST 105 ALISON AVE 105 ANGELA CRES 105 CARMINE CRES 105 SALTSMAN DR 106 ARGYLE ST N Is there a way to sort by the street name, then number without breaking apart the data in the single address field?

Reputation: 119

Sorting Address data in google sheets

I have several thousand rows address data, concatenated in a single field. When i try to sort i get:

Is there a way to sort by the street name, then number without breaking apart the data in the single address field?

Upvotes: 0

Reputation: 50697

=SORT(A1:A9,
    INDEX(SPLIT(A1:A9," "),,2),1,
    A1:A9,1)

SORT A1:A9 on two sort - criterias:
- SPLIT Col A by space and choose second column with INDEX
- If the street names are same, Sort by A1:A9

Upvotes: 1

Reputation: 59485

Sort of, for your example - because each street name is unique, hence sorting by street number would not affect the order anyway:

=ArrayFormula(sort(mid(A1:A9,5,50)))

Upvotes: 1