Split text from cell with semicolon

[milo1234]

I have a number of cells that contain two words which are separated by semicolons such as

1234;Release 6

5678;Release 7

Sometimes the words may be the other way round such as

1234;Release 6

Release 7;5678

I want to create a formula that splits out the two words however into the correct columns.

So I think I need an IF statement to say if the word contains 'Release' then split the word into X column. 

Please can someone help!!!!

[#2 · Accepted solution]

@milo1234 

If with legacy formulas

when

=IF(FIND("Release",B2)>FIND(";",B2),
    LEFT(B2,FIND(";",B2)-1),
    RIGHT(B2,LEN(B2)-FIND(";",B2)))

and

=IF(FIND("Release",B2)<FIND(";",B2),
    LEFT(B2,FIND(";",B2)-1),
    RIGHT(B2,LEN(B2)-FIND(";",B2)))

[#3]

@milo1234 

Let's say the data are in A2 and down.

In another column, enter the following formula in row 2:

=IF(FIND(";",A2)<FIND("Release",A2),LEFT(A2,FIND(";",A2)-1),MID(A2,FIND(";",A2)+1,100))

And in the next column:

=IF(FIND(";",A2)>FIND("Release",A2),LEFT(A2,FIND(";",A2)-1),MID(A2,FIND(";",A2)+1,100))

Fill or copy down.

[#4]

@milo1234 

If with 365 something like

=LET(str,B2:B5,
  sepPos,FIND(";",str),
  relPos,FIND("Release",str),
  IF({1,0},
    IF(relPos>sepPos,
      LEFT(str,sepPos-1),
      RIGHT(str,LEN(str)-sepPos)),
    IF(relPos<sepPos,
      LEFT(str,sepPos-1),
      RIGHT(str,LEN(str)-sepPos))
))