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

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：

I have made you a solution, based on four UDFs (User-Defined Functions), this is their source code:

Function Take1(invoer As String) As String
temp = Split(invoer, ",")

If UBound(temp) >= 0 Then
  Take1 = temp(0)
  Else: Take1 = ""
End If
End Function

Function Take2(invoer As String) As String
temp = Split(invoer, ",")
If UBound(temp) >= 1 Then
  Take2 = temp(1)
  Else: Take2 = ""
End If
End Function

Function Take3(invoer As String) As String
temp = Split(invoer, ",")
If UBound(temp) >= 2 Then
  Take3 = temp(2)
  Else: Take3 = ""
End If
End Function

Function Take4(invoer As String) As String
temp = Split(invoer, ",")
If UBound(temp) >= 3 Then
  Take4 = temp(3)
  Else: Take4 = ""
End If
End Function

The Excel sheet looks as follows:

Obviously, this can be done with just one function, but I started on the wrong foot :-)