pulling text from a cell

rickf19

Board Regular
Joined
Aug 30, 2019
Messages
73
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
How about
Excel Formula:
=MID(E2,FIND("21",E2),7)
 
Upvote 0
Solution
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)
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Would this be the only 7 digit number possibility (a number starting with 21)?
If that's the case, then perhaps:

Book1
EF
221 pods were picked 2187961 by james and murray2187961
37 fruit lane 2187961 by james and murray2187961
4murray and erica for 2187123 apple street2187123
52196501 jack and paul the crescent2196501
Sheet1
Cell Formulas
RangeFormula
F2:F5F2=MAX(IFERROR(--MID(E2,ROW(INDIRECT("1:"&LEN(E2))),7),""))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top