Menu
So I have an excel task that involves taking filepaths (C:\foo...) and getting just the path (that is, removing the actual file name from the path). I can't seem to get the SEARCH/FIND function to work since it is always finding the first "\" in the file path (after the drive designation) and only removes 3 or so characters.
Is there a formula that will allow me to trim after the last "\" in the filepath?
Thanks in advance for any help!
888
VBA: Remove all characters after the last space in ExcelSelect a blank cell, enter the formula =RemoveAfterLastSpace(A2) (A2 is the cell where you will remove all characters after the last space) into it, and the drag the Fill Handle to the range as you need.
You can quickly extract the text before space from the list only by using formula. 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.
Combine RIGHT, LEN & FIND Functions to Remove Text before First Space in Excel. You can use the text before a space by combining the RIGHT, LEN, and FIND functions. Using this combination, you can remove the text before 1st space if there is more than one space present in a string.
To extract the path from the full path and file name, firstly, the formula counts the number of character “\” by the LEN and SUBSTITUTE functions, then replace the last “\” with a special character “?” by the SUBSTITUTE function, finally, find the special character “?” and extract the path by using the FIND and LEFT ...
First of all, your question would be better off on superuser.com.
You can use LEFT, with FIND and SUBSTITUTE... and a couple others:
=LEFT(A1, FIND(CHAR(1), SUBSTITUTE(A1, "\", CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1, "\", ""))))-1)
LEN(A1)-LEN(SUBSTITUTE(A1, "\", "") basically gives the number of \ in the string.
SUBSTITUTE(A1, "\", CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1, "\", "")) This part substitute the last \ by a character called CHAR(1).
Then, use FIND to get the position of this character and minus 1 to remove that found character's position, to be LEFT (both figuratively and literally) with the part you need.
If you need the last backslash, remove the -1.
56
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
