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

VBAMicrosoft PowerPointMicrosoft ExcelMicrosoft WordMicrosoft Office

Last Comment

Stanley Lai

8/22/2022 - Mon

GrahamSkan

3/27/2020

This works for me:

Sub Number()
    Dim sh As Worksheet
    Dim wdApp As Word.Application
    Dim wdDoc As Word.document
   
    Set wdApp = CreateObject("Word.application")
    wdApp.Visible = True
    Set wdDoc = wdApp.documents.Add
    Set sh = ActiveWorkbook.Sheets(1)
    wdDoc.Range.Text = sh.Cells(2, 2)
End Sub

Open in new window

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

Gustav Brock

3/28/2020

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

I would expect it to be 23456.78912 as decimal values don't know about a thousand separator.

Stanley Lai

3/29/2020

ASKER

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

Stanley Lai

3/30/2020

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?

Gustav Brock

3/30/2020

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

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

If 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?

Stanley Lai

3/30/2020

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

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

Unlimited question asking, solutions, articles and more.

ASKER CERTIFIED SOLUTION

Gustav Brock

3/30/2020

Become an EE member today7-DAY FREE TRIAL

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

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

Stanley Lai

3/30/2020

ASKER

Thanks for your prompt action.

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