Test-To-Columns > 256

[Otto Moehrbach]

Excel XP & Win XP
With VBA, I need to break down large cell entries (more than 256 words)
into individual words. Text-To-Columns works only for 256 words because of
the 256 column limit in Excel XP. Is there a work-around to place the words
into a column rather than a row? Thanks for your time. Otto
PS: My objective is to have VBA work with each individual word. Is there
another way for VBA to capture each individual word?

 

[Otto Moehrbach]

Patrick, Jim
Thanks for that. I think that will do what I want. Otto

 

[ryguy7272]

Just to add to Jim's idea:

Sub test()
Dim str As String
Dim ary() As String
Dim lng As Long

str = "This is a test"
ary = Split(str, " ")
Range("A1").Select

For lng = LBound(ary) To UBound(ary)
ActiveCell = ary(lng)
ActiveCell.Offset(1, 0).Select
Next lng
End Sub

You probably figured it out already...

HTH,
Ryan---

 

[Rick Rothstein]

How about a somewhat short macro that doesn't involve any looping?

Sub SplitTextIntoWordsAndListDownTheColumn()
Dim Txt As String
Txt = "This is a test"
Range("A1:A" & (1 + Len(Txt) - Len(Replace(Txt, " ", "")))) = _
WorksheetFunction.Transpose(Split(Txt, " "))
End Sub

 

[Rick Rothstein]

Sorry, I tend to think in "one liners", but I'm guessing the macro I posted
might be a tad hard to decipher. Here it is rewritten slightly and it should
be easier to follow...

Sub SplitTextIntoWordsAndListDownTheColumn()
Dim Txt As String
Dim Ary() As String
Txt = "This is a test"
Ary = Split(Txt)
Range("A1").Resize(1 + UBound(Ary)) = WorksheetFunction.Transpose(Ary)
End Sub