Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Extracting just middle text
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 83
Default 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 :)
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 134
Default 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 :)
.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,651
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 36
Default 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 :)
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,651
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 36
Default 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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,651
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 83
Default 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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,651
Default 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
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default 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 :)
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add a character to the middle of a text string Glynn Taylor Excel Discussion (Misc queries) 3 April 3rd 23 02:38 PM
Adding contents of another cell to the middle of a line of text [email protected] Excel Worksheet Functions 1 September 22nd 08 08:06 PM
How to extract text from middle of a string Makelei Excel Worksheet Functions 18 July 9th 08 09:03 PM
Extract text in middle using Mid and Find or Search Karin Excel Discussion (Misc queries) 5 January 24th 08 08:01 AM
Can you put a formula in the middle of text? Weasel Excel Discussion (Misc queries) 6 March 24th 06 10:18 AM


All times are GMT +1. The time now is 08:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement