make all cols in TextToColumn text

[stuck]

I want to force all columns that end up holding the output from a TextToColumns command to be text an I know that I need something like:

Code: Select all

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Comma:=True, _
        FieldInfo:=Array(Array(1, 2), Array(2,2))

but the arrays I have are not all the same size so how do I make 'FieldInfo' cope with a different number of array elements?

Thanks,

Ken

[HansV]

For example:

Code: Select all

Sub T2C()
    Dim arrValue As Variant
    Dim arrFormats As Variant
    Dim i As Long
    Dim n As Long
    ' Split the first cell into an array
    arrValue = Split(Selection.Cells(1).Value, ",")
    ' Number of columns - 1
    n = UBound(arrValue)
    ReDim arrFormats(n)
    ' Fill array with format specifications
    For i = 0 To UBound(arrValue)
        arrFormats(i) = Array(i + 1, xlTextFormat)
    Next i
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Comma:=True, _
        FieldInfo:=arrFormats
End Sub

[stuck]

Umm, the code runs without error, which is a considerable step up on all my attempts so far but it's not giving what I expected. I think the problem is with the line that splits the first cell into an array because that cell is not comma separated, it's like a title. It the second row that holds the first line of 'real' data.

I will look at this again in the morning, when my brain is feeling fresher.

Meanwhile, thanks again Hans!

Ken

[HansV]

If the first cell is not comma-separated, either change the selection (and the destination), or use

Code: Select all

    arrValue = Split(Selection.Cells(2).Value, ",")

[stuck]

I'm back to announce I'd solved it all by myself (after a short break / doing something different). Only to discover that Hans had already solved it for me 20 mins ago.

Yes, all I needed to do was tweak the arrvalue line.



Ken

[stuck]

I'm back because I've discovered I need another tweak. Instead of:

Code: Select all

arrValue = Split(Selection.Cells(2).Value, ",")

I need something more general:

Code: Select all

arrValue = Split(Selection.Cells(n).Value, ",")

where n is the number of the row, in Col A, where the length of the string in the cell is MAX. I think to get this I'll first have to loop down every row and compare the LENgth of the current cell with a variable (say maxLEN) and if it's greater update the variable and capture the current row number in another variable, say maxRow. Once I know maxRow the code would become:

Code: Select all

arrValue = Split(Selection.Cells(maxRow).Value, ",")

Does this sound like a plan or is there a more elegant solution?

Ken
NB the data in Col A that I'm parsing into columns is not contiguous, i.e. it includes (single) empty rows, which means the bottom of the range is reached when the next two cells in Col are empty.

[HansV]

Try this version:

Code: Select all

Sub T2C()
    Dim arrFormats As Variant
    Dim r As Long
    Dim m As Long
    Dim v As Long
    Dim i As Long
    Dim n As Long
    m = Range("A" & Rows.Count).End(xlUp).Row
    ' Determine max number of columns
    For r = 2 To m
        v = Len(Range("A" & r).Value) - Len(Replace(Range("A" & r).Value, ",", ""))
        If v > n Then n = v
    Next r
    ReDim arrFormats(n)
    ' Fill array with format specifications
    For i = 0 To n
        arrFormats(i) = Array(i + 1, xlTextFormat)
    Next i
    Range("A1:A" & m).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Comma:=True, _
        FieldInfo:=arrFormats
End Sub

[stuck]

Neat, I'd never have thought of counting the number of fields in the string thus:

Code: Select all

v = Len(Range("A" & r).Value) - Len(Replace(Range("A" & r).Value, ",", ""))

as a way of determining the number of columns required but it's obvious now.

It's lunchtime, what's your tipple? or ? I suppose though a might be better if you have to go back to work this afternoon.



Ken

[HansV]

A will be most welcome - thanks!