# How do I find the next number in a cell?

I have a cell that contains a series as such:

``````3215314 01/01/1984 43 RASPBERRY PI CHEESE 10 COMPUTER PART 10 14 OZ 26.70 0
``````

I need to isolate each part in a cell but I cannot use text to columns because it would put RASPBERRY PI CHEESE in 3 different cells and there are millions of cells.

Assuming the text I need is in cell A4, then I can find the first character (R) using the following formula:

``````=FIND(" ",A4,FIND(" ",A4,FIND(" ",A4)+1)+2)
``````

I can then write to a cell using the MID function as such:

``````=MID(A4,FIND(" ",A4,FIND(" ",A4,FIND(" ",A4)+1)+2)+1,MY PROBLEM IS HERE)
``````

I do not know how to find the number of characters to write RASPBERRY PI CHEESE in one cell. Thanks in advance.

• can the rule: `from the third space to the next number` always apply? – Scott Craner Sep 9 '19 at 20:24
• Yes, this always applies. The last letter of the series of words (RASPBERRY PI CHEESE in this case) will always have a space and a number after it, I am just not sure how to search for it. Note that sometime it will be 2 words, sometimes, 3, sometimes 4 words (example: RASPBERRY BLUEBERRY PI CHEESE). – Sorin Ionescu Sep 9 '19 at 20:28

This removes everything before the 3rd space and then returns the left portion before the first number:

``````=TRIM(LEFT(MID(A4,FIND("}}}",SUBSTITUTE(A4," ","}}}",3)),999),MIN(FIND({1,2,3,4,5,6,7,8,9,0},MID(A4,FIND("}}}",SUBSTITUTE(A4," ","}}}",3)),999)&"1234567890"))-1))
``````

`MID(A4,FIND("}}}",SUBSTITUTE(A4," ","}}}",3)),999)` returns the string starting at the third space. The Substitute puts `}}}` in the 3rd space which the FIND then finds.

`MIN(FIND({1,2,3,4,5,6,7,8,9,0},MID(A4,FIND("}}}",SUBSTITUTE(A4," ","}}}",3)),999)&"1234567890"))` then finds the first number in that return by iterating through all the numbers and the min returns the lowest position.

The LEFT then returns the correct string.

• Wow... THANK YOU! What would I have to modify to find the words COMPUTER PART? – Sorin Ionescu Sep 9 '19 at 20:37
• That is a new question that should get its own post but see what you can do with the formula above to modify. – Scott Craner Sep 9 '19 at 20:38
• Managed to do it but I don't really understand how this works... Thank you though. – Sorin Ionescu Sep 9 '19 at 20:48