CODEWITHSUNDEEP
excel
user1592380
user1592380

Reputation: 36307

Extracting zip codes in excel in unstructured data

enter image description here

I'm working with excel 2016. I have some unstructured data as in the screenshot. I would like to extract the zip code ( which should be the last 5 digit number in each cell ).

I'm new to excel . Do I need to install and use regex to extract the zip code(How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops)

or is there a more standard not vb related function/group of functions in excel that can do this?

Upvotes: 1

Views: 626

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

If the zip code is always the second to last group delineated by commas:

=MID(A2,FIND("{{{",SUBSTITUTE(A2,",","{{{",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))-1))+2,5)

enter image description here

Or if one has it FILTERXML:

=FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s[last()-1]")

enter image description here

Upvotes: 2

Related Questions