Formatting

[Priya]

I need to prevent exponential formatting of data for large numbers

 

[TommyGun]

The maximum number is 15 significant digits. Here is the KB aricle for it:

Microsoft KB article

So, this is a limitation of XL that you cannot get around. Sorry.

 

[Seti]

You might be able to do math on the text values with a UDF. You would need to be able to parse the text character by character and then have code that would do the math just like you would on a piece of paper. I did this many years ago in APL and was able to handle numbers with hundreds of digits. I was able to do addition and multiplication fairly easily. Division and subtraction were much more difficult.

 

[TommyGun]

However, you would have to store the numbers in the cell as text. XL will not "retain" more than 15 significant digits.

Even if you did do these calculations through VBA, they would still be stored on your spreadsheet as text.