Forum Discussion
Excel Help - Zipcode Extraction
Hello!
I am having issues extracting zipcodes from a large database of primary addresses. The zipcodes are in varying locations within the address information, but are always 5 digits.
For example:
| Headquarters: 123 Example Street Random, Massachusetts 12345 United States Main Phone: 123 123 1234 |
The requested output would be the zipcode 12345 placed in cell L2.
A very late response...
Are you sure there will never be a five-digit house number? If so: with an address in A2:
=LET(T, A2, L, LEN(A2), S, SEQUENCE(L), N, CONCAT(--ISNUMBER(--MID(T, S, 1))), P, FIND("11111", N), MID(T, P, 5))
Provided you are a 365 user, you are in luck because Regular Expressions are coming available.
= REGEXEXTRACT(address, "\b\d{5}\b")If you wish to pick out multiple matches or combine it with extracting 7 digit zips that too is possible.
