CODEWITHSUNDEEP
excelexcel-formula
Damo
Damo

Reputation: 2070

Split string by first delimiter

I have a column with a long list of folder and file names. The folders and file names vary. I want to extract the file name from the column into another column but I struggling to do this in Excel.

Example of column data:(files and folder altered to hide details that should not be public)

c:\data\1\nc2\media\ss\system media\ne\d - wnd enging works v5.swf
c:\data\1\nc2\media\ss\special campaigns\samns dec 2012\trainerv5.swf
C:\Local\Messages\17362~000000001~20131231235910~4.MUF
c:\data\1\nc2\media\ss\system media\tl\nd - tfl statusv4.swf
c:\data\1\nc2\media\ss\system media\core\ss_bagage v2.swf

I know I should be able to search from the right to the first occurence of "\" but I can't figure out the syntax.

Many thanks

UPDATE:

Formula =RIGHT(B2,LEN(B2)-SEARCH("\",B2,1)) should work, but it shows incorrect results. But If I change it to search for "." it pulls out the file extension. So there is a key item I'm missing

Upvotes: 2

Views: 11341

Answers (4)

Tim
Tim

Reputation: 1826

If you need more generic string parsing and are willing to use a little bit of VBA, you can use the split function as suggested by Jamie Bull in his answer to this question on SuperUser.

His function will use any character you choose to split the string into segments and return whichever segment you choose.

I've copied Jamie's function here for convenient reference:

Function STR_SPLIT(str, sep, n) As String
    Dim V() As String
    V = Split(str, sep)
    STR_SPLIT = V(n - 1)
End Function

Upvotes: 0

chuff
chuff

Reputation: 5876

There are several different ways to get the text following the last slash in a string, including the following formula. In this example, H15 is the cell containing the string to search. If it can't find a slash, it returns the "-" (dash) character.

   =iferror(RIGHT(H15,LEN(H15)-SEARCH("|",SUBSTITUTE(H15,"/","|",LEN(H15)-LEN(SUBSTITUTE(H15,"/",""))))),"-")

The formula first finds the number of slashes in the string. LEN gives the total length of the string, and LEN of the string without slashes after using SUBSTITUTE to eliminate the slashes in the original string - the difference is the number of slashes.

Then, you substitute in a marker character(I used "|") for the last slash. By searching for the marker, you find where the bit after the slash starts. The total length of the string minus where the marker starts tells you how many characters to take from the right, which you then do.

Upvotes: 0

Maulik Shah
Maulik Shah

Reputation: 421

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

copy it in any column say b drag down,you are done

Upvotes: 3

InContext
InContext

Reputation: 2501

VBA is a more efficient option if you have many files to parse. Create a module and add the below:

Function GetFileName(file As String) As String

    Set fso = CreateObject("Scripting.FileSystemObject")

    GetFileName = fso.GetFileName(file)

End Function

Upvotes: 1

Related Questions