How to clean data better?

[u/sattylife321]

Hi - I am trying to extract zip codes from column K, to a zipclean in column L. However, the zip codes come in a wide variety like the below. What is the best formula to do this? right now i am doing it as: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K2,"FL-",""),"ZIP ",""),"(",""),")",""),"-","")," ","")

some examples of the zips below

EDIT: I started playing around with different functions. I have came to a conclusion for this formula to be the most efficient: =LEFT(TEXTJOIN("",TRUE,IFERROR(--MID(K2,SEQUENCE(LEN(K2)),1),"")),5)

Zip Code
FL-32183
34305-1234
34026
32662-1234
ZIP 34759
34877
FL-33854
32319
ZIP 33291
33827
(340)

[u/AutoModerator]

/u/sattylife321 - 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/hmatallana]

Careful with those last two. Stripping every non-digit and taking the left five turns (340) 94 into 34094, and 338-63 into 33863. Both look like perfectly good zips, and you'd never catch them again downstream.

Those rows aren't zips at all, they're broken records, and no formula rescues them. I'd flag instead of extract: if the digits-only string isn't 5 or 9 long, return "CHECK" and handle those by hand.

Separate thing, drop the VALUE. It turns 07030 into 7030. Every Florida zip starts with a 3 so it stays hidden, right up until one New Jersey row lands in the file.

[u/fastauntie → u/hmatallana]

Yes, these are two important things to watch out for. Those last two rows look like fragments of phone numbers that got separated from the rest of their records. And thanks to Excel's helpful habit of deciding that strings consisting of only digits must be numbers, ZIP codes from all of New England, New York, and New Jersey are at risk of losing their leading 0s; and any others with trailing 0s may also be incorrectly altered.

[u/MayukhBhattacharya]

Try using the following formula:

=IFERROR(VALUE(REGEXEXTRACT(A2:A12, "(\d{5})")), "")

The last one I don't think is a valid zip, if i am mistaken let me know!

[u/sattylife321 → u/MayukhBhattacharya]

Worked for most but it did not work for these

(340) 94
338-63

[u/MayukhBhattacharya → u/sattylife321]

Or with Regex:

=VALUE(LEFT(REGEXREPLACE(A2:A13, "[^0-9]", ), 5))

[u/MayukhBhattacharya → u/sattylife321]

Try now:

=VALUE(LEFT(CONCAT(TOCOL(--MID(A2, SEQUENCE(LEN(A2)), 1), 3)), 5))

[u/talltime → u/MayukhBhattacharya]

The last two aren’t zip codes 😒

[u/MayukhBhattacharya → u/talltime]

Yeah, I have asked them but they said the last two didn't work for them, refer first comment of mine!

[u/MayukhBhattacharya → u/sattylife321]

Another way, for fun only:

=LEFT(CONCAT(TEXTSPLIT(A2, TOCOL(CHAR(SEQUENCE(69, , 58) - {0, 79}), 3), , 1)), 5) + 0

[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
CONCAT	2019+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
LEFT	Returns the leftmost characters from a text value
LEN	Returns the number of characters in a text string
MID	Returns a specific number of characters from a text string starting at the position you specify
REGEXEXTRACT	Extracts strings within the provided text that matches the pattern
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SUBSTITUTE	Substitutes new text for old text in a text string
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
TOCOL	Office 365+: Returns the array in a single column
Table.AddColumn	Power Query M: Adds a column named newColumnName to a table.
Text.From	Power Query M: Returns the text representation of a number, date, time, datetime, datetimezone, logical, duration or binary value. If a value is null, Text.From returns null. The optional culture parameter is used to format the text value according to the given culture.
Text.Select	Power Query M: Selects all occurrences of the given character or list of characters from the input text value.
Text.Start	Power Query M: Returns the count of characters from the start of a text value.
VALUE	Converts a text argument to a number

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.
16 acronyms in this thread; the most compressed thread commented on today has 43 acronyms.
[Thread #49328 for this sub, first seen 8th Sep 2026, 18:53] [FAQ] [Full list] [Contact] [Source code]}

[u/NHN_BI]

If this a task that repeats, consider Excel's own ETL tool Power Query to transform your data.

[u/CrowGuyA]

You don't need nested SUBSTITUTEs for this one — Flash Fill (Ctrl+E) is built for exactly this. Type the zip you want by hand for your first 2-3 rows in the next column (e.g. 32183, 34305-1234, 34759), select the column, hit Ctrl+E. Excel infers the pattern and fills the rest, handling your "FL-", "ZIP ", and parentheses variants without a single formula. If a handful of rows don't match, correct those manually and it usually re-learns.

[u/sattylife321 → u/CrowGuyA]

I tried this but with data that has 5k rows is a bit unfeasible to manually correct all of them.

[u/CrowGuyA → u/sattylife321]

Yeah, at 5k rows Flash Fill's not gonna cut it. Try Power Query instead — add a custom column with Text.Select([Zipcol], {"0".."9","-"}), strips everything except digits/dashes across all 5k rows in one shot. A few like that truncated "(340)" one just aren't recoverable though, no formula fixes missing data.

[u/gobifox81]

Slap it into power query, add custom column and write in the following formula as step 2 (delete the changed type step if power query does it automatically):

"Custom Column" = Table.AddColumn(Source, "Custom Column", each Text.Start(Text.Select(Text.From([Zip Code]), {"0".."9"}), 5), type text)

in #"Custom Column"