Number formatting in arrays

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
In Excel, I have 3 numbers in cells A1 through to A3.

These are:

Rich (BB code):
1, 2 and 12345678901234567890

If column A was formatted as a number, it will show as:

Rich (BB code):
Rich (BB code):
Rich (BB code):
1, 2 and 12345678901234500000

The reason is Excel truncates long numbers.

When I look at the Locals Windows when running this code:

Rich (BB code):
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Dim MyArray() As Variant
    
    MyArray() = Cells(1, 1).CurrentRegion.Value

I see MyArray(3) shows:

Rich (BB code):
1.23456789012345E+19

Is there a way to force the Locals Window to display the actual figure (without the E)?

I don't mind if it displays the true figure of 12345678901234567890 or the truncated figure of 12345678901234500000.

The reason for all this is I have long numbers which are subsequently used in a lookup and therefore I cannot have the numbers truncated.

To overcome this, I add an apostrophe in front but if the number is displayed with an E, then adding an apostrophe at the beginning will turn it into text.

Thanks


[/FONT]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
I think you need to explicitly format the cell as numeric .... it looks like you have it formatted as General
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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