Hello,
I need to separate a complete mailing address into 3 columns.
Currently the entire Mailing address is on column, per this picture.
In this case I would like to spilt the cell after Department and after 9701 so (Experian Dispute Department in is on Column) P.O. Box 9701 is in a another column and then Allen, TX 75013 is in another column.
I’m guessing that there are line breaks after “Department” and “9701,” which we can use to create formulas to extract the pieces. First, create a formula field that contains the following:
SUBSTITUTE({Credit Bureau}, "\n", REPT(" ", 50))
This will replace all line breaks with large blocks of spaces (50 characters each in this case). I’ll name this field {Spread}, but you can use any name you prefer as long as you use that name in the later formulas. (It won’t look any different from the original field in the example below because Airtable auto-trims extra whitespace for display purposes, but the space blocks are there.)
For the first field that extracts the company name, which I’ll call {Company Name}, the formula would be:
TRIM(LEFT(Spread, 50))
The formula to extract the street/PO box address would be:
TRIM(MID(Spread, LEN({Company Name}) + 50, 50))
The formula to extract the city, state, and ZIP code would be:
TRIM(RIGHT(Spread, 50))
BTW, I recommend changing the “Credit Bureau” field to long text, in case you need to edit anything down the road. While single line text fields do support line breaks, you can’t add them manually, which makes editing a pain.
