pulling text from a cell

[rickf19]

Hi
I have a sheet which has in column E a text description , no restriction on length within that description, somewhere , and not in the same place within the text is a 7 digit number starting with 21, is there a way I can pull that seven digit number from the text into another cell ?
eg cell E2 contains 7 fruit lane 2187961 by james and murray
cell E3 contains murray and erica for 2187123 apple street
cell E4 contains 2196501 jack and paul the crescent

I want to have say cell H2 show 2187961
H3 show 2187123
H4 show 2196501

Any help greatly appreciated

Thanks
Rickf

 

[Fluff]

How about

=MID(E2,FIND("21",E2),7)

 

[Rick Rothstein]

Is it possible for your text to have the number 21 in it followed by 5 characters all followed by a space appearing before the number you are seeking? If so, then this formula will not for them, but will work for all other possibilites...

=MID(E2,SEARCH("21????? ",E2&" "),7)

 

[Scott Huish]

Is it possible for your text to have the number 21 in it followed by 5 characters all followed by a space? If so, then this formula will not for them, but will work for all other possibilites...

=MID(E2,SEARCH("21????? ",E2&" "),7)

That formula will work whether the original text has a space following it or not.

 

[Rick Rothstein]

That formula will work whether the original text has a space following it or not.

I am not sure what you are commenting on. Are you saying my "warning" is not needed? If so, yes, it is needed IF that text comes before the sought after number (I probably should have said that in my original post - I just modified my original post). For example, it will not work correctly on this text in the cell...

21 pods were picked 2187961 by james and murray

 

[Scott Huish]

Oh, I see. Sorry.

 

[Scott Huish]

Would this be the only 7 digit number possibility (a number starting with 21)?
If that's the case, then perhaps:

Book1
	E	F
2	21 pods were picked 2187961 by james and murray	2187961
3	7 fruit lane 2187961 by james and murray	2187961
4	murray and erica for 2187123 apple street	2187123
5	2196501 jack and paul the crescent	2196501
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=MAX(IFERROR(--MID(E2,ROW(INDIRECT("1:"&LEN(E2))),7),""))

 

[Rick Rothstein]

Like my formula, yours also depends on what is possible for the OP data. Your formula will fail if a longer number (this includes E-notation numbers) appears at the end of the text. So, as but two examples, these will fail...

2196501 jack and paul the crescent 21999999999

2196501 jack and paul the crescent 21E7

 

[rickf19]

Hi

Fluff answer worked great but as you guys pointed out if there is another series ie starting 20 or the sequence is not in the cell at all you get an NA for me this isnt such a big problem because there are only a relatively small no of deviations so changing the formula to 20 from 21 for a few cells is fine However if there is a date ie 2024 that comes before the sequence number it picks up that 20 plus 5 characters, again not a big problem for me but a limitation of the original formula
Also be aware that in my case the formula resulted in trailing spaces which meant I had to Text to columns to get rid of them ,Trim etc didnt work

Thanks to you all