Macro to split text to columns by maximum number of words in a cell

[tonywatsonhelp]

Hi Everyone,
bit of a Saturday morning challenge for you
In Column K I have a list of cells that contain search engine sentences.
So Row 1 is the Header "Search Words"
and it can be any amount of rows.

So What i'm looking for is a macro that can split up my words into their own columns.
now the problem is I don't know how many words this is as there can be just 1 or several,
So heres what I'd like please help if you can.

A Macro that when run seperates column K using the text to columns method spliting at spaces,
Then I simply need it to tell me how many columns where used and what the last column is
for example msgbox" Your data was split acrros 21 columns with the last column being AA!" for example

please help if you can


Thanks
Tony

 

[Micron]

maybe close, but the message needs tweaking. Let me know (no point in fixing the message if I'm off base).

Sub splitIntoColumns()
Dim rng As Range
Dim Lrow As Long, i As Integer, Lcol As Long
Dim msg As String

Lrow = Cells(Rows.count, "K").End(xlUp).Row
msg = "Your data was spread across "

For i = 2 To Lrow
    With Sheets("001")
        Set rng = .Range("K" & i)
        rng.TextToColumns Destination:=rng, DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, _
        Space:=True
        Lcol = .Cells(i, .Columns.count).End(xlToLeft).Column
        msg = msg & Lcol - 10 & "." & vbCrLf
        msg = msg & "Last column is " & .Cells(i).Address
        MsgBox msg
    End With
Next

End Sub

 

[Micron]

Just noticed that new last column part of the message needs fixing as well. As mentioned, I'll wait to re-post code. One reason is that I suspect you'll change your mind about a message box prompt for each row if there's hundreds of rows being processed.

 

[Peter_SSs]

Perhaps this is what you want?

Sub SplitWords()
  Dim lc As Long
 
  With Range("K2", Range("K" & Rows.Count).End(xlUp))
    .TextToColumns Destination:=.Offset(, 1), DataType:=xlDelimited, Space:=True, Other:=False
    lc = .EntireRow.Find(What:="*", SearchOrder:=xlByColumns, Searchdirection:=xlPrevious).Column
    MsgBox "Columns used: " & lc - .Column & vbLf & "Last column: " & Split(Cells(1, lc).Address, "$")(1)
  End With
End Sub

My sample data in col K, TTC to the right of that and MsgBox as shown.

 

[tonywatsonhelp]

Hi everyone,
Firstly, Thank you to Micron, some very nice ideas there and I apreciate you help,
Peter_SSs, this work first time and was exactly what I needed thank you.
Tony

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.