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!
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.
How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Sub Demo() MsgBox Split(Split(Selection.Cells(1).Text, "[")(1), "]")(0) End Sub
Cheers
Paul Edstein
[Fmr MS MVP - Word]
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
Last edited by Aussiebear; 12-09-2022 at 03:30 AM. Reason: Added code tags to supplied code
If you only ever do what you can , you'll only ever be what you are.