Test-To-Columns 256
 

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?

Test-To-Columns 256
 

Patrick, Jim
Thanks for that. I think that will do what I want. Otto
"Otto Moehrbach" wrote in message
...
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?

Test-To-Columns 256
 

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Otto Moehrbach" wrote:

Patrick, Jim
Thanks for that. I think that will do what I want. Otto
"Otto Moehrbach" wrote in message
...
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?

Test-To-Columns 256
 

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 (MVP - Excel)


"Otto Moehrbach" wrote in message
...
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?

Test-To-Columns 256
 

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Otto Moehrbach" wrote in message
...
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?