Simplify LEFT MID SEARCH formula

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
1,614
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Can this formula be simplified to a shorter version?

I am using below formula to get Grand Total Amount (Upto 20 digit) from large Text string

I don't have any problem with it but just want shorter version if possible

Text Formula - Templete.xlsx
BCD
1NotesGrand Total
21x White Guava - 23.97% THC | Big Tree (Eighth 3.5g) - $30 \n\nSubtotal: $30\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $33333333333333333333\n \nPayment: \n\nCash Payment\n$3333333333333333333
3WEEDMAPS\n1x Tangie - 0.5g Cartridge - 83.94% THC | Papas Herb (Cartridge 0.5g) - $25 \n\nSubtotal: $25\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $288000000\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$288000000
4Order updated!\n1x Mowie Wowie - 19% THC | Dime Bag (Eighth 3.5g) - $30 \n\nSubtotal: $30\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $3333333333333\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$3333333333333
52x Headband - 20% THC | 4Republic (Eighth 4g) - $20 \n\nSubtotal: $40\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $412121212121213\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$412121212121213
6WEEDMAPS\n1x Blue Dream - 0.5g Cartridge - 79.68% THC | Papas Herb (Cartridge 0.5g) - $25 \n\nSubtotal: $25\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $28\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nFTC$28
7Order updated!\n1x FunFetti - 16% THC | 4Republic (Eighth 4g) - $20 \n2x Papaya Punch - 22% THC | Honeydew Farms (Eighth 3.5g) - $42 \n\nSubtotal: $104\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $10507.52\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nFTC$10507.52
82x Mowie Wowie - 19% THC | Dime Bag (Eighth 3.5g) - $30 \n1x Pineapple Cream - 21% THC | Dime Bag (Eighth 3.5g) - $30 \n\nSubtotal: $90\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $93\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$93
9Apple Pay RECEIVED\nOrder updated!\n1x Alien OG - 1g Sauce - 83.78% | APEX Concentrates (Concentrate 1g) - $42 \n1x Cuvee Cookies - 0.5g Cartridge - 81.70% THC | KINGPEN (Cartridge 0.5g) - $40 \n\nSubtotal: $82\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $85\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$85
10Apple Pay RECEIVED\nOrder updated!\n1x Cookies N Cream - 22% THC | Dime Bag (Eighth 3.5g) - $30 \n1x Watermelon Zkittlez - 23% THC | Dime Bag (Eighth 3.5g) - $30 \n\nSubtotal: $60\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $63\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$63
111x (Smalls) Slurricane - 28% THC | Papas Herb (Eighth 3.5g) - $27 \n\nSubtotal: $27\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $30\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$30
Weekly Sale 1.1.21 - 1.7.21
Cell Formulas
RangeFormula
D2:D11D2=LEFT(MID(B2,SEARCH("nGrand Total",B2)+14,20),IFERROR(SEARCH("\",MID(B2,SEARCH("nGrand Total",B2)+14,20)),MID(B2,SEARCH("nGrand Total",B2)+14,20))-1)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
A bit shorter
Excel Formula:
=LEFT(REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,""),SEARCH("\",REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,"")&"\")-1)
 
Upvote 0
Hi,

Another way,

Use this formula, result as Text (just like you have it now):

Excel Formula:
=TRIM(LEFT(SUBSTITUTE(MID(B2,FIND("\nGrand Total:",B2)+14,99),"\",REPT(" ",99)),99))

Use this formula, result as Real Number (better for future calculations), Format cells as Currency:

Excel Formula:
=LEFT(SUBSTITUTE(MID(B2,FIND("\nGrand Total:",B2)+14,99),"\",REPT(" ",99)),99)+0

@Fluff, when I tried to copy my table as OP had it with XL2BB, I got an error "6-Overflow", thus unable to post table...., do you know what the problem might be?
 
Upvote 0
Solution
A bit shorter
Excel Formula:
=LEFT(REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,""),SEARCH("\",REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,"")&"\")-1)
Thank you very much Fluff!
 
Upvote 0
Hi,

Another way,

Use this formula, result as Text (just like you have it now):

Excel Formula:
=TRIM(LEFT(SUBSTITUTE(MID(B2,FIND("\nGrand Total:",B2)+14,99),"\",REPT(" ",99)),99))

Use this formula, result as Real Number (better for future calculations), Format cells as Currency:

Excel Formula:
=LEFT(SUBSTITUTE(MID(B2,FIND("\nGrand Total:",B2)+14,99),"\",REPT(" ",99)),99)+0

@Fluff, when I tried to copy my table as OP had it with XL2BB, I got an error "6-Overflow", thus unable to post table...., do you know what the problem might be?
Thank you very much jtakw
both versions are great specially number version.
 
Upvote 0
when I tried to copy my table as OP had it with XL2BB, I got an error "6-Overflow", thus unable to post table...., do you know what the problem might be?
No Idea I'm afraid, what version of XL2BB are you using, as it works for me & the OP

+Fluff 1.xlsm
BCD
1NotesGrand Total
21x White Guava - 23.97% THC | Big Tree (Eighth 3.5g) - $30 \n\nSubtotal: $30\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $33333333333333333333\n \nPayment: \n\nCash Payment\n$33333333333333333333
3WEEDMAPS\n1x Tangie - 0.5g Cartridge - 83.94% THC | Papas Herb (Cartridge 0.5g) - $25 \n\nSubtotal: $25\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $288000000\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$288000000
4Order updated!\n1x Mowie Wowie - 19% THC | Dime Bag (Eighth 3.5g) - $30 \n\nSubtotal: $30\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $3333333333333\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$3333333333333
52x Headband - 20% THC | 4Republic (Eighth 4g) - $20 \n\nSubtotal: $40\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $412121212121213\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$412121212121213
6WEEDMAPS\n1x Blue Dream - 0.5g Cartridge - 79.68% THC | Papas Herb (Cartridge 0.5g) - $25 \n\nSubtotal: $25\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $28\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nFTC$28
7Order updated!\n1x FunFetti - 16% THC | 4Republic (Eighth 4g) - $20 \n2x Papaya Punch - 22% THC | Honeydew Farms (Eighth 3.5g) - $42 \n\nSubtotal: $104\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $10507.52\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nFTC$10507.52
82x Mowie Wowie - 19% THC | Dime Bag (Eighth 3.5g) - $30 \n1x Pineapple Cream - 21% THC | Dime Bag (Eighth 3.5g) - $30 \n\nSubtotal: $90\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $93\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$93
9Apple Pay RECEIVED\nOrder updated!\n1x Alien OG - 1g Sauce - 83.78% | APEX Concentrates (Concentrate 1g) - $42 \n1x Cuvee Cookies - 0.5g Cartridge - 81.70% THC | KINGPEN (Cartridge 0.5g) - $40 \n\nSubtotal: $82\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $85\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$85
10Apple Pay RECEIVED\nOrder updated!\n1x Cookies N Cream - 22% THC | Dime Bag (Eighth 3.5g) - $30 \n1x Watermelon Zkittlez - 23% THC | Dime Bag (Eighth 3.5g) - $30 \n\nSubtotal: $60\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $63\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$63
111x (Smalls) Slurricane - 28% THC | Papas Herb (Eighth 3.5g) - $27 \n\nSubtotal: $27\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $30\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$30
Sheet2
Cell Formulas
RangeFormula
D2:D11D2=LEFT(REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,""),SEARCH("\",REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,"")&"\")-1)
 
Upvote 0
Thank you very much jtakw
both versions are great specially number version.

You're welcome, glad to help.

@Fluff, after a bit of investigating/testing, I've found the culprit for the XL2BB "6-Overflow" error,
Since I have my E column formula to convert the result to Real number, but E2 number result is $33,333,333,333,333,300,000.00
which is more than 15 numerical digits in length (Excel dislikes this, and won't do math above 15 digits), and seems XL2BB doesn't like it neither.
So if I Skip that line, it'll post with XL2BB, should I bring this up to whoever maintains XL2BB for a possible fix?

Here I'm adding Table for my post #3:

Book3.xlsx
BCDE
1NotesResult as TextResult as #, Format as Currency
3WEEDMAPS\n1x Tangie - 0.5g Cartridge - 83.94% THC | Papas Herb (Cartridge 0.5g) - $25 \n\nSubtotal: $25\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $288000000\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$288000000$288,000,000.00
4Order updated!\n1x Mowie Wowie - 19% THC | Dime Bag (Eighth 3.5g) - $30 \n\nSubtotal: $30\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $3333333333333\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$3333333333333$3,333,333,333,333.00
52x Headband - 20% THC | 4Republic (Eighth 4g) - $20 \n\nSubtotal: $40\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $412121212121213\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$412121212121213$412,121,212,121,213.00
6WEEDMAPS\n1x Blue Dream - 0.5g Cartridge - 79.68% THC | Papas Herb (Cartridge 0.5g) - $25 \n\nSubtotal: $25\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $28\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nFTC$28$28.00
7Order updated!\n1x FunFetti - 16% THC | 4Republic (Eighth 4g) - $20 \n2x Papaya Punch - 22% THC | Honeydew Farms (Eighth 3.5g) - $42 \n\nSubtotal: $104\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $10507.52\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nFTC$10507.52$10,507.52
82x Mowie Wowie - 19% THC | Dime Bag (Eighth 3.5g) - $30 \n1x Pineapple Cream - 21% THC | Dime Bag (Eighth 3.5g) - $30 \n\nSubtotal: $90\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $93\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$93$93.00
9Apple Pay RECEIVED\nOrder updated!\n1x Alien OG - 1g Sauce - 83.78% | APEX Concentrates (Concentrate 1g) - $42 \n1x Cuvee Cookies - 0.5g Cartridge - 81.70% THC | KINGPEN (Cartridge 0.5g) - $40 \n\nSubtotal: $82\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $85\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$85$85.00
10Apple Pay RECEIVED\nOrder updated!\n1x Cookies N Cream - 22% THC | Dime Bag (Eighth 3.5g) - $30 \n1x Watermelon Zkittlez - 23% THC | Dime Bag (Eighth 3.5g) - $30 \n\nSubtotal: $60\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $63\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$63$63.00
111x (Smalls) Slurricane - 28% THC | Papas Herb (Eighth 3.5g) - $27 \n\nSubtotal: $27\nTaxes: $0\nDelivery (incl. tax): $3\nGrand Total: $30\n \nPayment: \n\nCash Payment\n\nOrder Notes: \n \nRET$30$30.00
Sheet736
Cell Formulas
RangeFormula
D3:D11D3=TRIM(LEFT(SUBSTITUTE(MID(B3,FIND("\nGrand Total:",B3)+14,99),"\",REPT(" ",99)),99))
E3:E11E3=LEFT(SUBSTITUTE(MID(B3,FIND("\nGrand Total:",B3)+14,99),"\",REPT(" ",99)),99)+0
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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