How to split alphanumeric string to two seperate columns

[Manasa]

Hi !!

I need a formula that will search for the letters"LK" in the alphanumeric string and extract the portion before LK and put it in one column and put the the stuff following LK into another column(including the letters 'LK'). VB is also welcome.

Example:

01234567LK9086S----->01234576(Column1) LK9086S(Column2)

PP34121344LK121321--->PP34121344(Column1) LK121321(Column2)

00023454LK12----> 00023454(Column1) LK12(Column2)

Please help me out.

Thanks,
M

 

[r1c1]

Assuming the value is in A1,

in first column write,

=LEFT(A1,FIND("LK",A1)-1)

in second column write,

=MID(A1,FIND("LK",A1),LEN(A1))

and drag down

 

[Manasa]

Thank you so much.It works