SOLVED

Split text from cell with semicolon

%3CLINGO-SUB%20id%3D%22lingo-sub-2391108%22%20slang%3D%22en-US%22%3ESplit%20text%20from%20cell%20with%20semicolon%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2391108%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20number%20of%20cells%20that%20contain%20two%20words%20which%20are%20separated%20by%20semicolons%20such%20as%3C%2FP%3E%3CP%3E1234%3BRelease%206%3C%2FP%3E%3CP%3E5678%3BRelease%207%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESometimes%20the%20words%20may%20be%20the%20other%20way%20round%20such%20as%3C%2FP%3E%3CP%3E1234%3BRelease%206%3C%2FP%3E%3CP%3ERelease%207%3B5678%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20formula%20that%20splits%20out%20the%20two%20words%20however%20into%20the%20correct%20columns.%3C%2FP%3E%3CP%3ESo%20I%20think%20I%20need%20an%20IF%20statement%20to%20say%20if%20the%20word%20contains%20'Release'%20then%20split%20the%20word%20into%20X%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20can%20someone%20help!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2391108%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2391174%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20text%20from%20cell%20with%20semicolon%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2391174%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972444%22%20target%3D%22_blank%22%3E%40milo1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20with%20legacy%20formulas%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20308px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284271iD1AD856682F488F3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhen%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(FIND(%22Release%22%2CB2)%26gt%3BFIND(%22%3B%22%2CB2)%2C%0A%20%20%20%20LEFT(B2%2CFIND(%22%3B%22%2CB2)-1)%2C%0A%20%20%20%20RIGHT(B2%2CLEN(B2)-FIND(%22%3B%22%2CB2)))%0A%0Aand%0A%0A%3DIF(FIND(%22Release%22%2CB2)%3CFIND%3E%3C%2FFIND%3E%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Contributor

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

3 Replies
best response confirmed by milo1234 (Contributor)
Solution

@milo1234 

If with legacy formulas

image.png

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

@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.

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