Help retrieving text after 2nd, 3rd coma in cell

[gdnimrod]

Hello,

I have a bunch of cells in this format

NYC,TARGET,LUX
TARGET
NYC,TARGET,FRA,DUB

i.e some with a single item, some with 4 as in the last case. I need to seperate them into different columns.

I used this code for seperating them with an instance of a single item

IFERROR(LEFT(Q12,FIND(",",Q12)-1),Q12)

and this to get in between the first and 2nd comas:

=IFERROR(IFERROR(LEFT((MID(Q13,FIND(",",Q13)+1,LEN(Q13)-LEN(U13)-1)),FIND(",",MID(Q13,FIND(",",Q13)+1,LEN(Q13)-LEN(U13)-1))-1),MID(Q13,FIND(",",Q13)+1,LEN(Q13)-LEN(U13)-1)),"")

To get the item after the 3rd coma, i used this

=IFERROR(RIGHT(Q12,LEN(Q12)-FIND(",",Q12,FIND(",",Q12,1)+1)-FIND(",",Q12,FIND(",",Q12,1)+1)+1),"")

My real issue is i cant seem to get the code right to parse the text between the 2nd and 3rd comas.

Thank you in advance

 

[Jonmo1]

Welcome to the board..

Try
Data - Text To Columns
Deliminated - Next
Comma - Finish


Hope that helps.

 

[gdnimrod]

Thanks a lot, and thank you for that.
Indeed i did it that way at first, but i was told i require to have it written in code.

 

[Jonmo1]

Try this for the first one, in say B1

=LEFT(A1,FIND(",",A1&",")-1)

Then in C1 and filled right as far as needed

=TRIM(LEFT(MID(SUBSTITUTE($A1&",",",",REPT(" ",LEN($A1)+1)),(LEN($A1)+1)*(COLUMNS($A1:A1)),(LEN($A1)+1)*(COLUMNS($A1:A1))),LEN($A1)+1))