Extract text in square brackets

[Apple08]

Hi everyone

i have data in column I with square brackets e.g. Cell I2 is [W12345678] Project Plan. I have to extract the data in the bracket to column H, then Cell H2 is W12345678, and I2 is Project Plan.

the length of bracket text is various and there are various number of rows. Last row can be based on column A.

Any help is appreciated!

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub SplitSquareBracketData()
  Dim R As Long, Data As Variant, Result As Variant, Txt() As String
  Data = Range("I2", Cells(Rows.Count, "I").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 2)
  For R = 1 To UBound(Data)
    Txt = Split(Data(R, 1), "]", 2)
    Result(R, 1) = Mid(Txt(0), 2)
    Result(R, 2) = Trim(Txt(1))
  Next
  Range("H2").Resize(UBound(Result), 2) = Result
End Sub[/td]
[/tr]
[/table]

 

[Scott Huish]

Since you are replacing the text in the source cell, you will need VBA:

Sub SplitBrackets()
Dim c As Range, d As Range
For Each c In Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row)
    Set d = c.Offset(, -1)
    d = Replace(Left(c, InStr(c, "]") - 1), "[", "")
    c = Trim(Replace(c, "[" & d & "]", ""))
Next
End Sub

 

[MikeDBMan]

Sub ExtractBrackets()
Dim X, FindPoint As Integer
Dim Extract1, Extract2 As String
X = 1

Do While True
    If Left(Cells(X, 9).Value, 1) = "[" Then
        Let FindPoint = InStr(1, Cells(X, 9).Value, "]", vbTextCompare)
        Let Extract1 = Mid(Cells(X, 9).Value, 2, FindPoint - 2)
        Let Extract2 = Trim(Right(Cells(X, 9).Value, Len(Cells(X, 9).Value) - FindPoint))
        Cells(X, 8).Value = Extract1
        Cells(X, 9).Value = Extract2
    Else
        Exit Do
    End If
    X = X + 1
Loop
End Sub

Hard to know what to do about what is in column A since you don't say anything about it other than to use it. So I ignored it.

 

[Rick Rothstein]

Since you are replacing the text in the source cell, you will need VBA:

Sub SplitBrackets()
Dim c As Range, d As Range
For Each c In Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row)
    Set d = c.Offset(, -1)
    d = Replace(Left(c, [B][COLOR="#FF0000"]InStrRev(c, "]") [/COLOR][/B]- 1), "[", "")
    c = Trim(Replace(c, "[" & d & "]", ""))
Next
End Sub

Just to point out... while the OP did not give any indication that this is the case, but if the description part of the cell's text contained a square bracketed substring, your code would find its ending square bracket instead of the one located after the "code" number. Just out of curiosity, why would you not simply use a normal InStr function call to locate that square bracket and avoid the possible future problem?

 

[shg]

Another way, sans loop:

Sub Apple()
  Columns("I").Copy Columns("H")
  Columns("H").Replace "]*", ""
  Columns("H").Replace "*[", ""
  Columns("I").Replace "*] ", ""
End Sub

 

[Rick Rothstein]

Another way, sans loop:

Sub Apple()
  Columns("I").Copy Columns("H")
  Columns("H").Replace "]*", ""
  Columns("H").Replace "*[", ""
  Columns("I").Replace "*] ", ""
End Sub

I like this code, however, there are two potential problems with it...

1) If there is not always a space after the closing bracket, the Column I value will remain unchanged.

2) If the description part of the text contains a substring in square brackets, then your code will lock onto it for the value in Column I rather than the closing bracket around the "code" number.

Here is another non-looping macro that is not affected by the above should they occur...

[table="width: 500"]
[tr]
	[td]Sub SplitBrackets()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "I").End(xlUp).Row
  Range("H2:H" & LastRow) = Evaluate(Replace("IF({1},MID(LEFT(I2:I#,FIND(""]"",I2:I#)-1),2,99))", "#", LastRow))
  Range("I2:I" & LastRow) = Evaluate(Replace("IF({1},TRIM(MID(I2:I#,FIND(""]"",I2:I#)+1,LEN(I2:I#))))", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

 

[Apple08]

Thank you so much everyone! I can't believe that there are so many different options for the code. You are all genius!

I appreciated very much for all of your help!