Test-To-Columns > 256

O

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

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

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top