need to separate data from cells with multiple lines of text

[u/East_Supermarket8550]

i have to pull zip codes out of a column that has full client contact data in each cell and cannot figure out how to use the delimited tool or formulas to get the correct data. each cell is arranged like this:

Client Name
Address Line 1
City, State Zip
Phone Number
Email

I have several hundred rows in this column and need just the zip code separated. i've tried several methods and cannot find a solution.

EDIT: when i try to use the delimited tool, it only pulls the first line of text, client name, and not any of the other lines. i cannot understand why

[u/AutoModerator]

/u/East_Supermarket8550 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/TA1930]

I mean this sounds like text-to-columns, just click the checkbox for “Tab” and you’re done. You can concat and/or hide the other columns after.

[u/no_therworldly → u/TA1930]

How the hell could I forget about tab being literally there in the text to columns section 🤦🏼🤦🏼🤦🏼

[u/MayukhBhattacharya]

Try using the following formula:

=REGEXEXTRACT(A:.A, "[A-Z]{2} (\d{5}(?:-\d{4})?)", 2)

Or, if you don't have access to Regex then:

=TAKE(TEXTSPLIT(A1, CHAR(10), " "), -1, 1)

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Using TEXTSPLIT():

=TAKE(TEXTSPLIT(A1, CHAR(10), " "), -1, 1)

[u/ThisIsAdamB]

A quick search gave me:

=REGEXEXTRACT(A1, “\b\d{5} \b”, 0 , 0)

Assuming your data is in cell A1. If you need the zip plus four extracted, you would have to substitute a regex pattern matching #####-####, instead of #####, which is the d{5} does.

[u/Krispy_Knight]

Are the states the full name or the 2 letter abbreviations?

[u/overitalready04]

Look for "text to colums". I think it's in the data tab. I'm assuming that city state and zip are in the same cell. You may have to insert column to the right of the cell, but text to columns will take the city state zip into separate columns if you use space as the delimiter

[u/no_therworldly]

There must be (I don't know it though) to delimit by tab Or replace all tabs within the cells with another special character and then delimit by that new character. Afterwards you can do TEXT AFTER on the space before zip

[u/East_Supermarket8550 → u/no_therworldly]

do you know how i would replace all tabs? sorry, i am not very good with excel, i am doing this for an older coworker

[u/StevieG-2021 → u/East_Supermarket8550]

You’d have to be sure its tab delimited. Often data likes this uses a carriage return.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHAR	Returns the character specified by the code number
LEFT	Returns the leftmost characters from a text value
LEN	Returns the number of characters in a text string
REGEXEXTRACT	Extracts strings within the provided text that matches the pattern
RIGHT	Returns the rightmost characters from a text value
SUBSTITUTE	Substitutes new text for old text in a text string
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
TEXT	Formats a number and converts it to text
TEXTAFTER	Office 365+: Returns text that occurs after given character or string
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
10 acronyms in this thread; the most compressed thread commented on today has 22 acronyms.
[Thread #49552 for this sub, first seen 20th Oct 2026, 20:12] [FAQ] [Full list] [Contact] [Source code]}

[u/FunkSoulCousin]

Can you not just add two extra columns with these formulas?
=RIGHT(cell with city state zip, 5) for ZIP
=LEFT (cell with city state zip, LEN(cell with city state zip)-6) for City, State?

[u/MlookSM]

Can't you do Ctrl + J in the text to column delimiter? I believe that make the line break itself be the delimiter.

Separate into multiple columns and extract the column in which the ZIP lies. and go from there.

I haven't tested it, but if it doesn't work just use =SUBSTITUTE( A1, CHAR(10),"©") which turn line breaks into a non used symbol, then use said delimiter © in Text to columns.

[u/Klutzy_Highlight7500]

To answer your edit: those line breaks aren't tabs, so the Tab checkbox does nothing. In Text to Columns, tick Other, click in its box and press Ctrl+J (looks empty, but it enters the line break), and each line lands in its own column. The zip will then be at the end of the third column, so you can grab it with TEXTAFTER(C1," ",-1).

[u/carbonizedtitanium]

Post a screenshot of your spreadsheet in the comments. Redact or replace sensitive information with dummy info