Avatar of Stanley Lai
Stanley Lai
Flag for Hong Kong 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
VBAMicrosoft PowerPointMicrosoft ExcelMicrosoft WordMicrosoft Office

Avatar of undefined
Last Comment
Stanley Lai

8/22/2022 - Mon
GrahamSkan

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

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

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

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

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

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

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
Sign up - Free for 7 days
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
Stanley Lai

ASKER
Thanks for your prompt action.

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