Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 10, 2022 04:45 AM
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!
Jun 10, 2022 11:07 AM
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.
Jun 10, 2022 11:32 AM
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.
Jun 10, 2022 12:08 PM
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.
Jul 27, 2023 10:10 PM
This is great! Exactly what I was needing, and I fully appreciate the explanation so I can learn for the future.
One additional question about this scenario: I deleted $1 to eliminate the house number. I'm trying to group and organize by just the street name in order to connect neighbors for a neighborhood association. The issue I'm running into now is that the address field where this formula is pulling the data, I've got people entering the same street name in different ways: Carr Ave., Carr Avenue, carr, Carr
Is there a way to either force formatting in the address field, or to standardize once pulled into the formula field?
Thanks, I hope this makes sense.
Jul 28, 2023 12:23 PM
You can't force formatting/case in the address field, so you would still need a formula field to assist with grouping/sorting. Depending on the size of the neighborhood, you may want to try one of the following:
UPPER(REGEX_REPLACE({Address Field}, "[^\s]+\s([^\s]+)", "$1"))