String manipulation!!

[Guest]

Hi,

In my Excel sheet I have a cell with the following string:

Field\SM-A1\SEM A P03\Hydraulic sensors\PT01-LP Supply

What I need to do is extract everything after the 2nd backslash and before
the 3rd.
Leaving:

SEM A P03

I can't just count as the value could change from either end i.e SM-A1 could
become SM-A100. So as the string will change, I need to test the string, and
I can't think how....

Any help would be great!
Thanks in advance

Manny

 

[keepITcool]

you could use the very handy split function

if you have 10000 function calls it's maybe not the fastest solution,
but it's ultra easy.

Sub Parser()
'Note works only for xl2000+
Dim path$, aTmp, sRes$
path = "Field\SM-A1\SEM A P03\Hydraulic sensors\PT01-LP Supply"

'note split will give a 0based array
sRes = Split(path, "\")(2)
MsgBox sRes

End Sub



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Ron Rosenfeld]

Hi,

In my Excel sheet I have a cell with the following string:

Field\SM-A1\SEM A P03\Hydraulic sensors\PT01-LP Supply

What I need to do is extract everything after the 2nd backslash and before
the 3rd.
Leaving:

SEM A P03

I can't just count as the value could change from either end i.e SM-A1 could
become SM-A100. So as the string will change, I need to test the string, and
I can't think how....

Any help would be great!
Thanks in advance

Manny

As a worksheet function:

=MID(A1,FIND("~",SUBSTITUTE(A1,"\","~",2))+1,
FIND("~",SUBSTITUTE(A1,"\","~",3))-
FIND("~",SUBSTITUTE(A1,"\","~",2))-1)

As a VBA function in Excel 2000 and later:

================
Function foo(str As String)
Dim temp

temp = Split(str, "\")
foo = temp(2)

End Function
==================


--ron