How to parse data from a cell

[santa12345]

Hello.

What formula would I use to parse the following.

JC-RO-06-L367-E201-J166-MK5C40-M14XT

The number could and can be different, but the section I'm looking for the characters following the MK.
So in this example I would want the formula to return 5C40.

Another example:

JC-RO-06-L367-E201-J166-MK5C4011-M14XT
So in this example I would want the formula to return 5C4011

Bottom line - the text after the MK and before the - .

Thank you.... hopefully this makes sense.

 

[mrshl9898]

=LEFT(MID(A1,FIND("-MK",A1)+1,LEN(A1)),FIND("-",MID(A1,FIND("-MK",A1)+1,LEN(A1)))-1)

 

[Rick Rothstein]

Two questions...

1) Is it possible for the letters "MK" to appear in the text before the MK that you want?

2) Is the MK text that you want always located immediately before the last dash?

 

[santa12345]

this is awesome !!! works fantastic.....
i have another question....should i post a new thread ?

its in regards to ....
vba code.... how to open another worksheet...and then copy a tab from that file to the file holding the macro

file abc.xls having a macro to open def.xls...copy sheet1 from def.xls and paste it into sheet2 of abc.xls

 

[mrshl9898]

Best to open a new thread, and also answer Rick. He'll have a much neater solution.

 

[santa12345]

Hi Rick.

To answer your Two questions...

1) Is it possible for the letters "MK" to appear in the text before the MK that you want? NO..the text is always after the MK.

2) Is the MK text that you want always located immediately before the last dash? YES - that is correct.

 

[Rick Rothstein]

2) Is the MK text that you want always located immediately before the last dash? YES - that is correct.

In that case, this formula should also work...

=MID(TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",100)),200),100)),3,99)

 

[santa12345]

yes...that works fantastic ! ... thank you !

 

[Peter_SSs]

Always a good idea to provide enough sample to show any variety that might occur with your data. For instance, both your examples start with exactly "JC-RO-06-L367-E201-J166-MK"
and end with exactly "-M14XT" but I'm guessing that is not always the case?

If you have the LET function then another possibility might be

=LET(s,REPLACE(A1,1,FIND("-MK",A1)+2,""),LEFT(s,FIND("-",s)-1))

 

[santa12345]

i think i have everything working based on the great suggestions above. THANK YOU!
i have another cell (last one) that i need to populate

JC-RO-06-L603-E350-J253-MKX879-C04-IN-C06-OUT-M1.8 I need to extract the RO.... it will always be the 4th character...and will contain only 2 characters thank you again.