CODEWITHSUNDEEP
excelexcel-formula
Vincent
Vincent

Reputation: 2787

Excel: Formula to combine potentially incomplete address components into an address string with appropriate separators

I need to intake U.S. address data by street, city, state, and ZIP code and output an address in the following format:

1502 Bruce Rd, Oreland, PA 19075

without any superfluous spaces.

Any component of the address can be missing, so each of the four elements binarily exists or doesn't, which results in a total of 4! = 24 permutations of having or not having each of the four elements. Excluding the case where they're all missing, that leaves 23 permutations to deal with.

There seem to be lots of questions about splitting addresses apart, but none about combining them back together, especially when you need to either include or exclude the spacing based on what comes after. What's the best way to handle this? Bonus points for a way that's easily extensible (for example, I won't be surprised if later we have to include a unit field between street and city at some point in the future).

Upvotes: 0

Views: 142

Answers (1)

Vincent
Vincent

Reputation: 2787

As I was writing up this question, I came up with a couple solutions, where columns A, B, C, and D are street, city, state, and ZIP code, respectively.

First is this one:

=TRIM(
    IF(A1="", "", A1 & IF(B1&C1&D1="", "", ", ")) &
    IF(B1="", "", B1 & IF(   C1&D1="", "", ", ")) &
    IF(C1="", "", C1 & IF(      D1="", "", " " )) &
    IF(D1="", "", D1)
)

which works as follows:

  1. See if the element exists and, if so, include it.
  2. If the element exists, see if anything exists after it and, if so, add the spacer that follows it. (This works because spacers seem be determined by what is before the spacer.)
  3. And the whole thing's wrapped in a TRIM to get rid of unnecessary user-input spaces.

To check if anything after an element exists, I concatenated the following fields and checked to see whether that concatenation was a blank string.

Because of the use of concatenation here, I thought it might be easier to use TEXTJOIN and came up with this:

=TEXTJOIN(" ", TRUE, TEXTJOIN(", ", TRUE, TRIM(A1), TRIM(B1), TRIM(C1)), TRIM(D1))

which works as follows:

  1. The inner TEXTJOIN combines the first three elements (street, city, and state) with the common delimiter of a comma + a space.
  2. Once that's done, the outer TEXTJOIN combines the result of that with the ZIP code using a space as a delimiter.
  3. TRIM used again as above.

These seem to cover all 23 cases and aren't too hard to extend if additional fields need to be added, although I'm definitely open to any better solutions you might have.

Upvotes: 1

Related Questions