Function to extract remaining string after last backslash

excel vba replace excel-formula find-replace
user664481 picture user664481 · Dec 12, 2015 · Viewed 42k times · Source

I need an Excel function that can extract a string after last \ from a path and if no \ found then take the whole string. For example:

D:\testing\rbc.xls                     output will be   rbc.xls
D:\home\testing\test1\script1.sql      output will be   script.sql
script 3.txt                           output will be   script 3.txt

Answer

Davesexcel picture Davesexcel · Dec 12, 2015

1.Change all the "\" to spaces, the number of spaces is determined by the number of characters in the cell

2.Use the right function to extract the right of the string based on the number of characters in the cell.

3.Use the trim function to remove the spaces.

enter image description here

Your results will be.

enter image description here

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

As suggested, one way to do this without formulas or vba would be to use "Find/Replace". Hit Ctrl & "H" keys and do the following.

Find what *\ and replace with nothing

enter image description here

The VBA code for that would be

Sub ReplaceIt()
    Columns("A").Replace What:="*\", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
End Sub

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More