text extract macro

[zroadrider]

I have a string like

[hyd][Aes][mCes][Ges][Te][hyd]...

that I am trying to remove the text from the brackets and have just the text in a row of cells so A1=hyd, A2=Aes, A3=mCes and so on.

Right now I use the text to column function with [ as the delimiter followed by a few other search and logical functions to obtain the desired results , but there should be a simple VBA macro to accomplish this.

thanks

 

[Michael M]

Hi and Welcome to the Board
Have you considered using the macro recorder to do exactly as you have just described ?

 

[p45cal]

Select a single column of cells and run:

Sub blah()
If Selection.Columns.Count > 1 Then Exit Sub
For Each cll In Selection.Cells
    xx = cll.Value
    If Left(xx, 1) = "[" Then xx = Mid(xx, 2, Len(xx))
    If Right(xx, 1) = "]" Then xx = Left(xx, Len(xx) - 1)
    zz = Split(xx, "][")
    cll.Offset(, 1).Resize(, UBound(zz) + 1) = zz
    'cll.Resize(, UBound(zz) + 1) = zz ' this line overwrites original cell.
Next cll
End Sub

 

[ZVI]

May be something like this:

Sub test()
  Const MyStr = "[hyd][Aes][mCes][Ges][Te][hyd]"
  Dim a
  a = Split(Replace(Mid(MyStr, 2), "]", ""), "[")
  Range("A1").Resize(, UBound(a) + 1).Value = a
End Sub

 

[zroadrider]

Select a single column of cells and run:

Sub blah()
If Selection.Columns.Count > 1 Then Exit Sub
For Each cll In Selection.Cells
    xx = cll.Value
    If Left(xx, 1) = "[" Then xx = Mid(xx, 2, Len(xx))
    If Right(xx, 1) = "]" Then xx = Left(xx, Len(xx) - 1)
    zz = Split(xx, "][")
    cll.Offset(, 1).Resize(, UBound(zz) + 1) = zz
    'cll.Resize(, UBound(zz) + 1) = zz ' this line overwrites original cell.
Next cll
End Sub

wow that worked great. thanks. Is there also a way to not include certain letters? Sometimes I have text like "Tes" and "Te" and would only like it to return "Te". I am trying to use the search, IF, Len, and other functions to accomplish this but I seem to be overcomplicating things.

 

[mikerickson]

Perhaps a non looping solution.

Sub test()
    Dim dataRange As Range
    With Sheet1.Range("A:A")
        With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            With .Offset(0, 1)
                .FormulaR1C1 = "=SUBSTITUTE(RC[-1],""["","""")"
                .Value = .Value
                .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                    Semicolon:=False, Comma:=False, Space:=False, Other:=True, _
                    OtherChar:="]"
            End With
        End With
    End With
End Sub

Then you could use a group Find and Replace to change all the Tes into Te

 

[Akinrotimi]

Zroadrider,

Placing this mid fx on B1 solves it:

=MID(A1,2,LEN(A1)-2)

Drag down to the desired selection.

Regards

Rotimi

 

[p45cal]

wow that worked great. thanks. Is there also a way to not include certain letters? Sometimes I have text like "Tes" and "Te" and would only like it to return "Te". I am trying to use the search, IF, Len, and other functions to accomplish this but I seem to be overcomplicating things.

Is it going to be the whole cell content like that, or only parts of a cell maybe?
Will it have to be case sensitive?

 

[zroadrider]

I have these strings in [] brackets like this which vary in length

[Hydrogen][Aes][Ces][Ges][Tes][Ad][mCes][Uk][Hydrogen]

The text in the [] are 2-3 characters with an s at the end except for the last [] which does not have an s. So I am trying to extract the below sequence

Ae Ce Ge Te Ad mCe Uk

Thanks

 

[Akinrotimi]

Zroadrider,

Placing this IF Fx on B1 solves it:

=IF(RIGHT(MID(A1,2,LEN(A1)-2),1)="s",MID(MID(A1,2,LEN(A1)-2),1,LEN(MID(A1,2,LEN(A1)-2))-1), MID(A1,2,LEN(A1)-2))

Drag down to the desired selection.

Confirm status please.

Regards

Rotimi