smozgur

XL2BB - Excel Range to BBCode 2.1

No permission to download

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
thanks - you can see the screen shot and the XL2BB on my last post
 
subscribed, thanks again
 
If I set the range by using the dd/mm/yyyy date format, then it works as expected.

Book1
A
113/01/2021
214/01/2021
315/01/2021
416/01/2021
517/01/2021
618/01/2021
719/01/2021
820/01/2021
921/01/2021
1022/01/2021
1123/01/2021
1224/01/2021
Sheet1

I will also test by changing the system regional settings on Mac. Maybe it is the issue since it doesn't require exclusive number formatting.
I'll let you know. Thank you for reporting this.
 
@etaf - this is another Excel annoyance on different platforms.

You will find two samples below: Excel 365, different OS with the same regional settings - UK.
* I completely switched the regions on both computers to see the "default date format" issue clearly that is causing this problem.

Screen Shot 2021-01-19 at 7.18.23 PM.png

Windows - number formatting of A1 cell

Screen Shot 2021-01-19 at 7.18.35 PM.png

VBA thinks correctly in Windows


Screenshot 2021-01-19 at 19.14.46.png

Mac - number formatting of A1 cell

Screenshot 2021-01-19 at 19.15.08.png

VBA looks to be confused on Mac and doesn't return the correct value for the Text property - which is supposed to be WYSIWYG, if the default Date number format is used

And the following two mini-sheets have been generated for the same cell by using the default and custom number formatting on Mac respectively.
Default Date Formatting - dd/mm/yyyy
A
11/13/21
Sheet1

Custom Date Formatting - dd/m/yyyy
A
113/1/2021
Sheet1

You can see how the different (and wrong) Text property behavior on Mac causes this problem. This might be kind of a workaround, setting the date format of the cells other than the default format, even I know it doesn't sound like the best way to do it.

Thanks again for reporting this issue. However, XL2BB depends on the Text property to make its data portability magic work between the post and the worksheet, and I can't see any programmatical healing for this interesting behavior of VBA in Excel.
 
Hi Suat
From this thread Simplify LEFT MID SEARCH formula

+Fluff 1.xlsm
BCD
1NotesResult as Text
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\n3.33333E+19
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£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£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£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.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£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.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.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.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.00
Results
Cell Formulas
RangeFormula
D2:D11D2=LEFT(SUBSTITUTE(MID(B2,FIND("\nGrand Total:",B2)+16,99),"\",REPT(" ",99)),99)+0


If D2 is formatted as Currency or Accounting , XL2BB gives a 6-Overflow error presumably due to the size of the number.
Is there anything that can be done about that?
 
If D2 is formatted as Currency or Accounting , XL2BB gives a 6-Overflow error presumably due to the size of the number.
Thanks, @Fluff.

I should use the .Value2 property instead of .Value. To be fixed in the next update.

1612564908153.png
 
Hi,

Was going to post this issue with XL2BB, Fluff beat me to it in Post #98.
 

Forum statistics

Threads
1,223,250
Messages
6,171,036
Members
452,374
Latest member
keccles

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