Split by the second delimeter which is not the same in all cells

[chandramouliarun]

Hi All,

Happy Friday!.. I have a cell of address of the following types which i am trying to split into two after the second delimiter which is a ">" or":"...


Turkey > Istanbul: Saroke No:12 h12 kasa ku metal
Argentina > Buenos Aires > S4367 > San matar,34,linkefg drett


Can someone throw light on how this can be done..

Thanks,
Arun

[CAntosh]

Happy Friday! With your data in A2, try the following:

Left half: =LEFT($A2,SEARCH("@",SUBSTITUTE(SUBSTITUTE($A2,":","@"),">","@"),SEARCH("@",SUBSTITUTE(SUBSTITUTE($A2,":","@"),">","@"))+1))

Right half: =MID($A2,SEARCH("@",SUBSTITUTE(SUBSTITUTE($A2,":","@"),">","@"),SEARCH("@",SUBSTITUTE(SUBSTITUTE($A2,":","@"),">","@"))+1)+1,1000)

Then fill down as needed. The attachment should show how it looks:

[chandramouliarun]

Hi Cantosh,

Thanks for your response!...Appreciate your help!..Also can you please let me know how to get rid of the leading : and > in the first column as below..

Turkey > Istanbul:
Argentina > Buenos Aires >

[chandramouliarun]

Sorry I meant the trailing : and >

[CAntosh]

Glad to help!

To remove the trailing delimiter, just add a "-1", so the formula in B2 looks like this:

=LEFT($A2,SEARCH("@",SUBSTITUTE(SUBSTITUTE($A2,":","@"),">","@"),SEARCH("@",SUBSTITUTE(SUBSTITUTE($A2,":","@"),">","@"))+1)-1)

[chandramouliarun]

You are awesome!..Have a great day!!..