0

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

chowpay
  • 1,515
  • 6
  • 22
  • 44

3 Answers3

2

What you want is a reverse string search to grab everything to the right of the last "/".

The formula (assume one of your strings is in A1) would be

=IF(ISERROR(FIND("/",A1)),A1,RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))))

Take a look at this answer and this site for an explanation of why this works.

PeterT
  • 8,232
  • 1
  • 17
  • 38
  • ok wow that was way more complicated than I thought. Ended up doing this in python by the time you posted this. But it looks good so I'll mark it as the answer for someone who needs this in excel – chowpay Jun 13 '18 at 03:05
2

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

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

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27
  • @chowpay It is cannibalized version of the formula explained here: http://www.excelfox.com/forum/showthread.php/333-Get-Field-from-Delimited-Text-String?s=36b969a7a10fc3b2ef7d2b7dfefc7bab – shrivallabha.redij Jun 15 '18 at 15:04
1

Take a copy and Replace */ with nothing.

pnuts
  • 58,317
  • 11
  • 87
  • 139