Return texts between certain characters from a long string (de-concatenate)

[regresss]

Hi, I have a string which goes //John/McCormik/25

The slash is used to divide the string into a few parts. Now I would like to return them, i.e. to de-concatenate and retrieve:

*nothing ("") for the first position,
*"John" for the second position,
*"McCormick" for the third position, and
*"25" for the fourth position.

 

[Trevor G]

Use the Data Tab and Text to Columns step through and use the / as the separator

 

[regresss]

I need to use a formula for this. I was thinking about something like

*MID(cell,position of the 1st slash, position of the 2nd slash)
*MID(cell,position of the 2nd slash, position of the 3rd slash)
*MID(cell,position of the 3rd slash, position of the 4th slash)
*MID(cell,position of the 4th slash, position of the 5th slash)

But it's difficult to retrieve position of slashes.

 

[regresss]

Ï have found a solution

A1: //John/McCormick/25
=MID(A1,FIND(CHAR(120),SUBSTITUTE(F3,"/",CHAR(120),1))+1,FIND(CHAR(120),SUBSTITUTE(F3,"/",CHAR(120),24))-FIND(CHAR(120),SUBSTITUTE(F3,"/",CHAR(120),1))-1)
=MID(A1,FIND(CHAR(120),SUBSTITUTE(F3,"/",CHAR(120),2))+1,FIND(CHAR(120),SUBSTITUTE(F3,"/",CHAR(120),3))-FIND(CHAR(120),SUBSTITUTE(F3,"/",CHAR(120),2))-1)
=MID(A1,FIND(CHAR(120),SUBSTITUTE(F3,"/",CHAR(120),3))+1,FIND(CHAR(120),SUBSTITUTE(F3,"/",CHAR(120),4))-FIND(CHAR(120),SUBSTITUTE(F3,"/",CHAR(120),3))-1)