Text To Columns - Delimited - Next - Finish, macro

Hi,

I am needing a macro that will convert text to columns for the formatting.

For sheet 'Form to Enter", I need column E to be 'DATA - Text To Columns - Delimited - Next - Finish'. Then, I need the same thing for sheet "New Form Enter" for column G. I attached my workbook with the macro I have so far. Any help is appreciated!

Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim LO As ListObject
    Dim lstCol As ListColumn

    Set ws = Worksheets("Form to Enter")
    Set LO = ws.ListObjects(1)
    Set lstCol = LO.ListColumns(5)
    
    With lstCol.Range
        .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
                       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
                       Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
                       Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    End With

    'second sheet in other way
    With Worksheets("New Form Enter").ListObjects(1).ListColumns(7).Range
        .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
                       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
                       Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
                       Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    End With
End Sub

Artik

Hi Artik, the first with worked good for text to columns, but the second with statement did not. Do I need to change some of the set methods?

E7587, E2654, E698547 are not numbers, so it's hard for the trick of converting text to numbers using TextToColumns to work.

Artik