Ron,
I like that Split function! I didn't know it existed. Very cool!!
Dale

"Ron Rosenfeld" wrote:

On Wed, 27 Oct 2004 11:15:02 -0700, "gaba"
wrote:

How can I extract the middle word in a cell ? I'm copying the value from cell
"B91" and pasting in "H3"
the original value shows
C:\elandata\Dataset\101504-1\Blank.001
I need to keep 101504-1

I got the first part working: 101504-1\Blank.001
How can I get rid of \Blank.001? I don't know how many characters are going
to be after "\", it changes all the time.

Sub GetName ()
Dim X As String
Dim inside As Long
Dim D As String

Range("H3").Value = Range("B91").Value
X = Range("H3").Value
inside = InStr(X, "\")
D = (Trim$(Mid$(X, inside + 18)))
Range("H3").Value = D
End Sub

Thanks in advance


For a worksheet formula solution:

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

For a VBA solution (XL2000 or later):

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

temp = Split(str, "\")
foo = temp(UBound(temp) - 1)

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


--ron