Formula to Parse text for menu items ordered?

[moshea]

Greetings all, I hope someone can help me. In Column A, I have Orders (lots). And I want to parse the text into individual items ordered in the columns next to each other. Any suggestions? Thanks in advance!

Example: From this

Green Man Draft 4.98 Infusion ****tail 7.01 PREMO CAN COCTAIL 10.00

to this

Green Man Draft

4.98​

Infusion ****tail

7.01​

PREMO CAN COCTAIL

10.00​

Here is more examples of my Raw Data Text to parse

Green Man Draft 4.98 Infusion ****tail 7.01 PREMO CAN COCTAIL 10.00

FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 ICE CREAM 1.75 ICE CREAM 1.75 ICE CREAM 1.75

ICED TEA 1.73 TULLAMORE DEW 7.37 K-BURGER 6.95 K-BURGER 6.95 KID MAC N CHEESE 5.95 KID MAC N CHEESE 5.95 KID PASTA 5.95 KID PASTA 5.95 Half Salad - SOUTH WESTERN 5.00 SIDE FRIES 2.00 HOT SOUP - BOWL 6.00 GYM WRAP 8.00 - BLACKEN SHRIMP 4.00

 

[Peter_SSs]

Still seems a bit of a glitch in cells H8:I8 of your sheet and the OP says up to 20 items per order so I think still some adjustment to do?

 

[CA_Punit]

Can you highlight what specifically is the Glitch.


I didnt consider 20 items. So if there is 20 items there might be words between spaces more than 50 so we can expand the range from ROW(1:50) to ROW(1:100).

 

[CA_Punit]

Balance Quantity.xlsx
	A	B	C	D	E	F	G	H	I	J
8	BLT 8.50 - FRUIT CUP 1.00 KID PASTA 5.95 Kids Gr. Ham & Chz 5.95	BLT	8.50	- FRUIT CUP	1.00	KID PASTA	5.95	Kids Gr. Ham & Chz	5.95
Sheet4

Cell Formulas
Range	Formula
B8:AY8	B8	=TRANSPOSE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",TRUE,IF(IFERROR(TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",LEN(A8))),(ROW(6:55)-ROW(6:6))*LEN(A8)+1,LEN(A8)))+0,0),"{","")&TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",LEN(A8))),(ROW(6:55)-ROW(6:6))*LEN(A8)+1,LEN(A8)))&IF(IFERROR(TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",LEN(A8))),(ROW(6:55)-ROW(6:6))*LEN(A8)+1,LEN(A8)))+0,0),"{","")),"{",REPT(" ",LEN(A8))),(ROW(6:55)-ROW(6:6))*LEN(A8)+1,LEN(A8))))
Dynamic array formulas.

 

[CA_Punit]

Oh I suppose you have taken the formula which was re-edited. As i have inserted "&" before and after number. But in the case of A8 there was already built in "&" in the text. so

I have re-edited it and please check for the re-edited post

 

[Peter_SSs]

Oh I suppose you have taken the formula which was re-edited.

Yes, I must have looked at it before your edit.
Looks much better now.

And after you doing all of the hard work, I've tried to improve/compact it and have come up with the form below.

@moshea
The formula solutions suggested by CA_Punit & myself should work in your Excel 365 , but not Excel 2016
In Excel 365 the formula should only need to be entered in the left hand column of the result area (column B in our examples) and copied down. The other results will automatically 'spill' across to the right.

moshea 2020-08-07 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
3	PREMO CAN COCTAIL 10.00 K-BURGER 6.95 K-BURGER 6.95 - Side SALAD 2.00 KID PASTA 5.95 KID PASTA 5.95 Half Salad - SOUTH WESTERN 5.00 BLT 8.50 - UPCHARGE SD SALAD 2.00 CUCUMBER TOMATO SALA 2.00 SIDE ONION STRINGS 2.00	PREMO CAN COCTAIL	10.00	K-BURGER	6.95	K-BURGER	6.95	- Side SALAD	2.00	KID PASTA	5.95	KID PASTA	5.95	Half Salad - SOUTH WESTERN	5.00	BLT	8.50	- UPCHARGE SD SALAD	2.00	CUCUMBER TOMATO SALA	2.00	SIDE ONION STRINGS	2.00
4	Green Man Draft 4.98 Infusion ****tail 7.01 PREMO CAN COCTAIL 10.00	Green Man Draft	4.98	Infusion ****tail	7.01	PREMO CAN COCTAIL	10.00
5	FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 ICE CREAM 1.75 ICE CREAM 1.75 ICE CREAM 1.75	FOUNTAIN DRINK	1.73	FOUNTAIN DRINK	1.73	ICE CREAM	1.75	ICE CREAM	1.75	ICE CREAM	1.75
6	SANGRIA 8.00	SANGRIA	8.00
7	BLT 8.50 - FRUIT CUP 1.00 KID PASTA 5.95 Kids Gr. Ham & Chz 5.95	BLT	8.50	- FRUIT CUP	1.00	KID PASTA	5.95	Kids Gr. Ham & Chz	5.95
8	Half Salad - SOUTH WESTERN 5.00 Half Salad - HOUSE 4.50 COOPER RIVER 5.36 Infusion ****tail 7.01	Half Salad - SOUTH WESTERN	5.00	Half Salad - HOUSE	4.50	COOPER RIVER	5.36	Infusion ****tail	7.01
9	SANGRIA 8.00 KID PASTA 5.95 K-BURGER 6.95 - ADD CHEESE 0.75 BLT 8.50 - UPCHARGE SD SALAD 2.00 SANGRIA 8.00	SANGRIA	8.00	KID PASTA	5.95	K-BURGER	6.95	- ADD CHEESE	0.75	BLT	8.50	- UPCHARGE SD SALAD	2.00	SANGRIA	8.00
10	FLAVORED VODKA 5.16 FLAVORED VODKA 5.16 VODKA 3.87 Half Salad - SOUTH WESTERN 5.00 K-BURGER 6.95 - Side SALAD 2.00 K-BURGER 6.95 - Side SALAD 2.00 FRIED MOZZARELLA 7.00 KID PASTA 5.95 K-CHICKEN FINGER 5.95 CUCUMBER TOMATO SALA 2.00	FLAVORED VODKA	5.16	FLAVORED VODKA	5.16	VODKA	3.87	Half Salad - SOUTH WESTERN	5.00	K-BURGER	6.95	- Side SALAD	2.00	K-BURGER	6.95	- Side SALAD	2.00	FRIED MOZZARELLA	7.00	KID PASTA	5.95	K-CHICKEN FINGER	5.95	CUCUMBER TOMATO SALA	2.00
11	HOT SOUP - BOWL 6.00 GRILLED CHEESE 7.00 - UPCHARGE Hot Soup 2.00 KID PASTA 5.95 Half Salad - HOUSE 4.50 Half Salad - SOUTH WESTERN 5.00 Whole Salad - CHEF 12.00 EXTRA DIPPING SAUCE 0.26	HOT SOUP - BOWL	6.00	GRILLED CHEESE	7.00	- UPCHARGE Hot Soup	2.00	KID PASTA	5.95	Half Salad - HOUSE	4.50	Half Salad - SOUTH WESTERN	5.00	Whole Salad - CHEF	12.00	EXTRA DIPPING SAUCE	0.26
12	ICE CREAM PREMIUM 2.50 ICE CREAM PREMIUM 2.50 ICE CREAM PREMIUM 2.50 Candy 0.96 ICE CREAM 1.75 Candy 0.96	ICE CREAM PREMIUM	2.50	ICE CREAM PREMIUM	2.50	ICE CREAM PREMIUM	2.50	Candy	0.96	ICE CREAM	1.75	Candy	0.96
Sheet7

Cell Formulas
Range	Formula
B3:CV12	B3	=TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",,IFERROR(TEXT(MID(SUBSTITUTE(A3," ",REPT(" ",LEN(A3))),SEQUENCE(,99,,LEN(A3)),LEN(A3))+0,"|0.00|"),TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",LEN(A3))),SEQUENCE(,99,,LEN(A3)),LEN(A3))))),"|",REPT(" ",LEN(A3))),SEQUENCE(,99,,LEN(A3)),LEN(A3)))
Dynamic array formulas.

 

[CA_Punit]

It all started because you guys started voluntarily helping newbie like us and we are just following your path and trying to learn as much as we could from you and @Eric W

You guys will always be Legends.

 

[Peter_SSs]

.. trying to learn as much as we could from you ..

It works both ways - I tried for a formula after Eric's effort and couldn't come up with anything much good but your idea got me going again.

Just reading back over the thread though I'm not sure our formula efforts would be any use to the OP even if they decided not to use the macro. I had been going on this ..

.. but now found this again so I'm confused about versions.

I, however, only have Office 2016.

 

[moshea]

Guys! WOW. You spent a lot of time and again Thank You so much. It was like a big puzzle and you all soled it. Thanks!!! I have just purchased O365. I really enjoy dissecting it and trying to understand it all. Pretty complex for a novice like me. Again, you all are the best!

 

[Peter_SSs]

Guys! WOW. You spent a lot of time

It was an interesting exercise.

Pretty complex for a novice like me.

Stick with it and soon you won't be a novice any more.