Search code examples
exceltext-to-column

excel text to column delimiter starting from end of the row

I have strings in a column that look like this

/name/safsdf/231232/asesa/filename.mp4
/ds2/sasdsfsdf/2fd32/a234sa/filenameb.mp4
...
...

so text to column works great i can easily make a column that has just the filenames without the folders.

My problem is its inconsistent.

so some rows have more or less than 5 / which screws up the column. For example:

/name/safsdf/231232/asesa/filename.mp4
/ds2/sasdsfsdf/2fd32/a234sa/filenameb.mp4
/ds3/123/12321/123/123/filenamec.ts
...

will result in the last column being:

filename.mp4
filenameb.mp4
123

I'm looking for the result to be:

filename.mp4
filenameb.mp4
filenamec.ts

Is there a way to do text to colum in reverse where it starts looking for delimiters at the end of the row? This will give me a list of proper filenames

Solution

  • You already have an answer. Here is one more approach:

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