Separating Info in Excel Combined Fields

Here’s a quick fix if you have an Excel spreadsheet that has a field that combines the city, state, and zip in one field.

Three calculations will easily extract the information and place them into individual fields allowing you to easily import them into FileMaker.

The combined field is present in B2 and the formula for the city is:
=LEFT(B2,SEARCH(“,”,B2)-1)

Screen Shot 2016-07-29 at 9.34.50 AM

 

The formula to extract the state is:
=MID(B2,SEARCH(“,”,B2)+2,2)

Screen Shot 2016-07-29 at 9.35.02 AM

 

The formula to provide the zip code is:
=MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&”0123456789″)),255)

Screen Shot 2016-07-29 at 9.35.11 AM

 

2016-11-01T17:01:16+00:00 July 29th, 2016|