Formula Help - Splitting a String within a Column

[eco467student2]

I've got a database export of addresses that looks like this

Geo_Address
WHITE&SECOND
1 MCDONOUGH PLAZA
1 MCDONOUGH PLAZA
1 MCDONOUGH PLAZA
WASHINGTON&CENTER
297 SECOND AVE

I need a formula that can place a space before and after the ampersand (so I can run this through Google Geocoder API as an intersection) while leaving the other strings in each row without an & in tact. So this formula should provide the following results:

Geo_Address
WHITE & SECOND
1 MCDONOUGH PLAZA
1 MCDONOUGH PLAZA
1 MCDONOUGH PLAZA
WASHINGTON & CENTER
297 SECOND AVE

Thanks for any help you can provide.

 

[T. Valko]

I've got a database export of addresses that looks like this

Geo_Address
WHITE&SECOND
1 MCDONOUGH PLAZA
1 MCDONOUGH PLAZA
1 MCDONOUGH PLAZA
WASHINGTON&CENTER
297 SECOND AVE

I need a formula that can place a space before and after the ampersand (so I can run this through Google Geocoder API as an intersection) while leaving the other strings in each row without an & in tact. So this formula should provide the following results:

Geo_Address
WHITE & SECOND
1 MCDONOUGH PLAZA
1 MCDONOUGH PLAZA
1 MCDONOUGH PLAZA
WASHINGTON & CENTER
297 SECOND AVE

Thanks for any help you can provide.

With your data starting in A2...

Enter this formula in B2 and copy down as needed:

=SUBSTITUTE(A2,"&"," & ")

 

[eco467student2]

Thanks! That worked. Much appreciated.

 

[T. Valko]

Thanks! That worked. Much appreciated.

You're welcome. Thanks for the feedback!