 SOLVED

# Split text from cell with semicolon

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.

3 Replies
best response confirmed by milo1234 (Contributor)
Solution

# Re: Split text from cell with semicolon

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)))``````

# Re: Split text from cell with semicolon

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.

# Re: Split text from cell with semicolon

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))
))``````