Excel text formula to extract words separated by semicolons in a field

Question

A field in Excel contains words separated by semicolons, e.g.:

A1 = save;the;national;treasure;for;good

How can I apply Excel text formulas to produce separate words from this field in another fields? E.g.:

A2 should contain a formula to get the first word ("save")
A3 should contain a (different) formula to get the second word ("the")
etc.

However these formulas should hold good even when the value in A1 changes, e.g. if the value of A1 is changed to

A1 = hello;there;how;are;you

Any help in this respect will be highly appreciated.

(The problem is writing a function of my own is not allowed in this case, I have to use original functions like find, search, mid, etc.)

Answer 1

You can create a VBA function to split the fields from this example:

Function ExtractElement(str, n, sepChar)
'   Returns the nth element from a string,
'   using a specified separator character
    Dim x As Variant
    x = Split(str, sepChar)
    If n > 0 And n - 1 <= UBound(x) Then
       ExtractElement = x(n - 1)
    Else
        ExtractElement = ""
    End If
End Function

Then the A2 formula would be: =ExtractElement(A1, 1, ";") and A3 would be: =ExtractElement(A1, 2, ";") and so on

Answer 2

If you can use intermediate formulae, then this will work:

A1 -- save;the;national;treasure;for;good

B1 -- blank

C1 -- =IFERROR(FIND(";",$A1,1+(B1)),LEN($A1)+1)

copy C1 into D1:H1

C2 -- =MID($A1,B1+1,(C1-B1)-1)

copy C2 into D2:H2

Row 1 will display the position of each semi-colon in A1, because it starts looking in the string one character past the semi-colon found in the previous cell.

eg cell E1 searches for a semi-colon in A1 starting at D1+1 =10.

The iferror statement in C1:H1 traps the error which will occur when the search finds no further semi-colons, and returns the full length of string A1, plus 1 for an imaginary semi-colon at the end.

B1 needs to be blank to create an initial zero.

Cells C2:H2 then use the Mid function to copy the section of the A1 string starting one character after the value in each of B1:G1, with length (C1-B1)-1, (d1-c1)-1 etc (minus one to cut out the semi-colon itself)

You should get: 5, 9,18,27,31,36 in Row 1, and beneath those cells the individual words.

Hope this helps.

Answer 3

If you have your text to parse in A1 then the following formulas should work

In A2 enter the formula

=IF(ISERROR(LEFT(A1,FIND(";",A1)-1)),A1,LEFT(A1,FIND(";",A1)-1))

In B2 enter the formula

=IF(ISERROR(RIGHT(A1,LEN(A1)-FIND(";",A1))),"",RIGHT(A1,LEN(A1)-FIND(";",A1)))

You can then copy those down as far as you need. Column A grabs the left most word, and Column B displays the remaining string to be parsed. If it runs out of words to parse the formula will display a blank. Column B can also be hidden.