Formula to Parse text for menu items ordered?

moshea

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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
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?

OrdersItem1Item1CostItem2Item2CostItem3Item3CostItem4Item4CostItem5Item5Cost
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
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
SANGRIA 8.00
FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73
TULLAMORE DEW 7.37
BLT 8.50 - FRUIT CUP 1.00 KID PASTA 5.95 Kids Gr. Ham & Chz 5.95
Half Salad - SOUTH WESTERN 5.00 Half Salad - HOUSE 4.50 COOPER RIVER 5.36 Infusion ****tail 7.01
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
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
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
FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73 FOUNTAIN DRINK 1.73
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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
15ICE 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
Sheet2
 
Upvote 0
Peter, this is amazing!!! You saved me so much time. Thank you!!! And thanks again Eric W! You guys are awesome!!!
 
Upvote 0
You're welcome. Thanks for the follow-up. Hope you are able to keep taking plenty of orders!! :)
 
Upvote 0
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
5ICE CREAM PREMIUM2.50ICE CREAM PREMIUM2.50ICE CREAM PREMIUM2.50Candy0.96ICE CREAM1.75Candy0.96
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.
 
Upvote 0
CA_Punit said:
Can consider this also for ranges with modification in Row Ranges
Click to expand...
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
17ICE CREAM PREMIUM2.50ICE CREAM PREMIUM2.50ICE CREAM PREMIUM2.50Candy0.96ICE CREAM1.75Candy0.96
18PREMO CAN COCTAIL10.00K-BURGER6.95K-BURGER6.95- Side SALAD2.00KID PASTA5.95KID PASTA5.95Half Salad
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.
 
Upvote 0
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
15ICE 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
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:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
673
fjns
F
S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D

Forum statistics

Threads
1,223,710
Messages
6,174,019
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top