Extract a number from text in cell using VBA

Hi,
I'm trying to extract the number between the two "[]" in cell's text

tried to use a mix of MID and INSTR but it doesn't work for me
Can someone please help me with the code for it?
Thank you!

Why doesn't it work - error message, wrong result, nothing happens? Post your code. Show sample data.

Code:

---

Sub Demo()
MsgBox Split(Split(Selection.Cells(1).Text, "[")(1), "]")(0)
End Sub

---

Quote:

---

Originally Posted by laorko

Hi,
I'm trying to extract the number between the two "[]" in cell's text

tried to use a mix of MID and INSTR but it doesn't work for me
Can someone please help me with the code for it?
Thank you!

---

To extract the number between two square brackets in a cell's text using VBA, you can use the Split function to split the cell's text into an array of substrings, using the square brackets as the delimiter. Then, you can use the Mid function to extract the number from the second element of the array, which should be the substring between the two square brackets.


Here is an example of how you can do this:

Code:

---

Sub ExtractNumberBetweenBrackets()
    ' Define the cell containing the text
    Dim cell As Range
    Set cell = ActiveSheet.Range("A1")
' Split the cell's text into an array of substrings, using the square brackets as the delimiter
    Dim substrings() As String
    substrings = Split(cell.Value, "[]")
' Extract the number from the second element of the array, which should be the substring between the brackets
    Dim number As String
    number = Mid(substrings(1), 2)
' Display the extracted number
    MsgBox number
End Sub

---