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

[Jinny_Ahrens]

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!

[Kamille_Parks]

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.

[Andy_Lin1]

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.

[ScottWorld]

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.

[DSPhotography]

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.

[Andy_Lin1]

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:

• add UPPER() around the outside of your formula, and change the regex to only capture the first word of the street name (i.e. the second cluster of characters in the input string)
  

  UPPER(REGEX_REPLACE({Address Field}, "[^\s]+\s([^\s]+)", "$1"))

• use a single-select dropdown, which may be preferable if there aren't too many streets, since that sidesteps the need for validation and you can arbitrarily sort that field