sub TextToColumns

[Gabriel]

Hi,

I have a lot of columns on the same worksheet. I have to apply
TextToColumns to each column. This takes me quite long. The delimiter
is an equal sign (=).

###
Sub TextToColumns()

dim usedcolumn as range

For Each UsedColumn In ActiveWorkbook.ActiveSheet

UsedRange.TextToColumns Destination:=RANGE("a1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
:=False,
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar :="=", FieldInfo:=Array(Array(1, 1), Array(2,
1)), TrailingMinusNumbers:=True
Next UsedRange

End Sub

###

I've managed to start a code but.. It probably has to do something
with the destination Range... Does anyone have a suggestion?

Thank you - Gabriel

 

[Bernie Deitrick]

Gabriel,

Try the code below (including the function, which counts the max number of =
signs in your string), which assumes that your columns start with column A,
row 1, and are contiguous from there.

HTH,
Bernie
MS Excel MVP

Sub TextToColumns()

Dim UsedColumn As Range
Dim Col2Insert As Integer
Dim i As Integer
Application.DisplayAlerts = False
For i = ActiveWorkbook.ActiveSheet.Range("A1"). _
CurrentRegion.Columns.Count To 1 Step -1
Set UsedColumn = ActiveWorkbook.ActiveSheet.Range("A1"). _
CurrentRegion.Columns(i)
Col2Insert = MaxSplit(UsedColumn)
If Col2Insert <> 0 Then
UsedColumn.Offset(0, 1).Resize(, Col2Insert).EntireColumn.Insert
UsedColumn.TextToColumns Destination:=UsedColumn.Cells(1, 1), _
DataType:=xlDelimited, _
Other:=True, OtherChar:="="
End If
Next i
Application.DisplayAlerts = True

End Sub

Function MaxSplit(inRange As Range) As Integer
Dim i As Integer
For i = 1 To inRange.Cells.Count
MaxSplit = Application.Max(MaxSplit, _
Len(inRange.Cells(i).Value) - _
Len(Replace(inRange.Cells(i).Value, "=", "")))
Next i
End Function