Extracting text from cells

[AusTexRich]

Can anyone suggest a formula to use to extract certain text from a cell? The
text may be found anywhere in a single cell, it may vary in length and not
always preceded by punctuation, however, it will always be preceded by a "M0"
or "P0" . Here's an example: CCN 02/04/04, PAID 05/28/03, M02-10708, BOX
AE-3912, DD 01/31/08. The task is to extract just the text, "M02-10708".

 

[FMolina]

Hi: Use the text function FIND in conjunction with LEFT, RIGHT or MID
functions to extract it. However, this might prove to be difficult since you
say that what you want to extract can vary in lenght. If I missunderstood
than you can use the MID function and use the FIND as the starting character
and the lenght to extract only what you want.

Regards,

 

[Frank Kabel]

Hi
try
=MID(A1,FIND("M0",A1),FIND(",",A1,FIND("M0",A1))-FIND("M0",A1)+1)

 

[Frank Kabel]

Hi
make this
=MID(A1,FIND("M0",A1),FIND(",",A1,FIND("M0",A1))-FIND("M0",A1))

 

[AusTexRich]

I appreciate your input. Thank you.

Hi: Use the text function FIND in conjunction with LEFT, RIGHT or MID
functions to extract it. However, this might prove to be difficult since you
say that what you want to extract can vary in lenght. If I missunderstood
than you can use the MID function and use the FIND as the starting character
and the lenght to extract only what you want.

Regards,

 

[AusTexRich]

What will it take to learn a little about nesting formulas? I will learn from
this. Good job Frank. It does exactly what I wanted it to do. Thank you so
very much.

 

[Frank Kabel]

Hi
a good starting point (besides this newsgroup) would be John
Walkenbach's Formulas book

--
Regards
Frank Kabel
Frankfurt, Germany

What will it take to learn a little about nesting formulas? I will learn from
this. Good job Frank. It does exactly what I wanted it to do. Thank you so
very much.