Extracting part of a cell

[ginsterman]

Hello

I have a row of data, where the data is separated by two "/"'s i.e abc/123/defg


Is there a formula I can use to extract everything sfter the second backslash, the number of characters after the backslash varies.


Thanks

 

[Colin Legg]

Hi,


Here are two commonly suggested formulas:

This one assumes there are two / and you want everything after the second one. It also assumes that "|@" will not occur in your data:

[pre]

=RIGHT(A1,LEN(A1)-SEARCH("|@",SUBSTITUTE(A1,"/","|@",2)))
This one assumes that the 2nd / is the last one and you want everything after it excluding any starting/trailing/multiple spaces:

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))),LEN(A1)))

[/pre]