Split text in 1 column into 3 separate columns

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.