Separating Name & Address

[jdub21]

Hey Excel Gurus - Got a puzzling one - I am trying to separate location name from the address. I have tried using text to column but because the entries are different, it is never-ending. I need assistance please. I need to be able to cut anything before the address and place into a new cell.... Any help would be great.

Atria Senior Living - North Point - ASL010282 100 Somerby Drive

DRM/Ross #1874 310 East Martintown Road suite 300

Dollar General Store Number 13427 177 Main Street

Spirit Halloween 1945 West Palmetto Street

Buffalo Wild Wings 2625 Broad Street

 

[JamesCanale]

MrExcelPlayground12.xlsx
	A	B	C
1	Atria Senior Living - North Point - ASL010282 100 Somerby Drive	Atria Senior Living - North Point - ASL	010282 100 Somerby Drive
2	DRM/Ross #1874 310 East Martintown Road suite 300	DRM/Ross #	1874 310 East Martintown Road suite 300
3	Dollar General Store Number 13427 177 Main Street	Dollar General Store Number	13427 177 Main Street
4	Spirit Halloween 1945 West Palmetto Street	Spirit Halloween	1945 West Palmetto Street
5	Buffalo Wild Wings 2625 Broad Street	Buffalo Wild Wings	2625 Broad Street
Sheet8

Cell Formulas
Range	Formula
B1:B5	B1	=TRIM(LEFT(A1,MIN(IFERROR(SEARCH(SEQUENCE(10,1,0),A1),9999))-1))
C1:C5	C1	=TRIM(MID(A1,MIN(IFERROR(SEARCH(SEQUENCE(10,1,0),A1),9999)),999))

 

[jdub21]

Thank you again. Works beautifully