Question: How can one merge excel currency cell into Word letter mailing to print correctly?  All things that I have tried prints the amount as15897 instead of $15,897. I just wondered if you have a quick fix so I don’t have to go in manually and fix all of the amounts.

Answer: I have a fix, but it isn’t a real quick fix, but it is much faster than manually entering  hundreds of values. And once you have it set up in your Word options, you just have to remember to select the correct conversion for all formatting to convert properly. Follow the steps below for Word 2007/2010.

1. The most important step is to change your Word options (File > Options) Click Advanced and in the General settings place a check mark on “Confirm File Format conversion on Open” and click OK.
2. You must re-create your merge file (I prefer to use the Mail Merge Wizard pane under Mailings > Start Mail Merge).
3. Indicate your document type and click Next. of and browse for your Excel file to select.
4. You will be prompted with a screen “Confirm Data Source” with OLE DB database files. Do NOT select this. Instead, check Show All, which will display several data sources.
5. Scroll to select MS Excel Worksheets via DDE (*.xls) and the entire spreadsheet or a specific spreadsheet if the workbook contains more than one.
6. You’ll see your columns display (not necessarily in the same order as it was in Excel) and the currency format (as well as other cell formatting) should be retained.
7. Continue your mail merge.

For help with general mail merge steps, go to Microsoft free online training. http://office.microsoft.com/en-us/word-help/mail-merge-i-use-mail-merge-for-mass-mailings-RZ010277841.aspx?CTT=5&origin=HA010215566

Today I had a couple of questions that stumped me and relied on my best friends, Microsoft Office Support Community and Google to provide the answers. Thanks to these great resources!

Question 1: I was working on a Word document for my boss and can’t read the tiny comments.

Solution: Somehow the styles for the balloon got changed. To change it, go to your styles (click the diagonal arrow in the styles section) and click the Manage Styles icon in the bottom of the Styles window. Sort Alphabetical and find Balloon Text and click Modify to change to a larger font such as 8 or 10 and click OK. This one was on 1 pt, making it impossible to read. For complete instructions with screen prints, see this link.

Question 2: I need to count a series of patterns in an Excel column containing only 0s and 1s. I’m looking for patterns in a column with many rows where there is a 0 and then a 1 in the next row below it. It doesn’t matter if it is followed by several 1s. I just need to count all of the 0′s followed by at least one 1.

I submitted the question to Microsoft Online Community and got 3 answers within an hour.

Solution 1:

Assuming the data is in column A, this is the formula:

=SUMPRODUCT(($A$1:$A$998=0)*($A$2:$A$999=1))

Solution 2:

I like Jeeped’s solution (solution 1) - just remember that if you use it, the formula itself cannot be in one of the cells in the addresses in it.  In other words if you wanted the total at the end of the group you’ve shown in your question, and assuming that goes from row 2 to 17, your formula (in cell on row 18 in same column) would look something like:

=SUMPRODUCT(($A$2:$A$16=0)*($A$3:$A$17=1))

Just as an option, I’ll give you a User Defined Function (UDF) to do the same thing.  A UDF is a special type of macro that can be used in a worksheet formula just like built-in Excel functions such as SUMPRODUCT().

I called this one CountPairs.  And you would use it on a worksheet like this:

=CountPairs(A2:A17)

assuming your list of 1s and 0s is in column A from row 2 through 17.

Here is a web page that tells how to put the code into a regular code module for use:

http://www.contextures.com/xlvba01.html#Regular

Here is the code for the UDF:

Function CountPairs(listRange As Range) As Long
‘place formula such as
‘ = CountPairs(A2:A16) to get count
‘of zeros followed by a 1 in the group
Dim rowPtr As Long
Dim tempCount As Long

For rowPtr = listRange.Cells(1, 1).Row To _
(listRange(listRange.Cells.Rows.Count, 1).Row – 1)
If Cells(rowPtr, 1) = 0 _
And Cells(rowPtr + 1, 1) = 1 Then
tempCount = tempCount + 1
End If
Next
CountPairs = tempCount
End Function