Extracting just middle text
 

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
--
gaba :)

Extracting just middle text
 

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

-----Original Message-----
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
--
gaba :)
.

Extracting just middle text
 

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

Extracting just middle text
 

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



"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
--
gaba :)

Extracting just middle text
 

On Wed, 27 Oct 2004 14:41:06 -0400, 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

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
----------------------


--ron

Extracting just middle text
 

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

Extracting just middle text
 

On Wed, 27 Oct 2004 11:57:08 -0700, "Dale Preuss"
wrote:

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

Extracting just middle text
 

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

Gaba :)

"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

Extracting just middle text
 

On Wed, 27 Oct 2004 12:47:01 -0700, "gaba"
wrote:

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

You're welcome. Thanks for the feedback.


--ron

Extracting just middle text
 

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


"Dale Preuss" wrote:

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



"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
--
gaba :)