Simplify LEFT MID SEARCH formula

[Sufiyan97]

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
	B	C	D
1	Notes		Grand Total
2	1x 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
3	WEEDMAPS\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
4	Order 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
5	2x 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
6	WEEDMAPS\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
7	Order 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
8	2x 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
9	Apple 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
10	Apple 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
11	1x (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
Range	Formula
D2:D11	D2	=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)

 

[Fluff]

A bit shorter

=LEFT(REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,""),SEARCH("\",REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,"")&"\")-1)

 

[jtakw]

Hi,

Another way,

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

=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:

=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?

 

[Sufiyan97]

A bit shorter

=LEFT(REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,""),SEARCH("\",REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,"")&"\")-1)

Thank you very much Fluff!

 

[Sufiyan97]

Hi,

Another way,

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

=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:

=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.

 

[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?

No Idea I'm afraid, what version of XL2BB are you using, as it works for me & the OP

+Fluff 1.xlsm
	B	C	D
1	Notes		Grand Total
2	1x 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
3	WEEDMAPS\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
4	Order 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
5	2x 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
6	WEEDMAPS\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
7	Order 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
8	2x 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
9	Apple 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
10	Apple 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
11	1x (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
Range	Formula
D2:D11	D2	=LEFT(REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,""),SEARCH("\",REPLACE(B2,1,SEARCH("nGrand Total: ",B2)+13,"")&"\")-1)

 

[Fluff]

Thank you very much Fluff!

Glad to help & thanks for the feedback.

 

[jtakw]

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
	B	C	D	E
1	Notes		Result as Text	Result as #, Format as Currency
3	WEEDMAPS\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
4	Order 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
5	2x 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
6	WEEDMAPS\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
7	Order 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
8	2x 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
9	Apple 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
10	Apple 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
11	1x (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
Range	Formula
D3:D11	D3	=TRIM(LEFT(SUBSTITUTE(MID(B3,FIND("\nGrand Total:",B3)+14,99),"\",REPT(" ",99)),99))
E3:E11	E3	=LEFT(SUBSTITUTE(MID(B3,FIND("\nGrand Total:",B3)+14,99),"\",REPT(" ",99)),99)+0

 

[Fluff]

should I bring this up to whoever maintains XL2BB for a possible fix?

Yes please, if you raise the issue here XL2BB - Excel Range to BBCode
and include a link back to this thread. Cheers