Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test-To-Columns 256 | Excel Programming | |||
Test-To-Columns 256 | Excel Programming | |||
TEST for items in 2 columns to MATCH? | Excel Discussion (Misc queries) | |||
Trying to test several columns for changes when entering data | Excel Programming | |||
Sum one column after capmaring test from teo more columns | Excel Worksheet Functions |