separate numbers from text

[Victtor]

Most of my data comes like this in 1 cell (Test to columns does not work in this case):

2037020 Pint Milk 32 4

I need to extract that number 32

the Pint Milk could be any of my inventory items

How can I accomplish this. I do not need this hard coded for just Pint Milk, but for any of my items:

2037020 Pint Milk 32 4
2037021 Yup 5 0
2037022 Simply Juice 47 6
2037024 20oz Reg
2037025 Gold Tea 35 4
2037030 Minute Maid 66 8
2037031 HH Coke 12 1


I need the first number(s) after the inventory item. In the case of the Pint Milk it would be 32. Not the 4.

Please assume the data starts in C2 and I need the result in C3

Please help.

 

[Victtor]

Did Joe4's formula give you the answer you wanted for this item...

2037024 20oz Reg

If not, what should the answer have been? Also, for your additional question, what is the item name for the above item?

Looks Like I got what I needed:

if this is in C2:
2037020 Pint Milk 32 4

and this formula is in D2:
=TRIM(LEFT(SUBSTITUTE(MID(C2,9,LEN(C2))," ",REPT(" ",100)),LEN(SUBSTITUTE(MID(C2,9,LEN(C2))," ",REPT(" ",100)))-200))


and this formula is in E2:
=TRIM(LEFT(RIGHT(SUBSTITUTE(C2," ",REPT(" ",100)),110),100))

The result would be this:

D2= Pint Milk
E2= 32

Perfect

For the example item you gave, Rick, it does not work. Thankfully, I am not tracking that particular item. I imagine it would be a problem in the future, though

 

[Rick Rothstein]

For the example item you gave, Rick, it does not work. Thankfully, I am not tracking that particular item. I imagine it would be a problem in the future, though

Just to be clear, the example I gave came from the list you posted in Message #1 . Are you then saying that particular item should not have been in the list?

 

[Victtor]

Just to be clear, the example I gave came from the list you posted in Message #1 . Are you then saying that particular item should not have been in the list?

The 20oz drink is in the database list. There are about 200 inventory items in the list. For my current project, I am only tracking items that come from our grilled entrees. I can envision that I would be tracking drink sales at some point. This is especially true since I raised our drink prices by $0.10 and once a couple of weeks passed, I would like to see the impact of that.

for solving the equation, I simply grabbed 5 random items from the list, with the thinking that if it would work for this sample size, it would work for the entire database.

 

[Rick Rothstein]

The 20oz drink is in the database list. There are about 200 inventory items in the list. For my current project, I am only tracking items that come from our grilled entrees. I can envision that I would be tracking drink sales at some point. This is especially true since I raised our drink prices by $0.10 and once a couple of weeks passed, I would like to see the impact of that.

for solving the equation, I simply grabbed 5 random items from the list, with the thinking that if it would work for this sample size, it would work for the entire database.

So, assuming one day you will need to handle entries like the one I posted from you list, what would the two answers be for it?