Pull out numbers only in cell
 

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

Pull out numbers only in cell
 

It would help if you indicated that the number
must be after "P/N " or must be at least a certain
length or whatever your exact requirements are;
otherwise, you might be redoing things again.

However I think this will solve your problem.
Extraction of a Group of Digits and Dashes, from postings by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm

If all of your data is like what you indicate you could use
B11:
=MID(A11,SEARCH("P/N ",A11)+4, SEARCH(" ",MID(A11&" ", SEARCH("P/N ",A11)+4,200))-1)


---
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 in message ...
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

Pull out numbers only in cell
 

Thanks David. Your formula worked. What does the +4 and 200 indicate?

"David McRitchie" wrote:

It would help if you indicated that the number
must be after "P/N " or must be at least a certain
length or whatever your exact requirements are;
otherwise, you might be redoing things again.

However I think this will solve your problem.
Extraction of a Group of Digits and Dashes, from postings by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm

If all of your data is like what you indicate you could use
B11:
=MID(A11,SEARCH("P/N ",A11)+4, SEARCH(" ",MID(A11&" ", SEARCH("P/N ",A11)+4,200))-1)


---
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 in message ...
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

Pull out numbers only in cell
 

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