# Formula to Parse text for menu items ordered?

#### moshea

##### New Member
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

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### moshea

##### New Member
Greetings all, I just tried searching around to see if I could find a similar issue. I found this thread Parsing data into columns and thought maybe I could do something similar.

Does anyone know how I could get each Item Ordered (Orders) parsed into a separate column with its Cost? My data has no more then 20 items per order. Any thoughts?

#### Eric W

##### MrExcel MVP
Welcome to the MrExcel forum!

I took this as a challenge, and it seems to work:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1OrdersItem1Item1 CostItem2Item2 CostItem3Item3 CostItem4Item4 CostItem5Item5 CostItem 6Item6 CostItem 7Item7 CostItem 8Item8 CostItem 9Item9 CostItem 10Item 10 CostItem 11Item11 CostItem 12Item12 Cost
2PREMO 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
3Green Man Draft 4.98 Infusion ****tail 7.01 PREMO CAN COCTAIL 10.00Green Man Draft4.98Infusion ****tail7.01PREMO CAN COCTAIL10.00
4FOUNTAIN 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
5SANGRIA 8.00SANGRIA8.00
6FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73
Sheet4
Cell Formulas
RangeFormula
C2:Z6C2=IFERROR(TRIM(LEFT(MID(\$A2,LEN(TRIM(TEXTJOIN(" ",0,\$B2:B2)))+1,9999),AGGREGATE(15,6,SEARCH(IF(MOD(COLUMNS(\$C:C),2),{0,1,2,3,4,5,6,7,8,9},{" "}),TRIM(MID(\$A2,LEN(TRIM(TEXTJOIN(" ",0,\$B2:B2)))+1,9999))&" "),1)-MOD(COLUMNS(\$C:C),2))),"")

However, it has some limitations. The empty B column is required. It also should run on Excel 365, but not 2016. Also, it assumes that the ONLY numbers in the text are the costs. If you have "8 ounce drink" or something like that, it won't work right. If you look at similar questions on this forum, you'll see that splitting text like this is a VERY inexact science. This seems to work for what I've seen, but it's likely that there are cases that won't work right.

In any case, give it a shot and let us know! Good luck!

#### moshea

##### New Member
Eric, Thanks so much for trying to tackle this. I, however, only have Office 2016.I really do appreciate you trying to crack this nut.

#### Peter_SSs

##### MrExcel MVP, Moderator

Tall order (no pun intended ) to do by formulas I believe. You could try this macro.

VBA Code:
``````Sub ParseMenuOrders()
Dim RX As Object
Dim a As Variant
Dim i As Long

Set RX = CreateObject("VBScript.RegExp")
RX.Global = True
RX.Pattern = "( \d+\.\d{2}( |\$))"
a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
For i = 1 To UBound(a)
a(i, 1) = Replace(RX.Replace(a(i, 1), "^\$1^"), "^-", "^")
Next i
Application.ScreenUpdating = False
With Range("C2").Resize(UBound(a))
.Value = a
.TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="^"
.CurrentRegion.NumberFormat = "0.00"
.CurrentRegion.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub``````

My sample data in column A & nothing in other columns before the code. (Not all results shown (too wide)

moshea 2020-08-07 1.xlsm
ABCDEFGHIJKLMN
1Orders
2PREMO 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.95Side SALAD2.00KID PASTA5.95KID PASTA5.95
3Green Man Draft 4.98 Infusion ****tail 7.01 PREMO CAN COCTAIL 10.00Green Man Draft4.98Infusion ****tail7.01PREMO CAN COCTAIL10.00
4FOUNTAIN 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
5SANGRIA 8.00SANGRIA8.00
6FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73
7TULLAMORE DEW 7.37TULLAMORE DEW7.37
8BLT 8.50 - FRUIT CUP 1.00 KID PASTA 5.95 Kids Gr. Ham & Chz 5.95BLT8.50FRUIT CUP1.00KID PASTA5.95Kids Gr. Ham & Chz5.95
9Half 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
10SANGRIA 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.95ADD CHEESE0.75BLT8.50UPCHARGE SD SALAD2.00
11SANGRIA 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.95ADD CHEESE0.75BLT8.50UPCHARGE SD SALAD2.00
12FLAVORED 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.95Side SALAD2.00
13HOT 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.00UPCHARGE Hot Soup2.00KID PASTA5.95Half Salad - HOUSE4.50Half Salad - SOUTH WESTERN5.00
14FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73
Sheet2

#### moshea

##### New Member
Peter, this is amazing!!! You saved me so much time. Thank you!!! And thanks again Eric W! You guys are awesome!!!

#### Peter_SSs

##### MrExcel MVP, Moderator

You're welcome. Thanks for the follow-up. Hope you are able to keep taking plenty of orders!!

#### CA_Punit

##### Well-known Member
Can consider this also for ranges with modification in Row Ranges

Balance Quantity.xlsx
ABCDEFGHIJKLMN
3ICE CREAM PREMIUM 2.50 ICE CREAM PREMIUM 2.50 ICE CREAM PREMIUM 2.50 Candy 0.96 ICE CREAM 1.75 Candy 0.96
4
6
7
8
Sheet4
Cell Formulas
RangeFormula
B5:U5B5=TRANSPOSE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",TRUE,IF(IFERROR(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",100)),(ROW(1:20)-ROW(1:1))*100+1,100))+0,0),"&","")&TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",100)),(ROW(1:20)-ROW(1:1))*100+1,100))&IF(IFERROR(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",100)),(ROW(1:20)-ROW(1:1))*100+1,100))+0,0),"&","")),"&",REPT(" ",100)),(ROW(1:20)-ROW(1:1))*100+1,100)))
Dynamic array formulas.

#### Peter_SSs

##### MrExcel MVP, Moderator
Can consider this also for ranges with modification in Row Ranges
Not quite sure what modifications you envisage for the row ranges or why you have the formula on a different row to the data it is analysing, but the formula does not work robustly for me.
Keeping your layout of formula 2 rows below data & copying down, I have highlighted some of the issues below.

moshea 2020-08-07 1.xlsm
ABCDEFGHIJKLMNOPQ
3Green Man Draft 4.98 Infusion ****tail 7.01 PREMO CAN COCTAIL 10.00
4FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 ICE CREAM 1.75 ICE CREAM 1.75 ICE CREAM 1.75
5SANGRIA 8.00Green Man Draft4.98Infusion ****tail7.01PREMO CAN COCTAIL10.00
6FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73ICE CREAM1.75ICE CREAM1.75ICE CREAM1.75
7TULLAMORE DEW 7.37SANGRIA8.00
8BLT 8.50 - FRUIT CUP 1.00 KID PASTA 5.95 Kids Gr. Ham & Chz 5.95FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73
9Half Salad - SOUTH WESTERN 5.00 Half Salad - HOUSE 4.50 COOPER RIVER 5.36 Infusion ****tail 7.01TULLAMORE DEW7.37
10SANGRIA 8.00 KID PASTA 5.95 K-BURGER 6.95 - ADD CHEESE 0.75 BLT 8.50 - UPCHARGE SD SALAD 2.00 SANGRIA 8.00BLT8.50- FRUIT CUP1.00KID PASTA5.95Kids Gr. HamChz5.95
11SANGRIA 8.00 KID PASTA 5.95 K-BURGER 6.95 - ADD CHEESE 0.75 BLT 8.50 - UPCHARGE SD SALAD 2.00 SANGRIA 8.00Half Salad - SOUTH WESTERN5.00Half Salad - HOUSE4.50COOPER RIVER5.36Infusion ****tail7.01
12FLAVORED 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.00SANGRIA8.00KID PASTA5.95K-BURGER6.95- ADD CHEESE0.75BLT8.50- UPCHARGE SD SALAD2.00SANGRIA8.00
13HOT 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.26SANGRIA8.00KID PASTA5.95K-BURGER6.95- ADD CHEESE0.75BLT8.50- UPCHARGE SD SALAD2.00SANGRIA8.00
14FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73FLAVORED VODKA5.16FLAVORED VODKA5.16VODKA3.87Half Salad - SOUTH WESTERN5.00K-BURGER6.95- Side SALAD2.00
15ICE CREAM PREMIUM 2.50 ICE CREAM PREMIUM 2.50 ICE CREAM PREMIUM 2.50 Candy 0.96 ICE CREAM 1.75 Candy 0.96HOT SOUP - BOWL6.00GRILLED CHEESE7.00- UPCHARGE Hot Soup2.00KID PASTA5.95Half Salad - HOUSE
16PREMO 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.00FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73
Sample
Cell Formulas
RangeFormula
C5:V18C5=TRANSPOSE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",TRUE,IF(IFERROR(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",100)),(ROW(1:20)-ROW(1:1))*100+1,100))+0,0),"&","")&TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",100)),(ROW(1:20)-ROW(1:1))*100+1,100))&IF(IFERROR(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",100)),(ROW(1:20)-ROW(1:1))*100+1,100))+0,0),"&","")),"&",REPT(" ",100)),(ROW(1:20)-ROW(1:1))*100+1,100)))
Dynamic array formulas.

#### CA_Punit

##### Well-known Member
Sorry I Missed Out Rept(" ",100) with MID function sometimes divides text into two parts

Balance Quantity.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
3Green Man Draft 4.98 Infusion ****tail 7.01 PREMO CAN COCTAIL 10.00Green Man Draft4.98Infusion ****tail7.01PREMO CAN COCTAIL10.00
4FOUNTAIN 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
5SANGRIA 8.00SANGRIA8.00
6FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73
7TULLAMORE DEW 7.37TULLAMORE DEW7.37
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
9Half 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
10SANGRIA 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
11SANGRIA 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
12FLAVORED 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
13HOT 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
14FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73FOUNTAIN DRINK1.73
16PREMO 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
Sheet4
Cell Formulas
RangeFormula
B3:AY16B3=TRANSPOSE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",TRUE,IF(IFERROR(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",LEN(A3))),(ROW(1:50)-ROW(1:1))*LEN(A3)+1,LEN(A3)))+0,0),"{","")&TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",LEN(A3))),(ROW(1:50)-ROW(1:1))*LEN(A3)+1,LEN(A3)))&IF(IFERROR(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",LEN(A3))),(ROW(1:50)-ROW(1:1))*LEN(A3)+1,LEN(A3)))+0,0),"{","")),"{",REPT(" ",LEN(A3))),(ROW(1:50)-ROW(1:1))*LEN(A3)+1,LEN(A3))))
Dynamic array formulas.

Also ROW(1:50) can be adjusted considering no of spaces in the cell

Last edited: