Forum Discussion

milo1234's avatar
milo1234
Brass Contributor
May 27, 2021

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

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

Resources