dimanche 21 juin 2020

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.

Aucun commentaire:

Enregistrer un commentaire