Menu
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
434
Select a blank cell, and type this formula =LEFT(A1,(FIND(" ",A1,1)-1)) (A1 is the first cell of the list you want to extract text) , and press Enter button. Tips: (1) If you want to extract text before or after comma, you can change " " to ",".
SQL Server LEFT() Function The LEFT() function extracts a number of characters from a string (starting from left).
To get text following a specific character, you use a slightly different approach: get the position of the character with either SEARCH or FIND, subtract that number from the total string length returned by the LEN function, and extract that many characters from the end of the string.
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.
Your results will be.
=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
The VBA code for that would be
Sub ReplaceIt()
Columns("A").Replace What:="*\", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
