My problem I need to solve is to rip apart the last section of a URL.
I have the URL
.../one/two/three/four
and need to have
four
OR
/four
I used Excel function
=RIGHT(F2;SEARCH("/";F2))
But it returns
r
Anyone have a solution for this? Thanks!
This will get you four
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1))
this get you /four
=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))),1)+1)
Source: Find Position of the Last Occurrence of a Character in a String in Excel
You can do that using FILTERXML (available from Excel 2013 and later):
=FILTERXML("<t><s>"&SUBSTITUTE(A1;"/";"</s><s>")&"</s></t>";"//s[last()]")
This formula will first build an XML string like this
<t><s>...</s><s>one</s><s>two</s><s>three</s><s>four</s></t>
and then extract the last node.
There has recently been a nice question here on Stackoverflow that I highly recommend to read: