Take last section of URL and return it in Excel

Question

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

Anyone have a solution for this? Thanks!

Wizhi · Accepted Answer · 2020-08-05T10:05:29.877

3

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)

edited Aug 05 '20 at 10:05

answered Aug 05 '20 at 09:58

Wizhi

Michael Wycisk · Answer 2 · 2020-08-05T10:19:38.713

2

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:

edited Aug 05 '20 at 10:19

answered Aug 05 '20 at 09:57

Michael Wycisk

You should add that this solution is only available in Excel 2013 or higher – Foxfire And Burns And Burns Aug 05 '20 at 10:16
@FoxfireAndBurnsAndBurns Thank you, I have edited my post. Even though I assume most people use Office 2013 or newer. – Michael Wycisk Aug 05 '20 at 10:23
Note that FILTERXML is not available on a Mac. Sigh. https://support.microsoft.com/en-us/office/filterxml-function-4df72efc-11ec-4951-86f5-c1374812f5b7 – Nic Nov 18 '22 at 15:28

2 Answers2