extract space-separated words in a cell into an array

If I have a bunch of text in a cell, is it possible to extract each individual word into an array? If so, how would I do it?

There are never any commas or other symbols in the text - just words separated by a space. So, for example "This is my text" Would be converted into an array ("This", "is", "my", "text")

My final goal is to put this text into a column in a worksheet so that cell A1 = "This", A2 = "is", and so on.

Thanks

In this example the long string is in B1 of the same sheet as the output:

Sub luv2glyd()

Dim ws As Worksheet
Dim myText
Dim i As Long

Set ws = Worksheets("Sheet1")
myText = Split(ws.Range("B1").Value, " ") 'assuming the long string is in B1
For i = 0 To UBound(myText)
    ws.Range("A" & i + 1).Value = myText(i)
Next i

End Sub

Great! Thank you all who contributed. I was looking for a VBA solution for now, but the rest is good to know for future reference.

TextToColumn1.jpg

TextToColumn2.jpg

TextToColumn3.jpg

end result

TextToColumn4.jpg

Here is a formula solution
Enter formula in B1 and copy across and down

Formula:

=TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),255*COLUMNS($B:B),255))

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L
1	I have a bunch of text in a cell	I	have	a	bunch	of	text	in	a	cell
2	My final goal is to put this text into a column	My	final	goal	is	to	put	this	text	into	a	column

Sheet: Sheet3

By VBA, A2:A100 converted in a single line

Range("A2:A100").TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True

Thanks for the Reps

Originally Posted by kev_

By VBA, A2:A100 converted in a single line

Range("A2:A100").TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True

Thanks for the Reps

You're welcome

Looks like this puts the contents of the array into a row. Is it possible to use something similar to what you have and put it into a single column that is different than the original string's column? So for example, if original string is in cell F5, put the extracted elements into cells A1, A2, A3, A4 and so on?

Nice solution kev... Since every one of the threads I solved today had someone come along knowing better, I think I will stop for a while.

@Arkadi My code is not better - just different.
But I am a bad typist and fewer characters result in fewer typos
Found that solution by recording the macro and removing everything that was not required.

Id have to say your code is definitely better
Your solution is nice, dont apologize or explain yourself.... I just keep missing simpler solutions and got annoyed at myself. Great job with your code... Be proud!

Originally Posted by Marc L

Hi !

PHP Code: 

Sub Demo()
    SP = Split([F5].Value)
    Cells(1).Resize(UBound(SP) + 1).Value = Application.Transpose(SP)
End Sub 


Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

Nice trick! Thanks.

I would go with @Mark L solution - it is more elegant and flexible than TextToColumns

If your original data is a row of values, TextToColumns gets a bit clumsy
- a separate "play" area is required and
- VBA has to clean up after itself

eg with values in A1:Z1
(CurrentRegion needs blank cells around it - hence AB1 not AA1 for "play" area)

Sub TextToRows()

With Range("AB1")
    Range("A1:Z1").Copy: .PasteSpecial Paste:=xlPasteValues, Transpose:=True
    .Resize(.End(xlDown).Row).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True
    .CurrentRegion.Copy: Range("A1").PasteSpecial Paste:=xlPasteValues, Transpose:=True
    .CurrentRegion.Clear
End With

End Sub

Solution from @Marl L only needs a loop and a minor mod to achieve the same

For c = Range("A1").Column To Range("Z1").Column
    ap = Split(Cells(1, c).Value): Cells(1).Resize(UBound(ap) + 1).Offset(, c - 1).Value = Application.Transpose(ap)
Next c