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:
I am doing a mail merge using Word 2002 and Excel 2002.
The database in excel has all of the text in CAPS.
I did not create the database. When I merge my word doc, all of the data appears in CAPS.
OF COURSE EVERYONE KNOWS HOW STUPID THIS LOOKS.
5.I can’t find a change case widget in Excel.
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?**
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.
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:
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:
=[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).
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.
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.
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. )
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.
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.
)