Extract string from between 2 delimiters in a string (VBA)

[Johnny C]

Hi

I've a function 'piece' that extracts text from between 2 delimiters in a string. E.g. text = "123-abc-567", piece(text,"-",2)="abc"

It currently goes through character by character looking for the delimiters.

Some time ago, someone mentioned on here that there was a function in XL2007 VBA that did it without the character loop.

I assume it's not worksheetfunction.find; can anyone tell me what it is?

(here's my code)

Function piece(Searchstring As String, Separator As String, IndexNum As Integer) As String
Dim i, SepCount, SepLen, StartPos, EndPos As Integer
Dim TestStr As String
SepLen = Len(Separator)
SepCount = 0
StartPos = 0
EndPos = 0
For i = 1 To Len(Searchstring) - (SepLen - 1)
    TestStr = Mid(Searchstring, i, SepLen)
    If TestStr = Separator Then
        SepCount = SepCount + 1
        If SepCount = IndexNum - 1 Then StartPos = i + 1
        If SepCount = IndexNum Then EndPos = i
    End If
Next i
If StartPos = 0 And EndPos = 0 Then
    piece = ""
    Exit Function
End If
If StartPos = 0 Then StartPos = 1
If EndPos = 0 Then EndPos = Len(Searchstring) + 1
piece = Mid(Searchstring, StartPos, EndPos - StartPos)
End Function

 

[VoG]

Try like this

Sub atest()
Dim s As String, piece As String, i As Long, j As Long
s = "123-abc-567"
i = InStr(s, "-")
j = InStrRev(s, "-")
piece = Mid(s, i + 1, j - i - 1)
MsgBox piece
End Sub

 

[Johnny C]

Cheers. That would work where there are 3 'pieces, but this is a generic function that needs to work with any number of chunks of delimted text.

I could use Instr recursively until it returns a 0, but it's still a loop.

Maybe I dreamt it but I'm sure Norie or someone said there was a single line (non-loop) function that would do it.

The reason I'd rather a single line version is that it's running across 300,000 cells in a macro that takes an hour without the extra 'piece' processing. So if I can avoid a loop I will.

 

[MrKowz]

Can you please provide some more inputs with your desired output? I'm thinking this might be able to be handled using Split.

 

[Scott Huish]

In your example of 123-abc-567, what if you had specified 1, would you want to return 123? Technically that doesn't meet your criteria of being between the two delimiters. The first and only case that that happens is abc which would then be 1.

If what you really want is the section separated by the specified delimiter, then this should work.

Function piece(Searchstring As String, Separator As String, IndexNum As Integer) As String
Dim t
t = Split(Searchstring, Separator)
If UBound(t) > 0 Then piece = t(IndexNum - 1)
End Function

Excel Workbook
	A	B
1	123-abcdefghi-jkl-mnop	123
2	123-456-789	456
3	123-abcdefghi-jkl-mnop	mnop
Sheet1

 

[Johnny C]

Brilliant hotpepper - thanks! That's what I was looking for.

Returning an array has the advantage that I can then pull a number of chunks, so piece("a-b-c-d","-",2,3) = "b-c".

Function Piece(Searchstring$, Separator$, Index1%, Optional Index2%) As String
Dim t, IndexCount%
Piece = ""
t = Split(Searchstring, Separator)
If UBound(t) + 1 < Index1 Then Exit Function
If UBound(t) + 1 < Index2 Then Index2 = UBound(t) + 1
If Index2 = 0 Or Index2 <= Index1 Then
    If UBound(t) > 0 Then Piece = t(Index1 - 1)
Else
    For IndexCount = Index1 To Index2
        Piece = Piece & t(IndexCount - 1)
        If IndexCount <> Index2 Then Piece = Piece & Separator
    Next IndexCount
End If
End Function

 

[Sontauzo]

Try like this

Sub atest()
Dim s As String, piece As String, i As Long, j As Long
s = "123-abc-567"
i = InStr(s, "-")
j = InStrRev(s, "-")
piece = Mid(s, i + 1, j - i - 1)
MsgBox piece
End Sub

Sorry to resurrect this thread, but I am looking for something similar. I have a number that is contained in brackets that i need to extract. There is also text in the cell with the number, so I was trying to write a macro that found the brackets [] and returned what was inside. With the help of this post, I was able to get close, but I keep getting a Type Mismatch error. I am sure it is an easy fix, but I'm stumped. Here is the code that I have so far.

Sub erx()
Dim i As Long
Dim wke As Worksheet
Set wke = Worksheets("Inventory")
i = 2
Dim s As String, piece As String, k As Long, j As Long
Dim erx As String


Do While wke.Cells(i, "G").Value <> ""


s = wke.Cells(i, "G").Value
k = InStr(s, "[")
j = InStrRev(s, "]")
Mid(s, k + 1, [j - k - 1]) = piece <--error occurs on this line
piece = wke.Cells(i, "G").Value




Loop
End Sub

 

[Rick Rothstein]

Sorry to resurrect this thread, but I am looking for something similar. I have a number that is contained in brackets that i need to extract. There is also text in the cell with the number, so I was trying to write a macro that found the brackets [] and returned what was inside. With the help of this post, I was able to get close, but I keep getting a Type Mismatch error. I am sure it is an easy fix, but I'm stumped. Here is the code that I have so far.

Sub erx()
Dim i As Long
Dim wke As Worksheet
Set wke = Worksheets("Inventory")
i = 2
Dim s As String, piece As String, k As Long, j As Long
Dim erx As String


Do While wke.Cells(i, "G").Value <> ""


s = wke.Cells(i, "G").Value
k = InStr(s, "[")
j = InStrRev(s, "]")
Mid(s, k + 1, [j - k - 1]) = piece <--error occurs on this line
piece = wke.Cells(i, "G").Value




Loop
End Sub

It would be far more helpful to us if you posted a few representative samples of what your data looks like and what you want to retrieve from them. Also, tell us in what cells your data is in and what cells you want the output to go to.

 

[Sontauzo]

Original Data - This is just a sample. There are about 500 lines in the spreadsheet.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Products
[/TD]
[/TR]
[TR]
[TD]Hammer [3102][/TD]
[/TR]
[TR]
[TD]Belt [99]
[/TD]
[/TR]
[TR]
[TD]Nails [120][/TD]
[/TR]
[TR]
[TD]Screws [33850][/TD]
[/TR]
[TR]
[TD]Paint [553152][/TD]
[/TR]
[TR]
[TD]Ladder [162][/TD]
[/TR]
[TR]
[TD]Screwdriver [1][/TD]
[/TR]
[TR]
[TD]Cart [32][/TD]
[/TR]
[TR]
[TD]Saw [995][/TD]
[/TR]
</tbody>[/TABLE]


Right now the information above is in Column G. It would be ideal if the macro left the number in the same cell, but if it needs to go to a different column, that is OK too. Also, I am running the macro from a different sheet which is why I put the worksheet variable in there.

Ultimately I am looking for output like this.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Products
[/TD]
[/TR]
[TR]
[TD]3102[/TD]
[/TR]
[TR]
[TD]99
[/TD]
[/TR]
[TR]
[TD]120[/TD]
[/TR]
[TR]
[TD]33850[/TD]
[/TR]
[TR]
[TD]553152[/TD]
[/TR]
[TR]
[TD]162[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]32[/TD]
[/TR]
[TR]
[TD]995
[/TD]
[/TR]
</tbody>[/TABLE]

Sorry that I did not include this before. Thank you for your help.

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub NumbersOnly()
  With Range("G2", Cells(Rows.Count, "G").End(xlUp))
    .Value = Evaluate(Replace("IF(@="""","""",MID(LEFT(@,LEN(@)-1),FIND(""["",@)+1,LEN(@)))", "@", .Address(0, 0)))
  End With
End Sub[/td]
[/tr]
[/table]