How to sort by ascending address (Street, house Number) in Google sheets?

https://docs.google.com/spreadsheets/d/1Kc6WBBdoWi...

Hello! You can't sort the address list in ascending order in Google sheets.
We get the following:

Lenina 1
Lenina 19
Lenin 2
22 Lenina
Lenina 4
Lenina 42

But you need to:

Lenina 1
Lenin 2
Lenina 4
Lenina 19
22 Lenina
Lenina 42

Change the format (text \ number, etc.) does not help.
In other software products will sort correctly in Google sheets does not work.
Thank you!
April 4th 20 at 13:30
1 answer
April 4th 20 at 13:32
Solution
Actually there is a normal sort of strings. The figures in this case are their elements, and do not turn into numbers.

The only option seems to use a formula SORT.

Sorting by house number

=SORT(B2:B7;VALUE(REGEXEXTRACT(B2:B7;"\d+"));1)

5e29b13db732f802081945.png

Sort by street name, then house number

=SORT(B2:B7;REGEXEXTRACT(B2:B7;"\D+");1;the VALUE(REGEXEXTRACT(B2:B7;"\d+"));1)


5e29b18e67d33040338207.png

Not a fountain, but at least something.

Sincerely,
contributor.pw
Thank you! - kennedi commented on April 4th 20 at 13:35

Find more questions by tags Google Sheets