Hi Clay,
The +4 is an adjustment to start after the length of "P/N "
The 200 is a number high enough to include all characters
that might be included as MID requires third operand to
denote length.

Since you indicated the formula to find the word after "P/N"

=MID(A11,SEARCH("P/N ",A11)+4, SEARCH(" ",MID(A11&" ", SEARCH("P/N ",A11)+4,200))-1)

worked then you can improve it a bit

=IF(LEN(A11)=0,"",IF(ISERR(formula),"",formula)

=IF(LEN(A11)=0,"",IF(ISERR(MID(A11,SEARCH("P/N ",A11)+4, SEARCH(" ",MID(A11&" ", SEARCH("P/N ",A11)+4,200))-1)), "",
MID(A11,SEARCH("P/N ",A11)+4, SEARCH(" ",MID(A11&" ", SEARCH("P/N ",A11)+4,200))-1))

BTW, the value is extracted is text, if you want to convert it to a number
then use VALUE(x) or since there is error checking simply add +0
to convert to a number in both places that the formula is used within.

Otherwise, you're probably back to a User Defined Function
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm

--
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Clay" wrote...
I have cells that contain text and numbers. How can I pull just the numbers
out into a new cell. I would use the Right or Left function, but as you can
see from below they are different lengths. Does this have to be done in
Access? If so, how?

Here is an example of my cells:
LOT 0 - 2nd WAVE P/N 98416 (Qiqihar)
LOT 0 - 2ND WAVE P/N 120144410 (Delong)
DEDS-MECH-02 P/N 100136862