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

[ttootell]

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!

 

[Eric W]

Maybe?

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet18​

Worksheet Formulas Cell Formula C1 =TRIM(MID(A1,FIND(" ",A1),FIND("UNITED",A1)-FIND(" ",A1))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[jtakw]

Hi,

This should work for you:

Excel 2010
	A	B
1	4320 McConnell Rd 2A Johnsonville UNITED STATES NC 27405	Johnsonville
2	9986 Bay Drive Las Vegas UNITED STATES NV 89122	Las Vegas
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1,FIND(" ",A1)+3,SEARCH("UNITED",A1)-FIND(" ",A1)-4)

B1 formula copied down.
PLEASE NOTE: the " " in the formula actually is "3 spaces" as you described.

 

[ttootell]

Hi,

This should work for you:

Excel 2010

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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =MID(A1,FIND(" ",A1)+3,SEARCH("UNITED",A1)-FIND(" ",A1)-4) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

B1 formula copied down.
PLEASE NOTE: the " " in the formula actually is "3 spaces" as you described.

This worked - I am so very thankful for your solution.
THANK YOU!

 

[jtakw]

This worked - I am so very thankful for your solution.
THANK YOU!

You're welcome, welcome to the forum.

I think Eric W's formula works as well if you make sure you put "3 spaces" in the formula.