Extracting words from a cell

[andreascostas]

I have a list of words in cell A1, the words are separated by commas. Sometimes the list is one word and other times it
has as many as fifty words in it. I need to separate each word in its own cell without the commas. So continuing on the same row
B1, C1, D1 etc. Is there a function I can drag across the rows and get each word by itself?

 

[Trebor76]

Hi andreascostas,

Not sure about a native Excel function to do what you're after but this macro will:

Option Explicit
Sub Macro1()

    Dim strData() As String
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    strData = Split(Range("A1"), ",")
    
    For i = LBound(strData) To UBound(strData)
        Range("A1").Offset(0, i + 1).Value = strData(i)
    Next i
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[andreascostas]

Option Explicit Sub Macro1() Dim strData() As String Dim i As Long Application.ScreenUpdating = False strData = Split(Range("A1"), ",") For i = LBound(strData) To UBound(strData) Range("A1").Offset(0, i + 1).Value = strData(i) Next i Application.ScreenUpdating = True End Sub

Awesome, Thank you. It works perfectly.

 

[Rick Rothstein]

You can also do this with a non-looping macro as well...

Sub Macro1()
  Dim Arr() As String
  Arr = Split(Range("A1"), ",")
  Range("B1").Resize(1, 1 + UBound(Arr)) = Arr
End Sub

 

[Trebor76]

Awesome, Thank you. It works perfectly.

Thanks for the feedback and you're welcome.

 

[sandy666]

another way

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MaxCount = List.Max(Table.AddColumn(Source, "SCount", each List.Count(Text.Split([raw],",")))[SCount]),
    Split = Table.SplitColumn(Source, "raw", Splitter.SplitTextByDelimiter(","), MaxCount)
in
    Split

eg.

raw		raw.1	raw.2	raw.3	raw.4
a,b,c		a	b	c
d,r		d	r
qwer,opt,hjk,lop		qwer	opt	hjk	lop

 

[Peter_SSs]

You can also quickly do it manually with Text to Columns (on the Data ribbon tab) by selecting the cell (or column) choosing Delimited -> Next -> Comma only -> Next -> Destination: B1 -> Finish

Or same thing as a one-liner macro

Sub TtC()
    Columns("A").TextToColumns Range("B1"), xlDelimited, , , False, False, True, False, False
End Sub

If you have other data in column A and you really do only want to do this for A1 as described in post 1 then Change Columns("A") to Range("A1") in the above code


Or to answer the question you actually asked ..

Is there a function I can drag across the rows and get each word by itself?

.. Yes, but the formula suggested would depend on your Excel version. So I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

 

[Jborg]

Data is in A1. Put this formula in B1

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100)),SEQUENCE(,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1,0)*100+1,100))

 

[Peter_SSs]

Data is in A1. Put this formula in B1

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100)),SEQUENCE(,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1,0)*100+1,100))

Hi John, hope you are well!!

Firstly, clarifying for readers that this formula would require excel 365.

But also note that the OP states up to 50 words in the cell so your substitution of 100 spaces will not be sufficient - see cells T1:U1 below.
To be sure, we need to insert at least as many spaces as the length of the original string as shown in row 2, or similar in row 3 but shortening the formula a little in both length & calculation time by using the LET function if available.

20 12 28.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen,twenty	one	two	three	four	five	six	seven	eight	nine	ten	eleven	twelve	thirteen	fourteen	fifteen	sixteen	seventeen	eighteen	ni	neteen
2	one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen,twenty	one	two	three	four	five	six	seven	eight	nine	ten	eleven	twelve	thirteen	fourteen	fifteen	sixteen	seventeen	eighteen	nineteen	twenty
3	one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen,twenty	one	two	three	four	five	six	seven	eight	nine	ten	eleven	twelve	thirteen	fourteen	fifteen	sixteen	seventeen	eighteen	nineteen	twenty
Split Words

Cell Formulas
Range	Formula
B1:U1	B1	=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100)),SEQUENCE(,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1,0)*100+1,100))
B2:U2	B2	=TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),SEQUENCE(,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1,0)*LEN(A2)+1,LEN(A2)))
B3:U3	B3	=LET(L,LEN(A3),TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",L)),SEQUENCE(,L-LEN(SUBSTITUTE(A3,",",""))+1,0)*L+1,L)))
Dynamic array formulas.

 

[Jborg]

Hello Pete,
Hope you are well too. It has been a while.

Sorry for not clarifying to detail. I missed out the 50 words requirement.
Thanks for the wonderful tip with your alternatives. They are great!!!