excel vba- extract text between 2 characters

Question

If i had this column:

ColA
-----
NUMBER(8,3)
NUMBER(20)

I need a VBA function that would go (note these start and end string would only ever appear once in a cell):

extract_val(cell,start_str,end_str)

ie. extract_val(A1,"(",")") and give the results:

8,3
20

I only need to use this function within other vba code not by putting it as a formula on the sheet.

UPDATE (thanks to the answer, i settled on:)

---------------------------
Public Function extract_value(str As String) As String
Dim openPos As Integer
Dim closePos As Integer
Dim midBit As String
 On Error Resume Next
openPos = InStr(str, "(")
 On Error Resume Next
closePos = InStr(str, ")")
 On Error Resume Next
midBit = mid(str, openPos + 1, closePos - openPos - 1)
If openPos <> 0 And Len(midBit) > 0 Then
extract_value = midBit
Else
extract_value = "F"
End If
End Function

Public Sub test_value()
MsgBox extract_value("NUMBER(9)")
End Sub

You might want to make the closePos line to start at the position where it found the openPos, otherwise you could find a closing bracket BEFORE the open bracket, but given your examples this is unlikely. — Harag, Sep 05 '11 at 15:25
slightly off-scope hint: http://stackoverflow.com/questions/2757477/trap-error-or-resume-next — Mathieu Guindon, Jan 23 '13 at 04:51
For a one-line method, use [Split](https://stackoverflow.com/a/73736725/3527297). — Gustav, Sep 16 '22 at 07:00

score 31 · Accepted Answer · edited Sep 03 '11 at 18:58

You can use instr to locate a character within the string (returning the position of '(' for example). You can then use mid to extract a substing, using the positions of '(' and ')'.

Something like (from memory):

dim str as string
dim openPos as integer
dim closePos as integer
dim midBit as string

str = "NUMBER(8,3)"
openPos = instr (str, "(")
closePos = instr (str, ")")
midBit = mid (str, openPos+1, closePos - openPos - 1)

You may want to add error checking in case those characters don't occur in the string.

score 1 · Answer 2 · answered Oct 06 '15 at 08:32

If the string is “Value of A is [1.0234] and Value of B is [3.2345]”

If you want to extract the value of B i.e., 3.2345, then

firstDelPos = InStrRev(textline, “[“) ‘ position of start delimiter
secondDelPos = InStrRev(textline, “]”) ‘ position of end delimiter

stringBwDels = Mid(textline, firstDelPos + 1, secondDelPos – firstDelPos – 1) ‘ extract the string between two delimiters

MsgBox (stringBwDels) ‘ message shows string between two delimiters

Zv Lfdl · Answer 3 · 2018-12-02T02:23:00.573

If I have a cucumber table as test data in a cell, and need to access the value under 'Header 1' between 4th and 5th pipes, below is how I done it. My table example as below in cell D7 :

*Cell D7:

code to access 'abcd' which is found after 4th occurrence of '|' and before 5th occurrence of '|'

Dim sheet5 As Worksheet
Dim i As Integer
Dim k As Integer
Dim openPos As Long
Dim clsPos As Long
Dim textBetween as String

'Using for loop to find 4th occurrence of pipe '|' for openPos
For i = 1 To 4
openPos = InStr(openPos + 1, sheet5.Range("D7"), "|", vbTextCompare)
Next i

'Using for loop to find 5th occurrence of pipe '|' for clsPos
For k = 1 To 5
clsPos = InStr(clsPos + 1, sheet5.Range("D7"), "|", vbTextCompare)
Next k

'Displaying the value between openPos and clsPos
txtBetween = Mid(sheet5.Range("D7").Value, openPos + 1, clsPos - openPos - 1)
MsgBox ("Current Header 1 value: " & txtBetween)

Not sure why my answer get downvoted, please leave a comment letting me know why it did. I know previous two answers already helped others (me especially) and i am contributing to help find the characters via a loop in case the characters are further within a string . thanks — Zv Lfdl, Dec 06 '18 at 01:14

score 0 · Answer 4 · answered Nov 04 '20 at 21:18

I know this question has been marked answered, but i thought i will add my code based on the updated question's function. I made a function that will allow it to parse out multiple.

Sub testextract()
 s = extract_values("This is [what i want to keep][And this]")
 Debug.Print (s)
  End Sub
 Function extract_values(str As String, Optional openStr = "[", Optional closeStr = "]") As String
 Dim openPos As Integer
 Dim closePos As Integer
 Dim midBit As String
 prevOpen = 1
 prevClose = 1
 Dim keep As String
 While prevOpen <> 0
    On Error Resume Next
    openPos = InStr(prevOpen, str, openStr)
    On Error Resume Next
   closePos = InStr(prevClose, str, closeStr)
    On Error Resume Next
    midBit = Mid(str, openPos + 1, closePos - openPos - 1)
    If openPos <> 0 And Len(midBit) > 0 Then
    keep = keep & openStr & midBit & closeStr
   End If
   If openPos = 0 Or prevClose = 0 Then
       i = 0
   Else
      i = 1
  End If
  prevOpen = openPos + i
   prevClose = closePos + i
 Wend
 extract_values = keep
 End Function

excel vba- extract text between 2 characters

4 Answers4

Linked

Related