Stanley Lai

asked on

# Would like to know how to copy a formatted number in Excel 2013 to a destination with truncated result?

Hi,

Would like to know how to copy a formatted number in Excel 2013 to a destination with truncated result?

My scenario is :

I have received an Excel worksheet daily. The worksheet contains many formatted number data. Yet, the format (especially the decimal points) of each number will be varying daily. But the underlying data is fixed in length. Due to the need to transfer the formatted data to PowerPoint slide and Word File, I need to write Excel VBA to do this. However, I can only transfer the actual number but not the formatted number. May I know how to transfer the formatted data to the PowerPoint slide and Word File by using Excel VBA?

Example.

Today : The actual underlying raw data of the Excel cell B1 is : 1234.45678

Today : The formatted data as appeared in cell B1 : 1,234.457

Today : Actual raw data that need to transfer to PowerPoint Slide and Word document for cell B1 : 1234.457

Tomorrow : The actual underlying raw data of the Excel cell B1 is : 23,456.78912

Tomorrow : The formatted data as appeared in cell B1 : 23,456.8

Tomorrow : Actual raw data that need to transfer to PowerPoint Slide and Word document for cell B1 : 23456.8

May I know how to do this in Excel VBA?

Thanks.

Stanley

Would like to know how to copy a formatted number in Excel 2013 to a destination with truncated result?

My scenario is :

I have received an Excel worksheet daily. The worksheet contains many formatted number data. Yet, the format (especially the decimal points) of each number will be varying daily. But the underlying data is fixed in length. Due to the need to transfer the formatted data to PowerPoint slide and Word File, I need to write Excel VBA to do this. However, I can only transfer the actual number but not the formatted number. May I know how to transfer the formatted data to the PowerPoint slide and Word File by using Excel VBA?

Example.

Today : The actual underlying raw data of the Excel cell B1 is : 1234.45678

Today : The formatted data as appeared in cell B1 : 1,234.457

Today : Actual raw data that need to transfer to PowerPoint Slide and Word document for cell B1 : 1234.457

Tomorrow : The actual underlying raw data of the Excel cell B1 is : 23,456.78912

Tomorrow : The formatted data as appeared in cell B1 : 23,456.8

Tomorrow : Actual raw data that need to transfer to PowerPoint Slide and Word document for cell B1 : 23456.8

May I know how to do this in Excel VBA?

Thanks.

Stanley

Tomorrow : The actual underlying raw data of the Excel cell B1 is : 23,456.78912I would expect it to be 23456.78912 as decimal values don't know about a thousand separator.

ASKER

Even not considering the thousand separator. The problem of transferring truncated decimal value based on the formatted result is still a problem.

ASKER

Or,put it in another way...

Under Excel, are there any functions that can check the number of digits of a formatted cell?

For example, in cell A1

Actual valure ---> 12345.6789

Formatted value ---> 12345.68

Are there any functions (or VBA script) that will return 2 decimal places rather than 4 decimal place for cell A1?

Under Excel, are there any functions that can check the number of digits of a formatted cell?

For example, in cell A1

Actual valure ---> 12345.6789

Formatted value ---> 12345.68

Are there any functions (or VBA script) that will return 2 decimal places rather than 4 decimal place for cell A1?

It is not very clear what your problem is, and your sample data seems not consistent:

But your sample data isn't. Or what does "length" mean?

And what does "today" and "tomorrow" mean, please?

The actual underlying raw data of the Excel cell B1 is : 23,456.78912If so, the data is text, not a number as numbers have no thousand separator.

the underlying data is fixed in length.

But your sample data isn't. Or what does "length" mean?

And what does "today" and "tomorrow" mean, please?

ASKER

Hi Gustav,

Sorry for describe the situation not that good.

In fact, I mentioned Today and Tomorrow image for the same worksheet and for the same cell B1 is to let you know that even for the same cell of the same sheet, the format will change daily. For the first day, the same cell may be rounded to 3 decimal places (use cell format) while for another day, the same cell will round to 2 decimal places (use cell formatting), even the underlying value contains 4 decimal places.

For the example I mentioned, you are right, I wrongly put the underlying value with comma. Actually, it does not contain any thousand separators.

The length I mentioned here is the decimal place length AFTER cell formatting. For the decimal places of underlying value,it is fixed to 4 decimal places while the formatted value have varying length of decimal places length.

Wish this could explain better.

Stanley

Sorry for describe the situation not that good.

In fact, I mentioned Today and Tomorrow image for the same worksheet and for the same cell B1 is to let you know that even for the same cell of the same sheet, the format will change daily. For the first day, the same cell may be rounded to 3 decimal places (use cell format) while for another day, the same cell will round to 2 decimal places (use cell formatting), even the underlying value contains 4 decimal places.

For the example I mentioned, you are right, I wrongly put the underlying value with comma. Actually, it does not contain any thousand separators.

The length I mentioned here is the decimal place length AFTER cell formatting. For the decimal places of underlying value,it is fixed to 4 decimal places while the formatted value have varying length of decimal places length.

Wish this could explain better.

Stanley

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks for your prompt action.

Let me have a test and get back to you soon. ^^

Let me have a test and get back to you soon. ^^

Open in new window

It uses early binding, so needs to have a reference to Microsoft Word set.