# Word/Excel mail merge question **URL:** \ **Category:** Factual Questions\ **Created:** [May 4, 2004, 12:51pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454 "2004-05-04T12:51:40Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Gfactor](https://avatars.discourse-cdn.com/v4/letter/g/9de053/32.png) [@Gfactor](https://boards.straightdope.com/u/Gfactor)\ **Post date:** [May 4, 2004, 12:51pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/1 "2004-05-04T12:51:40Z")
Ok. I’m sure the answer is right in front of my face. But I can’t seem to find it. Here’s the deal: 1. I am doing a mail merge using Word 2002 and Excel 2002. 2. The database in excel has all of the text in CAPS. 3. I did not create the database. When I merge my word doc, all of the data appears in CAPS. 4. OF COURSE EVERYONE KNOWS HOW STUPID THIS LOOKS. 5.I can’t find a change case widget in Excel. 1. And I can’t seem to set the case on the fields in Word. \*\* So that is my question: How can I take an Excel database that is in ALLCAPS and express it in a merged Word document as Titlecase without having to edit all 500 individual Word documents?\*\* ---
**Author:** ![Frank\_2](https://avatars.discourse-cdn.com/v4/letter/f/8c91f0/32.png) [@Frank\_2](https://boards.straightdope.com/u/Frank_2)\ **Post date:** [May 4, 2004, 1:15pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/2 "2004-05-04T13:15:29Z")
There is one of two different functions you’ll need to use, depending on how you want the end data to look. LOWER makes all the characters into lower case, while PROPER capitalizes only the first letter of each word and uncaps the remaining letters. **Step 1:** Insert a column next to the caps data. **Step 2:** Use either the LOWER function or the PROPER function in the blank column and reference the data column. i.e. =PROPER(A1) **Step 3:** Copy the function and paste all the way down the blank column. **Step 4:** Copy the formula column that contains the un-capped data. **Step 5:** Use the Paste Special option and select values. This overwrites the formula with the data. **Step 6:** Delete column with capped data. Repeat for each column and you should be good to go. ---
**Author:** ![fezpp](https://avatars.discourse-cdn.com/v4/letter/f/da6949/32.png) [@fezpp](https://boards.straightdope.com/u/fezpp)\ **Post date:** [May 4, 2004, 1:16pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/3 "2004-05-04T13:16:37Z")
The LOWER function in Excel will convert to Lower case. The following function will convert the contents of the A1 cell to Titlecase in the way that (I think) you want: ```auto =CONCATENATE(UPPER(LEFT(Sheet1!A1,1)),LOWER(RIGHT(Sheet1!A1, LEN(Sheet1!A1)-1))) ``` This will probably result in a #VALUE! if the referenced cell has no data in it. It’s a starting point though! ---
**Author:** ![fezpp](https://avatars.discourse-cdn.com/v4/letter/f/da6949/32.png) [@fezpp](https://boards.straightdope.com/u/fezpp)\ **Post date:** [May 4, 2004, 1:18pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/4 "2004-05-04T13:18:03Z")
… or just use PROPER! I really need to preview more :smack: ---
**Author:** ![Gfactor](https://avatars.discourse-cdn.com/v4/letter/g/9de053/32.png) [@Gfactor](https://boards.straightdope.com/u/Gfactor)\ **Post date:** [May 4, 2004, 1:19pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/5 "2004-05-04T13:19:35Z")
> [@Frank #2](#): > > There is one of two different functions you’ll need to use, depending on how you want the end data to look. LOWER makes all the characters into lower case, while PROPER capitalizes only the first letter of each word and uncaps the remaining letters. > > **Step 1:** Insert a column next to the caps data. > **Step 2:** Use either the LOWER function or the PROPER function in the blank column and reference the data column. i.e. =PROPER(A1) > **Step 3:** Copy the function and paste all the way down the blank column. > **Step 4:** Copy the formula column that contains the un-capped data. > **Step 5:** Use the Paste Special option and select values. This overwrites the formula with the data. > **Step 6:** Delete column with capped data. > > Repeat for each column and you should be good to go. That’s exactly what I needed. Thanks. ---
**Author:** ![BrotherCadfael](https://avatars.discourse-cdn.com/v4/letter/b/977dab/32.png) [@BrotherCadfael](https://boards.straightdope.com/u/BrotherCadfael)\ **Post date:** [May 4, 2004, 1:27pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/6 "2004-05-04T13:27:43Z")
On a slightly different note, does anyone know of any way to use an Excel spreadsheet as the _merge document_? It is easy to use the spreadsheet as the data source, but I have a bunch of identical spreadsheets to produce, only with one block of text to be based on a data source… ---
**Author:** ![dwc1970](https://avatars.discourse-cdn.com/v4/letter/d/a183cd/32.png) [@dwc1970](https://boards.straightdope.com/u/dwc1970)\ **Post date:** [May 4, 2004, 2:38pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/7 "2004-05-04T14:38:08Z")
> [@BrotherCadfael](#): > > On a slightly different note, does anyone know of any way to use an Excel spreadsheet as the merge document? It is easy to use the spreadsheet as the data source, but I have a bunch of identical spreadsheets to produce, only with one block of text to be based on a data source… I’m not really clear on what you’re trying to do. Are you wanting to pull data from a “master” spreadsheet and use it in other spreadsheets? If this is the case, you can point to cell references in other Excel files (and from within different worksheets in those files). The format for doing this appears as follows: ```auto =[filename.xls]Sheet1!A1 ``` filename.xls, of course is the name of the master file, whose name is enclosed in square brackets, and Sheet1 is (by default) the name of the worksheet. If the worksheet is referenced by a different name then substitute this. The “!” separates the location referer from the cell referenced (A1, in this case). ---
**Author:** ![BrotherCadfael](https://avatars.discourse-cdn.com/v4/letter/b/977dab/32.png) [@BrotherCadfael](https://boards.straightdope.com/u/BrotherCadfael)\ **Post date:** [May 5, 2004, 1:06pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/8 "2004-05-05T13:06:58Z")
> [@dwc1970](#): > > I’m not really clear on what you’re trying to do. Are you wanting to pull data from a “master” spreadsheet and use it in other spreadsheets? If this is the case, you can point to cell references in other Excel files (and from within different worksheets in those files). What I am trying to do is the exact analog of the Word mail-merge. I have a spreadsheet (it’s a time sheet for Payroll), and I need to print out a hundred plus of them, differing only in the name and payroll numbers of the employee. I need to do this every two weeks, for obvious reasons, and I don’t want to automate the entire process, so that I can just kick it off, and have the completed sheets come out of the printer, one right after another. ---
**Author:** ![BrotherCadfael](https://avatars.discourse-cdn.com/v4/letter/b/977dab/32.png) [@BrotherCadfael](https://boards.straightdope.com/u/BrotherCadfael)\ **Post date:** [May 5, 2004, 1:21pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/9 "2004-05-05T13:21:12Z")
Arg. that should read: “I DO want to automate the entire process”. ---
**Author:** ![Early\_Out](https://avatars.discourse-cdn.com/v4/letter/e/6f9a4e/32.png) [@Early\_Out](https://boards.straightdope.com/u/Early_Out)\ **Post date:** [May 5, 2004, 1:38pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/10 "2004-05-05T13:38:14Z")
**BrotherCadfael** , since you’re not going to be using the results as spreadsheets (i.e., you’re just printing out the timesheets), how about just marking and copying the spreadsheet, and pasting it into Word? That will produce a table in Word. In fact, you can actually open a spreadsheet in Word (tell it to open files of all types, not just .doc’s - you may have to install the converter, so have your Office CD handy!), which will yield the same result. Then, you can just use Word’s merge routines to create your printed timesheets. ---
**Author:** ![BrotherCadfael](https://avatars.discourse-cdn.com/v4/letter/b/977dab/32.png) [@BrotherCadfael](https://boards.straightdope.com/u/BrotherCadfael)\ **Post date:** [May 5, 2004, 1:43pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/11 "2004-05-05T13:43:49Z")
I can do that, although it seems somewhat roundabout. ---
**Author:** ![Early\_Out](https://avatars.discourse-cdn.com/v4/letter/e/6f9a4e/32.png) [@Early\_Out](https://boards.straightdope.com/u/Early_Out)\ **Post date:** [May 5, 2004, 1:50pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/12 "2004-05-05T13:50:50Z")
> [@BrotherCadfael](#): > > I can do that, although it seems somewhat roundabout. I’m willing to be proven wrong, but I have the sneaking suspicion that a purely Excel-based solution, if anyone can produce one, is going to be a lot more elaborate! (I feel some godawful macro routine coming on - hand me the Excedrin. 😃 ) ---
**Author:** ![larsenmtl](https://avatars.discourse-cdn.com/v4/letter/l/d78d45/32.png) [@larsenmtl](https://boards.straightdope.com/u/larsenmtl)\ **Post date:** [May 5, 2004, 2:29pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/13 "2004-05-05T14:29:34Z")
Early Out is correct. BrotherCadfael what you are tying to do can be with a little VBA. The below code opens your “source” workbook and pulls data from sheet1. It enters that data into sheet1 on the open workbook and then prints this sheet. Specifically it would pull data from the A and B columns row by row of the source, place this into A1 and A2 of the target and print. Repeat till done. You can stick this all under the click event of a command button. ```auto Dim i As Integer Dim XL As New Excel.Application 'Excel objects Dim wrk As Excel.Workbook 'Excel workbook Set wrk = XL.Workbooks.Open("C: est.xls") 'open source workbook - replace path For i = 1 To 2 'replace 2 with highest row on source Sheet1.Range("A1").FormulaR1C1 = wrk.Sheets("Sheet1").Application.Range("A" & i).Text 'loop through source grabbing text from cells and place in target Sheet1.Range("A2").FormulaR1C1 = wrk.Sheets("Sheet1").Application.Range("B" & i).Text Sheet1.PrintOut 'Print Sheet 1 of target Next ``` Is it that godawful? The hardest part is now modifying to fit your workbooks and needs. ---
**Author:** ![BrotherCadfael](https://avatars.discourse-cdn.com/v4/letter/b/977dab/32.png) [@BrotherCadfael](https://boards.straightdope.com/u/BrotherCadfael)\ **Post date:** [May 5, 2004, 3:41pm UTC](https://boards.straightdope.com/t/word-excel-mail-merge-question/243454/14 "2004-05-05T15:41:11Z")
Thanks! I’ll check it out… I’m a fairly experienced VBA programmer, but mostly within the Word context, not Excel.