Extract a number from text in cell using VBA

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

[June7]

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

[macropod]

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

[Grade4.2]

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:


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