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

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!

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.

2 Likes

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.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.