If , Mid or right functions or a combination thereoff.

[Dave]

Hi All,

I am using Excel 2003 on Windows XP.

I have some data in cell A13 as follows:


Sometimes the text data is :


PRIOR-RECIPT--> 00000000000 SP-INT--> 123456789


& Sometimes the text data is :


NOT REQ FILE PRIOR-RECIPT--> 00000000000 SP-INT-->
123346789
(On the above line the 123346789 is all in a13.)


I must pull out the 9 digit SP-INT number either way into cell l36
I must pull out the 11 digit PRIOR-RECIPT number either way into cell
l41

I have tried If , Mid or right functions but to no avail.

Thanks a bunch ,
Dave

 

[zvkmpw]

This works for me:
=LEFT(RIGHT(A13,34),11)

It assumes the text is exactly as shown in the original post.
Otherwise, modify the formula as needed.

 

[Dave]

This works for me:
  =LEFT(RIGHT(A13,34),11)

It assumes the text is exactly as shown in the original post.
Otherwise, modify the formula as needed.

Thanks this gives me 11 digit PRIOR-RECIPT how would I get the 9 digit
SP-INT number ??

Thanks in advance,

Dave

 

[Rick Rothstein]

The SP-INT number can be gotten with this formula...

=RIGHT(A13,9)

The PRIOR-RECIPT can be gotten (assuming your text was posted with the
correct number of delimiting space) with this formula...

=LEFT(RIGHT(A13,34),11)

Rick Rothstein (MVP - Excel)




"Dave" wrote in message

Hi All,

I am using Excel 2003 on Windows XP.

I have some data in cell A13 as follows:


Sometimes the text data is :


PRIOR-RECIPT--> 00000000000 SP-INT--> 123456789


& Sometimes the text data is :


NOT REQ FILE PRIOR-RECIPT--> 00000000000 SP-INT-->
123346789
(On the above line the 123346789 is all in a13.)


I must pull out the 9 digit SP-INT number either way into cell l36
I must pull out the 11 digit PRIOR-RECIPT number either way into cell
l41

I have tried If , Mid or right functions but to no avail.

Thanks a bunch ,
Dave