Formula to Parse text for menu items ordered?

moshea

New Member
Joined
Aug 6, 2020
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,836
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
Balance Quantity.xlsx
ABCDEFGHIJ
8BLT 8.50 - FRUIT CUP 1.00 KID PASTA 5.95 Kids Gr. Ham & Chz 5.95BLT8.50- FRUIT CUP1.00KID PASTA5.95Kids Gr. Ham & Chz5.95
Sheet4
Cell Formulas
RangeFormula
B8:AY8B8=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

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,836
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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. (y)

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 (y), but not Excel 2016 (n)
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
ABCDEFGHIJKLMNOPQRSTUVWXY
3PREMO 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.00PREMO CAN COCTAIL10.00K-BURGER6.95K-BURGER6.95- Side SALAD2.00KID PASTA5.95KID PASTA5.95Half Salad - SOUTH WESTERN5.00BLT8.50- UPCHARGE SD SALAD2.00CUCUMBER TOMATO SALA2.00SIDE ONION STRINGS2.00
4Green Man Draft 4.98 Infusion ****tail 7.01 PREMO CAN COCTAIL 10.00Green Man Draft4.98Infusion ****tail7.01PREMO CAN COCTAIL10.00
5FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 ICE CREAM 1.75 ICE CREAM 1.75 ICE CREAM 1.75FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73ICE CREAM1.75ICE CREAM1.75ICE CREAM1.75
6SANGRIA 8.00SANGRIA8.00
7BLT 8.50 - FRUIT CUP 1.00 KID PASTA 5.95 Kids Gr. Ham & Chz 5.95BLT8.50- FRUIT CUP1.00KID PASTA5.95Kids Gr. Ham & Chz5.95
8Half Salad - SOUTH WESTERN 5.00 Half Salad - HOUSE 4.50 COOPER RIVER 5.36 Infusion ****tail 7.01Half Salad - SOUTH WESTERN5.00Half Salad - HOUSE4.50COOPER RIVER5.36Infusion ****tail7.01
9SANGRIA 8.00 KID PASTA 5.95 K-BURGER 6.95 - ADD CHEESE 0.75 BLT 8.50 - UPCHARGE SD SALAD 2.00 SANGRIA 8.00SANGRIA8.00KID PASTA5.95K-BURGER6.95- ADD CHEESE0.75BLT8.50- UPCHARGE SD SALAD2.00SANGRIA8.00
10FLAVORED 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.00FLAVORED VODKA5.16FLAVORED VODKA5.16VODKA3.87Half Salad - SOUTH WESTERN5.00K-BURGER6.95- Side SALAD2.00K-BURGER6.95- Side SALAD2.00FRIED MOZZARELLA7.00KID PASTA5.95K-CHICKEN FINGER5.95CUCUMBER TOMATO SALA2.00
11HOT 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.26HOT SOUP - BOWL6.00GRILLED CHEESE7.00- UPCHARGE Hot Soup2.00KID PASTA5.95Half Salad - HOUSE4.50Half Salad - SOUTH WESTERN5.00Whole Salad - CHEF12.00EXTRA DIPPING SAUCE0.26
12ICE CREAM PREMIUM 2.50 ICE CREAM PREMIUM 2.50 ICE CREAM PREMIUM 2.50 Candy 0.96 ICE CREAM 1.75 Candy 0.96ICE CREAM PREMIUM2.50ICE CREAM PREMIUM2.50ICE CREAM PREMIUM2.50Candy0.96ICE CREAM1.75Candy0.96
Sheet7
Cell Formulas
RangeFormula
B3:CV12B3=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

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,836
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

.. 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. :biggrin:

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 ..
1596961753578.png


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

I, however, only have Office 2016.
 

moshea

New Member
Joined
Aug 6, 2020
Messages
6
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,935
Messages
5,545,099
Members
410,656
Latest member
Hydraulics
Top