XL2BB - Excel Range to BBCode 2.1

[smozgur]

i have a mac using 365 if that makes a difference maybe

It shouldn’t, normally. But I will make sure about it.

 

[etaf]

thanks - you can see the screen shot and the XL2BB on my last post

 

[etaf]

subscribed, thanks again

 

[smozgur]

If I set the range by using the dd/mm/yyyy date format, then it works as expected.

Book1
	A
1	13/01/2021
2	14/01/2021
3	15/01/2021
4	16/01/2021
5	17/01/2021
6	18/01/2021
7	19/01/2021
8	20/01/2021
9	21/01/2021
10	22/01/2021
11	23/01/2021
12	24/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.

 

[smozgur]

Dates test for US / UK format - if still formats as US when UK

Tested and confirmed Excel behaves differently in Mac causing this issue. Investigating to find a cure in the next update.

Thanks, @etaf.

 

[etaf]

Thanks

 

[smozgur]

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

Windows - number formatting of A1 cell

VBA thinks correctly in Windows

Mac - number formatting of A1 cell

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
1	1/13/21
Sheet1

Custom Date Formatting - dd/m/yyyy
	A
1	13/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.

 

[Fluff]

Hi Suat
From this thread Simplify LEFT MID SEARCH formula

+Fluff 1.xlsm
	B	C	D
1	Notes		Result as Text
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		3.33333E+19
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		£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		£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		£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.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		£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.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.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.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.00
Results

Cell Formulas
Range	Formula
D2:D11	D2	=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?

 

[smozgur]

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.

 

[jtakw]

Hi,

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