Extracting just middle text

[Guest]

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

 

[Frank Stone]

hi,
Look up the =mid function in help
it looks like to me it would be
=mid(b91,20,8) this formula would be in H3.
Regards
Frank

 

[Ron Rosenfeld]

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

 

[Guest]

Gaba,
Try this...

Sub ShowGetName()
MsgBox GetName("C:\elandata\Dataset\101504-1\Blank.001")
End Sub

Function GetName(strOldDirectory As String)
Dim bolAddCharacters As Boolean
Dim i As Integer

For i = Len(strOldDirectory) To 1 Step -1
If Mid(strOldDirectory, i, 1) = Application.PathSeparator Then
If Not bolAddCharacters Then
bolAddCharacters = True
Else
Exit For
End If
ElseIf bolAddCharacters Then
GetName = Mid(strOldDirectory, i, 1) & GetName
End If
Next
End Function

You can use it as a worksheet function or call it from another VBa procedure.

Dale Preuss

 

[Ron Rosenfeld]

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

Thanks to Dale, a slight modification of my UDF:

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

temp = Split(str, Application.PathSeparator)
foo = temp(UBound(temp) - 1)

End Function

 

[Guest]

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

 

[Ron Rosenfeld]

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

I think it's in the version of VBA that shipped with XL2000 and later.


--ron

 

[Guest]

Thanks so much, everybody
Ron, I've used your function foo and it work great!

Gaba

 

[Ron Rosenfeld]

Thanks so much, everybody
Ron, I've used your function foo and it work great!

You're welcome. Thanks for the feedback.


--ron

 

[Guest]

Function SeparateTextWithChr(TextToSeparate, TextNo, WithChr)
p = 1
i = 1
j = 0

X = Application.WorksheetFunction.Trim(TextToSeparate) & WithChr

Do Until p = 0
p = InStr(i, X, WithChr, 1)
j = j + 1
NewStr = Application.WorksheetFunction.Trim(Right(Left(X, p - 1), p
- i))

If j = TextNo Then
Exit Do
End If
If p = 0 Then Exit Do
i = p + 1
Loop
SeparateTextWithChr = NewStr
End Function