Search code examples
excelif-statementconcatenationcellis-empty

concatenate an address based on multiple cells, some of which may be blank

This is an address list that has a field for po box (number only)[A1], street number[A2], street name[A3].

Objectives use PO Box over street address where there are both OR no street given
then concatenate "P.O. Box "& A1; to get "P.O. Box 1234" as the second line under the name OR, if po is blank, concatentate street number and name: A2&" "&A3; to get "1234 Smith Street" THEN join the results to produce a mailing label with the city,state,and zip cells.

Everything I come up with give Excel (and me) a bellyache. It hates empty cells, circular refs, etc. enter image description here

1124 Kenilworth Ave.

i did Col I by hand. {=If(F2>"","P.O. Box "&F2,G2&" "&H2)} is a no go:excel does not like the empty G and H cells. I don't seem to grasp the various of blank, isblank,isnotempty, etc. for they all fail. This seems pretty basic conditional choice. test for presence in one col and act accordingly: if the first test is met, why balk at the second.

Any help appreciated.

Solution

  • The initial question is which kind of address to create, street or postbox.

    =IF(OR(COUNTA($F3)=1,COUNTA($F3:$H3)=0),"P.O.Box "&IF(COUNTA($F3),$F3,$A$1), "Street address")

    The above formula determines that (a) if a Box number is given OR (b) no addressing information is available at all a Box address must be returned. In the latter case, if there is no box number the default from A1 is to be used. This is the full and final solution for box addresses. Any corrections to that thought must be made here.

    The alternative to a Box address is a street address. In the above formula that solution is represented by the place holder "street address". Below is the formula that should take the place holder's place. It's very simple, initially.

    =IF(COUNTA($G3:$H3)=2,"OK","Not OK")

    Observe that the cell count can only be 1 or 2. Therefore there are only two possible solutions marked by the two place holders in this formula. The formulas for these are given below. Observe that a missing street name is replaced with A3 and a missing street number with A2. The formula allows for both but, in fact, this can't occur because a Box address would be returned.

    [OK] =$G3&" "&$H3
[Not OK] =IF(COUNTA($G3),$G3,$A$2)&" "&IF(COUNTA($H3),$H3,$A$3)

    With the place holders replaced we get this formula.

    [street address] =IF(COUNTA($G3:$H3)=2,$G3&" "&$H3,IF(COUNTA($G3),$G3,$A$2)&" "&IF(COUNTA($H3),$H3,$A$3))

    And when this formula replaces the place holder "street address" in the initial, basic formula here is the final result.

    =IF(OR(COUNTA($F3)=1,COUNTA($F3:$H3)=0),"P.O.Box "&IF(COUNTA($F3),$F3,$A$1), IF(COUNTA($G3:$H3)=2,$G3&" "&$H3,IF(COUNTA($G3),$G3,$A$2)&" "&IF(COUNTA($H3),$H3,$A$3)))