I am trying to separate the address 2 (e.g. Ste 30) from the address column into the address 2 column.
I tried using a =Filter function to no success.
The text to column delimited function does not work for this scenarion.
i have attached a sample excel doc
Nothing attached. After attaching, be sure to click 'Start Upload' and wait for the grey tick mark. On that note, have you considered TEXTSPLIT?
I have not consulted textsplit, i will look for that on the site.
I used =TEXTAFTER(C2" ",-2) I just need to work on a formula to remove the "STE" from the address 1 column, thanks for the tip.
Hi Patrick,
You can get the first part of the address with this
=IF(IFERROR(SEARCH(" Ste ",C2),0)>0,LEFT(C2,LEN(C2)-(LEN(C2)-SEARCH(" Ste ",C2))-1),C2)
and the second part of the address with this
=IF(IFERROR(SEARCH(" Ste ",C2),0)>0,MID(C2,SEARCH(" Ste ",C2)+1,LEN(C2)-SEARCH(" Ste ",C2)),"")
Please see attached file.
Regards
Phil
Using TEXTBEFORE with the same arguments should return the address up to the STE part.
Thank you everyone!
