delete all text to the left of???

[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

 

[cmart02]

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

 

[Rob van Gelder]

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.

 

[Harlan Grove]

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??

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