Log In
Register
Home
Active Member
Dashboards
Power Query
Power Pivot
Advanced Excel Formulas
Excel Tables
Excel
Word
Outlook
August 7, 2022
Offline
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
Trusted Members
February 13, 2021
Offline
Nothing attached. After attaching, be sure to click 'Start Upload' and wait for the grey tick mark. On that note, have you considered TEXTSPLIT?
Active Member
Dashboards
Power Query
Power Pivot
Advanced Excel Formulas
Excel Tables
Excel
Word
Outlook
August 7, 2022
Offline
I have not consulted textsplit, i will look for that on the site.
Active Member
Dashboards
Power Query
Power Pivot
Advanced Excel Formulas
Excel Tables
Excel
Word
Outlook
August 7, 2022
Offline
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.
October 5, 2010
Offline
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
1 Guest(s)
