How can I extract words from a cell separately in excel-CodePudding

I am very new to using formulas in excel and I am having some issues. I have four comma separated words in a cell. When I type the words into the cell, I then want to extract each word separately into another cell without the comma.

See screenshot below.

The formula for extracting the first word is working, however the formula for the other 3 words is not and is returning a value error. If there is nothing in cell C7 I don’t want any errors in the other cells either. I will provide the code I have for extracting each of the words, any help appreciated.

Code for extracting first word:

=IF(LEN(C7)=0,"",IF(ISERROR(LEFT($C$7,FIND(",",$C$7)-1)),C7,LEFT($C$7,FIND(",",$C$7)-1)))

Code for extracting second word:

=IF(ISERROR(FIND(",",C7)),"",MID(C7,FIND(",",C7) 1,FIND(",",C7,FIND(",",C7) 1)-FIND(",",C7)-1))

Code for extracting third word:

=MID(C7,FIND(",",C7,FIND(",",C7) 1) 1,FIND(",",C7,FIND(",", C7,FIND(",",C7) 1)  2)-FIND(",", C7,FIND(",",C7) 1)-1)

Code for extracting fourth word:

=RIGHT(C7,LEN(C7)-FIND(",",C7,LEN(E7) LEN(F7) LEN(G7)))

One word in cell

No words in cell

When I type in four words it separates them as shown in 3rd screenshot using the formulas above in each cell, which is working ok. However I think I need to edit the formula in F, G and H7 so that if there is one word in C7, it is populated in E7 and F G and H7 are empty(don't show error value either), if there are two words in C7, the first word is populated in E7 and second word in F7 and so on. Apologies if I am not explaining correctly. Also to note, the length of the words in C7 will be variable.

CodePudding user response：

With TEXTSPLIT()(currently available to beta channel Office 365):

Put this in E7

=IFERROR(TRIM(TEXTSPLIT(C7,",")),C7&"")

And it will spill tp the right.

If you are using an older version on a PC:

Put this in E7 and copy over:

=IFERROR(INDEX(FILTERXML("<a><b>"&SUBSTITUTE($C7,",","</b><b>")&"</b></a>","//b"),COLUMN(A1)),"")

CodePudding user response：

Use this User-Defined Function (UDF):

Function Take(invoer As String, index As Integer) As String
temp = Split(invoer, ",")
If UBound(temp) >= index - 1 Then
  Take = temp(index - 1)
Else: Take = ""
End If
End Function

And use it as follows in the Excel sheet: