Right Function to 1st "\" in String

  • Thread starter Thread starter Warren Phillips
  • Start date Start date
W

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
 
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)
 
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
 
Only with a custom formula :-

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