Google Sheets - Addresses on one line

[pattyarms]

Hi all,

I'm working from Google Sheets, trying to mail merge the address... I'm wondering if there is anyway to take JUST the Street Address from Column D and put it in Column E below. We got data that had the whole address sans commas. We already have matching data for F through H (City, State, ZIP). The Street Address in the example below would be 450 N Wall St or 11838 Center Hill Rd etc. I am looking for a formula to put in Column E that would take out the unneeded City/State/ZIP of Column D, but leave the rest. It could reference Column F for example and say "Copy all text but stop after you hit Column F and then past into Column E" I just am so lost with this stuff. If it's easier in Excel, happy to alternatively get answers for that platform! Thanks in advance

(A) School Name	(B) Contact	(C) Title	(D) Mailing Address	(E) Mailing Street	(F) Mailing City	(G) Mailing State	(H) ZIP
Example High School	Aaron	Business Education Teacher	450 N Wall St Denmark WI 54208-9416		Denmark	WI	54208
Blank High School	Aaron	Principal	11838 Center Hill Rd Darlington WI 53530-9605		Darlington	WI	53530
Blank High School	Aaron	Principal	S6520 State Highway 131 Viola WI 54664-8528		Viola	WI	54664
Tech High School	Aaron	Principal	700 S Fourth St Milwaukee WI 53204-1760		Milwaukee	WI	53204

 

[steve the fish]

Try this:

=IFERROR(TRIM(LEFT(D2,SEARCH(F2,D2&F2)-1)),D2)

 

[bobsan42]

Try this in E2

=left(D2, FIND(F2,D2)-1)

And of course Steve's solution is more error proof.

 

[steve the fish]

This is probably best in case F2 is blank:

=IF(F2="",D2,TRIM(LEFT(D2,SEARCH(F2,D2&F2)-1)))