Populate cell with substring following last / in the string

Question

I have a CSV in which column A is populated with strings, such as:

ABCDE/FGHI/JKL/MNOPQR

I need to populate column C with everything after the last occurrence of the "/". In this example, it would have to be "MNOPQR".

Is there a function that could be used for this? "RIGHT" doesn't seem to do the trick. I don't know what the length of the substring will be in each row, so I definitely have to look for the "/".

Loop backwards by characters from the right until you hit the first `/`? — findwindow, Apr 06 '16 at 20:56
Possible duplicate of [Excel: last character/string match in a string](http://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string) — Grade 'Eh' Bacon, Apr 06 '16 at 20:58

score 2 · Accepted Answer · edited Oct 29 '21 at 17:51

2

If your text is in A4, put this in another cell:

=MID(A4,LEN(LEFT(A4,FIND(CHAR(1),SUBSTITUTE(A4,"/",CHAR(1),LEN(A4)-LEN(SUBSTITUTE(A4,"/",""))))))+1,LEN(A4)-LEN(LEFT(A4,FIND(CHAR(1),SUBSTITUTE(A4,"/",CHAR(1),LEN(A4)-LEN(SUBSTITUTE(A4,"/",""))))))+1)

I think that should work. Thanks to @Jerry for the main part, where it finds the last / in a string.

edit: Per @ScottCraner, this is shorter: =MID(A1,SEARCH("}}}",SUBSTITUTE(A1,"/","}}}",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))‌))+1,LEN(A1))

edited Oct 29 '21 at 17:51

Nimantha

6,405
6
28
69

answered Apr 06 '16 at 20:58

BruceWayne

22,923
15
65
110

1

This one is a little shorter: `=MID(A1,SEARCH("}}}",SUBSTITUTE(A1,"/","}}}",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,LEN(A1))` – Scott Craner Apr 06 '16 at 21:01
@ScottCraner - Nice! I knew there would probably be a quicker way, thanks for letting me know! – BruceWayne Apr 06 '16 at 21:01
Which by the way is nearly exact to the formula in the link by Grad eh bacon – Scott Craner Apr 06 '16 at 21:02
Why is it searching for `"}}}"`? – findwindow Apr 06 '16 at 21:03
2

See the link for a better explanation, but in essence I am replacing the last "/" with "}}}" then searching for it. – Scott Craner Apr 06 '16 at 21:04
Thanks so much for the quick response! I'm able to get the original solution you proposed to work. The shorter version is giving me a function error, I'll have to look into it some more. – katharey Apr 06 '16 at 21:09
@katharey - if this works for you, do you mind marking as the answer (click the check outline left of my post)? – BruceWayne Apr 07 '16 at 13:55

score 2 · Answer 2 · answered Apr 06 '16 at 21:26

Here's a bit shorter formula to return the last delimited substring in a string.

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

Replace the delimiter with 99 spaces, then return the rightmost 99 characters. The leading characters must be spaces also, so TRIM gets rid of them.

score 0 · Answer 3 · answered Oct 31 '21 at 20:12

A simple formula approach using FilterXML might be:

    =FILTERXML("<items><i>" & SUBSTITUTE(A1,"/","</i><i>") & "</i></items>","//i[position()=last()]")

Profitting from the dynamic features of vers. 2019+ you can change the cell address to a range input, e.g. A1:A10 allowing output in a so called spill range.

VBA approach

As the VBA tag has been recently added to OP, an obvious VBA approach would be to split the string input and get the last array element via Ubound():

    Dim tmp As Variant
    tmp = Split("ABCDE/FGHI/JKL/MNOPQR", "/")
    Debug.print tmp(Ubound(tmp))     ' ~~> MNOPQR

Populate cell with substring following last / in the string

3 Answers3