delete all text to the left of???

  • Thread starter Thread starter Sheila
  • Start date Start date
S

Sheila

I want to put a list in column A that will be the paths to the files I
have, (ie c:\Program Files\Common
Files\Microsoft\Stationery\signature.htm), but I wqant to delete the
path in column B, so I need a formula that will delete everything to
the left of "\". Can anyone help??

TIA

Sheila
 
You can create a formula, but I would go for a function in VBA instead.

You can do something like:

Function splitName(text As String) As String
txt = Split(text, "\")
splitName = txt(UBound(txt))
End Function

Which will return "signature.htm". Add the function to a module so that it
is available in the worksheet (or create an addin with the function)

Regards
Robert
 
Assuming your string is A1
Here is an array formula. Enter with Ctrl+Shift+Enter

=MID(A1, MAX(--(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)="\") *
ROW(INDIRECT("1:" & LEN(A1)))) + 1, LEN(A1))

I'm not that great at the ROW(INDIRECT( trick..
I've noticed some who are quite confident at it. I'd be happy to see
corrections.
 
Sheila wrote...
I want to put a list in column A that will be the paths to the files I
have, (ie
c:\Program Files\Common Files\Microsoft\Stationery\signature.htm),
but I wqant to delete the path in column B, so I need a formula that
will delete everything to the left of "\". Can anyone help??
Click to expand...

If you need this dynamic, then use Rob's formula. If this would be
mostly static, then copy the pathnames from col A into col B, then Edit
 
Back
Top