How do I set up an Address Field to sort by full address, not just the number portion

540 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I am trying to set up and “Address Field” that has the full address, ie: “1234 Summerset Dr.”. How can I sort that field A-Z without it just sorting by the number portion of the address? Thank you in advance!

3 Replies 3

Do you already have the field created? Sorting by that would be the same as sorting by any other text value. It would sort A-Z including the numbers at the start.

If you’re trying to sort by street name then street number, then you’ll need a separate column to help with that (unless you have the street name as its own field).
So create a new formula field with the following formula:
REGEX_REPLACE( {Address Field}, "([^\s]+)\s(.+)", "$2 $1")
Then sort by that column.

Explanation of the formula (you don’t need to know this to use it)
What the formula does is search for the first space in the address (this is to address street numbers like “12A”, or “12-14”), and then captures everything to the left of that space (([^\s]+) , where () marks what you want to capture, [^\s] stands for “anything that isn’t a space”, and \s stands for any space character – it’ss outside the parentheses because we’re not capturing it), then everything to the right of that space ((.+), . stands for “anything”). It then swaps the two ($2 $1, where $2 is the second capture group, $1 is the first capture group, and there’s a space between them).*

*I’m using \s here instead of a simple space because space characters can look very similar. This includes things like the non-breaking whitespace character (sometimes entered as  ) – which word processors like Microsoft Word sometimes insert – or a double-width whitespace character – which can sometimes accidentally be inserted depending on a user’s keyboard settings.

Alternatively, you can use a non-REGEX formula like this:

RIGHT({Address Field},LEN({Address Field})-FIND({Address Field}," "))

This will not detect nonstandard spaces.