Extract City from Single Cell with Different Delimiters on Either Side - with Formula!

ttootell

New Member
Joined
May 6, 2016
Messages
8
Formula Challenge!

This one is out of my league.

For an ASAP work project, I need to extract an a physical address (including multiple spaces) from a single cell, with two different (but same) delimiters on either side, and drop into new columns. See example below. I've tried two different snippets from this and other forums - with various combinations of Find, Mid, Search, etc. Can you help?

4320 McConnell Rd 2A Johnsonville UNITED STATES NC 27405
9986 Bay Drive Las Vegas UNITED STATES NV 89122

I have three spaces on the left, and a single space and UNITED on the right as unique static delimiters.

I was able to extract address from left, and ZIP from right. Still working on State... if you can help with that, bonus Excel karma for you!

Thank you in advance. Happy Friday!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
That's one of the issues - and I will ABSOLUTELY try your solution!

The primary problem is that I need the city name(s). I need JOHNSONVILLE and LAS VEGAS out of those listings (and similar out of other records)... between delimiters of " " on left, and " UNITED" on right.
 
Upvote 0
That's one of the issues - and I will ABSOLUTELY try your solution!

The primary problem is that I need the city name(s). I need JOHNSONVILLE and LAS VEGAS out of those listings (and similar out of other records)... between delimiters of " " on left, and " UNITED" on right.

But this is still the primary problem ^
 
Upvote 0
How about downloading the US zip code list and using it as your VLOOKUP TABLE?
- Separate just the last (RIGHT) 5 digits into your next column
- VLOOKUP or INDEX/MATCH the zip to give you CITY and STATE in separate columns
 
Upvote 0
I'm getting the entire address in one column as an output from a database, and I can't modify the source in this case as it's the result of a triggered event within the database. Otherwise, I would absolutely export in columns and concatenate or vlookup if needed afterward. This is irritating and messy. But I have little choice. :(
 
Upvote 0
you need to tell excel where to start and end the search

this will tell it where to end but as you have a varying amount of spaces at the front and possibly cities with two sets of character names there is no consistent way to tell it where to start. well known that I know of anyway

Code:
=LEFT(A:A,FIND("UNITED",A:A)-1)
 
Upvote 0
Actually, the amount of spaces at the front is not varied. There are three spaces leading the city, and nowhere else in the string are there three spaces. Can that be the leading delimiter?
 
Upvote 0
So, I still need to extract the CITY from this single string in once cell.

4320 McConnell Rd 2A Johnsonville UNITED STATES NC 27405
9986 Bay Drive Las Vegas UNITED STATES NV 89122

We're half way there with: =LEFT(A:A,FIND("UNITED",A:A)-1) [thx somesoldiers]

It doesn't show it here (as HTML stripped the spaces), but there are (3) spaces before the CITY that can be used as the leading delimiter.

Can someone help me add to this function to pull out the city between the " (3) " spaces and "UNITED" ?

Thanks in advance!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top