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

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

Charting
excel
office 365
power bi

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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))
))
  • 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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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)))

Resources