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.

enter image description here

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.