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

Johnny C · Jun 21, 2011

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)

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 · Jun 21, 2011

Try like this

Code:

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 · Jun 21, 2011

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 · Jun 21, 2011

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 · Jun 21, 2011

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.

Code:

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 · Jun 22, 2011

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

Code:

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 · Mar 29, 2018

VoG said:

Try like this

Code:

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 · Mar 29, 2018

Sontauzo said:
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 · Mar 29, 2018

Original Data - This is just a sample. There are about 500 lines in the spreadsheet.

Products

Hammer [3102]

Belt [99]

Nails [120]

Screws [33850]

Paint [553152]

Ladder [162]

Screwdriver [1]

Cart [32]

Saw [995]

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.

Products
3102
99
120
33850
553152
162
1
32
995

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

Rick Rothstein · Mar 29, 2018

Give this macro a try...

Code:

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

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

Johnny C

Well-known Member

Excel Facts

VoG

Legend

Johnny C

Well-known Member

MrKowz

Well-known Member

Scott Huish

MrExcel MVP

Johnny C

Well-known Member

Sontauzo

New Member

Rick Rothstein

MrExcel MVP

Sontauzo

New Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

Well-known Member

Excel Facts

Legend

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock