Forum Discussion

milo1234's avatar
milo1234
Brass Contributor
May 27, 2021
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 R...
Charting
excel
office 365
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    May 27, 2021

    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)))
HansVogelaar's avatar
HansVogelaar
MVP
May 27, 2021

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.

Resources