Excel text formula for extracting words separated by semicolons in a field

Question

A field in Excel contains words separated by semicolons, for example:

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

How to apply Excel text formulas to create single words from this field in other fields? For instance:.

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 be good even when the value in A1 changes, for example. if A1 is changed to

A1 = hello; there; how; are; you

Any help in this regard would be greatly appreciated.

(In this case, the problem with writing my own function is not allowed, I have to use the original features, such as find, search, mid, etc.)

Answer 1

A1,

A2

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

B2

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

, . A , B , . , . B .

Answer 2

VBA, :

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

A2 : =ExtractElement(A1, 1, ";"), A3 : =ExtractElement(A1, 2, ";") ..

Answer 3

, :

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

B1 - blank

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

C1 D1: H1

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

C2 D2: H2

1 A1, , .

, E1 A1, D1 + 1 = 10.

iferror C1: H1 , , A1 1 .

B1 , .

C2: H2 Mid, A1, B1: G1, (C1-B1) -1, (d1-c1) -1 .. ( , )

: 5, 9,18,27,31,36 1, - .

, .