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

VBAMicrosoft PowerPointMicrosoft ExcelMicrosoft WordMicrosoft Office

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.

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

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?

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

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
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.