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