1

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?

user1592380
  • 34,265
  • 92
  • 284
  • 515
  • can it be said that the zip code will always be the second to last grouping delineated by the comma? – Scott Craner Aug 31 '21 at 16:49
  • Yes, Lets assume that – user1592380 Aug 31 '21 at 17:00
  • What about the hyphen inside "78154-1265"? Would that be what you are after (since you spoke about the 5 digit number)? Also, please include your own attempt at solving this issue to make it on-topic. – JvdV Aug 31 '21 at 17:06

1 Answers1

2

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81