Maintaining formatting of data when merging with Word files

How do you keep the formatting for data in Excel when the 
data is being used in a Word letter merge?  The data is 
formatted as 2 decimal points but when merged in Word the 
data expands to 6 decimal places or more.

I've tried copy and paste special as value; round 
function; saving as a comma separated value (csv) file....

Savings as a CSV seemed to work except when you want to 
go back and edit the data file, it doesn't recognize the 
file type and the formatting is lost when saved as an 
Excel file again.
0
anonymous (74721)
8/27/2004 1:18:18 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
351 Views

Similar Articles

[PageSpeed] 38

I would add the format switch in Word's merge fields.

In article <10a701c48bd3$bc4fe750$a301280a@phx.gbl>,
 "Maria" <anonymous@discussions.microsoft.com> wrote:

> How do you keep the formatting for data in Excel when the 
> data is being used in a Word letter merge?  The data is 
> formatted as 2 decimal points but when merged in Word the 
> data expands to 6 decimal places or more.
> 
> I've tried copy and paste special as value; round 
> function; saving as a comma separated value (csv) file....
> 
> Savings as a CSV seemed to work except when you want to 
> go back and edit the data file, it doesn't recognize the 
> file type and the formatting is lost when saved as an 
> Excel file again.
0
jemcgimpsey (6723)
8/27/2004 4:59:46 AM
>From a previous post:


You could use some helper cells and create strings for each field--then use
those helper values:

=text(a1,"dd-mmm-yyyy")
=text(b1,"0.0")
=text(c1,"#,##0.00")

or you could do some magic at the MSWord side.

Debra Dalgleish posted this for a different question:

In the Mail Merge, after you select your Excel file as a data source, 
you should see a 'Confirm Data Source' dialog box.
   (If you don't see the dialog box, change the setting in Word --
    under Tools>Options, General -- add a check mark to
     'Confirm Conversion at Open')

 From that list, choose 'MS Excel Worksheets via DDE (*.xls)', and your 
formatting will be retained.

If you have to connect through a different source, you can format the 
fields in the Word document. For example, to specify a number of decimals:

1. In Word, in the Main Document, press Alt+F9 to view the field codes.
2. Find the field code for the number. It will look something like:
            { MERGEFIELD FieldName }
3. Add a switch, to format the number with two decimal places.
           For example:
            { MERGEFIELD FieldName  \# "#,##0.00" }
4. Press Alt+F9 to hide the field codes.
5. Save the Main Document

Maria wrote:
> 
> How do you keep the formatting for data in Excel when the
> data is being used in a Word letter merge?  The data is
> formatted as 2 decimal points but when merged in Word the
> data expands to 6 decimal places or more.
> 
> I've tried copy and paste special as value; round
> function; saving as a comma separated value (csv) file....
> 
> Savings as a CSV seemed to work except when you want to
> go back and edit the data file, it doesn't recognize the
> file type and the formatting is lost when saved as an
> Excel file again.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/27/2004 9:07:44 PM
Reply:

Similar Artilces:

How-to apply conditional format
I have a list of attendance at my schools' chess club. I'd like to highlight those that have been absent for the past 3 meetings, as they are now eligible to be replaced by kids on the waiting list. Any idea how I can write a conditional format to check ideally the row for check marks and highlight the Name cell if three consecutive cells are not checked or alternatively (2nd prize) if the total of attendances are 3 less than the maximum number of attendances. Also fine if I can get the bottom 15% of attendance and highlight their names. I had a conditional formula on the Name cell...

Need help making chart using data from two columns? Elementary....
Hi, This is prabobally very simple I guess, but I can't figure out how to get it to work.... I was trying to make a chart that would use data from two columns.. more less like that: One column "A" let's say has names of people who bought something JACK, JOHN, JEFF etc... SECOND column "B" has how much they each spent next to their name so whatever $1, $2, $15 etc... Now the names will repeat with some other $.... of who bought something. So this is what I would like the chart to do... Check column "A" find all the JACK's and SUM what he sp...

Sorting Data Inside of Cell
New to Excel, but have a problem that I need to resolve. I'm pullin data from a web site (single table) into a spreadsheet. Everything t this point is perfect. The problem is that I need to sort the dat within each cell. Example; the number in the cell might read 275510. need to sort the data into 3 groups, 27,55,10. Does anyone have an ideas. Again I'm new to Excel but I tend to catch on quick. Thank yo in advance -- Message posted from http://www.ExcelForum.com This will take care of the data as the example that you posted. If your data starts in A1, enter this in an adjacent c...

Error when inserting Word document, or opening pub doc containing Word doc
I have Office 2000, with all the current updates. Installed fairly freshly from Service Pack 3 CDs, then finished off with Office Update. Using Windows 98 SE, also current via Windows Update. 76 GB HDD, but using only 1 partition, and that of only 31.1 GB; of those 31 GB, 21 are used and 10 are free. I have a 19 KB Word document, containing just the word "xyzzy". When I create an empty Publisher document, and try to insert the Word document (Insert -> Object -> From File), I get a pop-up complaining "Publisher cannot launch or communicate with the OLE server program n...

File.xsd and duplicate ID value
Hello, Here's a sample of a file.xsd and file.xml I wrote : File.xsd: <?xml version="1.0" encoding="utf-8" ?> <xs:schema id="LogErrori" targetNamespace="http://tempuri.org/LogErrori.xsd" elementFormDefault="qualified" ......... ......... <xs:sequence> <xs:element name="ID" type="xs:ID" minOccurs="0" /> <xs:element name="Utente" type="xs:string" minOccurs="0" /> ......... ......... </xs:sequence> ....... ....... </xs:schema&g...

apply conditional formatting
How do I apply conditional formatting that evaluates two criteria. For instance I need the field to change colors if the the date = today and Completed = no jimh wrote: >How do I apply conditional formatting that evaluates two criteria. For >instance I need the field to change colors if the the date = today and >Completed = no Use the Expression Is: option. The expression might be something like: Not [Completed] And [datefield] = Date() Don't ask me why, but the [ ] are necessary arounf the field names, even if they are pure alphanumeric names. -- Marsh MVP [MS Ac...

date format #13
04/11/05 is 2005/11/04 but Excel thinks it's 2004/11/05. I have many this kind of date need to be correct. Anyone knows how to do it in the simplest way? Thank you very much. This is purely a guess, but I think I'd play around with regional settings for this. Start -> Control Panel -> Regional and Language options "gil.qi.zhang@gmail.com" wrote: > 04/11/05 is 2005/11/04 but Excel thinks it's 2004/11/05. I have many > this kind of date need to be correct. Anyone knows how to do it in the > simplest way? Thank you very much. > > ...

Reading ranges and copying data from Excel when it is not open
Is there a way in code to copy data out of an Excel file even if Excel is not up and running? If Excel were open, I'd copy, say, the first 30 rows and paste the info to PowerPoint. Then, since rows 1-5 are for column headings, I'd hide rows 6-30 and copy a new range which would look involve 30 rows, but since rows 6-30 were hidden would be 1-5 and then 31-55 as a contiguous block. I am trying to do this in VBA without opening Excel and instatiating objects, etc. Is it possible? What VBA commands would I use? You would need to treat the excel file as a database and use ADO to g...

How do I maintain formatting?
I'm using Word 2K and when I save as a .txt file all formatting is lost. I use a Text to HTML converter prior to uploading to a website. How do I go about preserving the formatting so that the web doc looks like the one I created in Word? Best regards, -- VHH III Txt is plain text and that does not support formatting. Html does support formatting, but its requirements are entirely different from those of a Word document and so there is little correlation between the two. You can view what the Word document will look like in html by using the Web view. -- <>&...

group-object formatting question
Hello there, one question on the formatting of the group-object output. Using dir | Get-Acl | ForEach-Object { $_.Access } | Group-Object IdentityReference I get a table with Count, Name and Group. Count Name Group ----- ---- ----- 9 predefines\Administr... {System.. 9 grpTP-FILES-IT-RW {System.Secur How can I access just the Name field of this output? Thanks very much, Uwe R2V0IHRoZSBwcm9wZXJ0eSB2YWx1ZSBmcm9tIGVhY2ggW01pY3Jvc29mdC5Qb3dlclNoZWxsLkNv bW1hbmRzLkdyb3VwSW5mb10NCg0KZGlyIHwgR2V0LU...

Scrolling problem in MS Excel / Word under Terminal Server.
When ever, we scroll down (or any directioin) the keyboard gets stuck, and the sheet keeps on scrolling down, Any idea how this can be modified. Thanks in advance. Limesh Parekh enjayworld@sify.com ...

Bug with Data Sets!!!
I have created a drawing and copied servers to it from the default network stencil. I then created a data set that I wanted to apply to all the server shapes on the page. So I can show the data I want to see. I had to delete all shape data from the shape, via define shape data, before I can apply the updated data set. If I do not, even with remove exsisting data sets selected, I will get random shape labels in the shape data window. This happened because on one of the labels I have a fixed list and forgot to add a selection. I went into the data set and added the info. I then app...

hidden formatting symbols
In Word I'm able to see paragraph marks and other hidden formatting symbols. Can this be done in powerpoint 2007? I've searched and haven't found it. Seeing the hidden symbols would help when editing a slide within a text box Thanks -- Dave Yep would be useful but you can't do it AFAIK. "Dave R" <DaveR@discussions.microsoft.com> wrote in message news:8755AD11-7B3B-47E1-A6D4-DA54B8AE87BB@microsoft.com... > In Word I'm able to see paragraph marks and other hidden formatting > symbols. > Can this be done in powerpoint 2007? I'...

Conditional Formatting #22
I have 2 cells: A1 (yes or no value) B1 (numeric value) I want to apply conditional formatting to cell B1 that says if A1="yes" then contents of B1 are bold. How can I do this? Jess, Select B1. Format - Conditional formatting - formula: =A1="yes" -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jess" <Jess@discussions.microsoft.com> wrote in message news:F28BDFA7-959E-4F39-BF7A-01872C9CA3EF@microsoft.com... >I have 2 cells: > > A1 (yes or no value) > B1 (numeric value) > >...

how to fetch blob data in bulk fetching mode
Since bulk fetching mode do things more efficiently, so I determined to use bulk fetching mode in my app. But the problem is, there're some long large binary data in my database. As a tutorial in MSDN says, you can bind these field to CLongBinary objects , but this approach works only when you fetch your data once at a row. I'll be really very appreciate if you tell me how to achieve this, or tell me where can I find a example. Thanks in advance. ...

Copy conditional formatting across multiple rows?
I am using Excel 2003 and have created conditional formatting on the first row in the worksheet with a formula as follows =R3C3="No". This works correctly and the formatting of the row works as expected. If I now copy the formatting to the row below, using the 'Fill formatting Only' option when pasting the formatting is still based on the cell referenced in the intial formatting. Is there anyway to reference column 3 more generically in the formatting formula so I do not have to modify the formula on every row? TIA Gil Gil, R3C3 is absolute referencing. You can e...

M06 Error
I was opening Money 2006 off line (I don't use on-line) and received an error that there was an internal error in opening the file. The application aborted. I have since tried to open My Money.mny file and I get the error message below. I have shut down and restarted, does not help. I know th "Money has detected that you did not close the file "My Money" before exiting. Money now needs to verify the information in your file. If your file is too large, this may take a few minutes. Do you want to do this now? If you click No, you will need to open a different ...

Specify format in source data
can you specify format information in the source data. for example coul you specify that a segment in a pie chart for sales of oranges shoul always be orange, apples green etc et -- Message posted from http://www.ExcelForum.com In this post I show a macro that colors a series based on the series name. It would not be a major effort to base the color instead on the X value of a point. Of course, if the fruits are always in the same order, just keep using a copy of the same chart, or turn it into a custom chart type: http://peltiertech.com/Excel/ChartsHowTo/CreateCustomTypes.html - J...

Maximum word length 04-14-10
Does Word file have a size limit? What is the maximum number of words a Word file can contain without getting sluggish or making problems? Thanks. Limitations: http://support.microsoft.com/kb/211489 Sluggishness: That will depend on the system you are running Word on. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Clarendon" <Clarendon@discussions.microsoft.com> wrote in message news:F9F4B48F-0705-4F88-9D37-34E459673D55@microsoft.com... > Does Word file have a size limit? What is th...

Formatting sheets
Hi, Is there a way to cascade formatting in an xls? I.e I have a xls with one sheet in it, for each new sheet added I woul like it to have the formatting etc of the first sheet. Is this possible? Thanks, -- Message posted from http://www.ExcelForum.com Hi You can set up a default for new sheets/workbooks but these are for all new sheets/workbooks and not one default per workbook. Save a sheet with the formatting you want (incl font, format, print layout, margins etc) and save it as a template (xlt) called sheet.xlt in your XLStart folder. You can do the same with a workbook and save ...

Trouble with named data
In the formula bar of an x-y chart, I entered: =SERIES(,'Sheet1'!xData,'Sheet1'!yData,1) I tried both with and without the single quotes around Sheet1 -- either way I get an error message indicating the formula contains an invalid external reference. How can I solve the problem? It should be indicated: 1) xData and yData both contain an INDIRECT function whose text argument is a relative reference, thus data depends on which is the active cell. (I select a cell and recalculate in order to plot different sets of data). 2) This method of charting DID work for ...

.xnk file: how to recover?
An old employee somehow left us an "archive" of important documents by sending us an email called filename.xnk. I assume this is an Exchange link file but do not know what to do with it or how to handle it. Is there an optimal method to try and unpack/recover its contents? Wanted to check before toying with it ;^) Todd XNK is an Exchange Link file. Basically this a link file which will point you to a folder that is on the Exchange server. Run the following command to open it, exchng32 /t abc.xnk (Before you do anything, it is always a good idea to make a copy of this file ...

mail merge formatting
I have a Publisher brochure merged with an Excel spreadsheet. Many of the fields go into a table. The cells in Excel are formatted as currency but don't show up as currency in the Publisher document. I can't find how to format the table or the table cells in Publisher as currency. Thank you for your help. okschlaps wrote: > I have a Publisher brochure merged with an Excel spreadsheet. Many of the > fields go into a table. The cells in Excel are formatted as currency but > don't show up as currency in the Publisher document. I can't find how to > format the ta...

opening files in xcel...
I have run into a problem with excel opening a new file if excel is already open. If I have excel workbook open, and try to open an *.xls attachment from email, it responds like is trying to open, but all I see is the original workbook. Thanks, Russell When we used Netscape Messenger, it sometimes got confused when I tried to open an attachment the second time. I think it was because it didn't want to overwrite the filename in the windows Temp folder. If I closed netscape and restarted it, it usually worked. But real workaround was to just make sure I emptied my windows Temp fold...

Maintaining Links
All, I have a db that is linked to a table of another db. They are kept in one folder. Whenver I move the folder to another comp or change the location I need to re-link. How do I maintain the link so that I don't have to relink every time I move the folder? Thanks in advance. What you are asking is "How can I be in 2 places at the same time?" Do I really need to answer that? On the other hand, you can have the front-end app automatically ask for the new location. Have a look at the following: http://www.mvps.org/access/tables/tbl0009.htm -- Arvin Meyer, MCP, MVP http://...