Right Function to 1st "\" in String

[Warren Phillips]

Is there a way I can use the "Right" function to extract any number of
characters from a string to the 1st "\" in the string. For example

\\user1\c$\outlook\user1.pst

\\user2\C$\documents and settings\user2\my documents\archive.pst

I would like to end up with just the following in a cell

user1.pst
archive.pst

To do this, I need to take all the characters from the right until the
1st "\" shows up in the string

Can anyone help

Thanks

Warren

 

[Frank Kabel]

Hi Warren
some possible solutions to get the last word after a '\'of a cell:
1. Non array formula:
=MID(TRIM(A1),FIND("^",SUBSTITUTE(TRIM(A1),"\","^",LEN(TRIM(A1))-LEN(SU
BSTITUTE(A1,"\",""))))+1,1024)

2. Array formulas
2.a) Array formula (proposed by Harlan Grove some days ago): To be
entered with CTRL+SHIFT+ENTER
=MID(TRIM(A1),MAX(IF(MID(TRIM(A1),Seq,1)="\",Seq))+1,1024)
Where 'Seq' is a defined name(goto 'Insert - Name - Define') referring
to =ROW(INDIRECT("1:1024")).


2.b) Or even shorter / using less function calls (also by Harlan
Grove):
=MID(TRIM(A1),1024+2-MATCH("\",MID(TRIM(A1),1024+1-Seq,1),0),1024)

 

[BrianB]

Only with a custom formula :-

'---------------------------------------
Public Function GETFILE(st As String)
For c = Len(st) To 1 Step -1
x = Mid(st, c, 1)
If Mid(st, c, 1) = "\" Then Exit For
Next
GETFILE = Right(st, Len(st) - c)
End Function
'-------------------------------------------

Use as normal, like :- =GETFILE(A1

 

[Frank Kabel]

Only with a custom formula :-

Hi
it's possible with worksheet function - though it's a little bit more
complicated
Frank