Streaminghunter598

In the Mail Merge Manager, click Select Recipients List and then choose Get List→Open Data Source. Navigate to the Excel (.xlsx) workbook you’re using as the data source and click Open. Select the worksheet or range that has the names and addresses for the data source, and then click OK. Your Word mail merge document is now linked to the. Leading zeros—for example, 00399—in codes are dropped during a mail merge if they're not formatted as text. Choose the column that contains the ZIP Codes, postal codes, or other data to be formatted. Go to Home, and in the Number group, select the Number Format box down arrow, and then choose an option in the list (such as Text).

A reader asked how to convert an Excel workbook into a PowerPoint presentation where the first column was a title page and the next two columns were the subtitles.

This sounded to me like a Mail Merge problem, and indeed Word proved a bridge to getting this done. This is a circuitous solution but avoids using any VBA code.

The above video is a walk-through of the below steps.

Download the exercise file here.

Select file to merge

In this lesson it will be the above exercise file.

Put a copy of this file on your Desktop or somewhere else accessible.

Then open a new Word document (no need to save this one) and set up a new Mail Merge by selecting Mailings from the home ribbon.

Under Select Recipients, there is an option to “Use an Existing List.” Select this option and navigate to your “exercise.xlsx” workbook. This workbook does contain headers, so you can leave that option checked on.

Insert fields to merge

This essentially loaded the workbook data into the Word doc. We will now tell Word where to insert these fields into our mail merge. We will do that by navigating to “Insert Merge Field,” also on the Mailings tab, and clicking on each of our three fields.

Apply Header styles to merge fields

Excel Text Converter For Mail Merge Mac Numbers

For our finished product, we want the first field, Name, to be the header, and the other two fields to be sub-headings. So we will use Word’s Heading Styles menu to set this up:

Finally… we are ready to merge.

Head to “Check for Errors” on the Mailings tab and select the last option, “Complete the merge without pausing. Report errors in a new document.”

You will get a Word document with each page reporting each row of the Excel workbook.

Save the Word document.

Note – if you are having trouble with the next step, try saving the document as an RTF file and then loading to PowerPoint.

To PowerPoint!

Ok, now we will go to PowerPoint. Under “New Slide” on the Home tab, one of the very last options says “Slides from Outline.” Well, hey! We made an outline in Word! So go ahead and select that file, wherever you saved it. Make sure that file is closed, or PowerPoint will return an error.

BOOM! We’ve got a mail-merged PowerPoint slideshow, and we didn’t need any coding.

I hope this helped!

Convert Labels into Mail Merge Data File

Mail Merge In Excel For Time

See also my Labels to Excel Worksheet add-in

Youtube Mail Merge Excel

The company addresses used in this example, were taken from an old sample mailing list and may not reflect the current addresses of the companies concerned.

Convert the table to text

If you have more than one page of mailing labels in a document, it becomes difficult to maintain. The labels should be converted to a mail merge data source which can be merged to create a new label document.

In the following examples I have toggled-on the formatting information (CTRL+SHIFT+8 - or click the ¶ button on the Home tab of the Word ribbon) to demonstrate more clearly what is happening.

The first step is to extract the addresses by converting the table to text. In Word versions to 2003, click in the table and from the Table menu select Convert > Table to Text.

In Word 2007/2016 select the table then click Convert to Text on the Data section of the Layout Tab of the ribbon. The button positions vary with version, but the command is at the right hand side of the ribbon.

Using paragraph marks as record separators (see above), the result should appear like the first of the two examples below.

If, like the right column example, it has the marks that indicate 'soft returns' against some of the address lines, then you must use the replace function to replace ^l (lower case L - which represents ) with ^p (which represents ¶), to produce a result similar to that of the left column.

With label documents spanning several pages, it will probably be necessary to extract each page separately.

If each label had the same number of address lines, you could simply convert back to a table now, but they rarely do, so we now need a few more steps to complete the process. First step is to insert a marker that will hold the ends of each address. Each address is terminated by (at least) a double paragraph mark, so we can run a wildcard search to replace that double paragraph mark with a random and unique string of characters to produce the result below. On reflection, '@' was not the most ideal choice as it is a reserved character in searches, but we can work round that one.

To search for a paragraph mark in a wildcard search, you must enter ^13 and not ^p in the 'Find what' string. The 'Replace with' string should, however, use ^p.

Excel Text Converter For Mail Merge Mac Labels

For more information on wildcard searching in Word see https://www.gmayor.com/replace_using_wildcards.htm

Next step is to swap those paragraph marks for tabs, to put each record on its own line. The search string looks for each paragraph mark and the character preceding it, but *not* characters preceded by @. The replacement string restores the preceding character and adds a tab in place of the paragraph mark.

The result is as follows:

Next step is to lose the marker. Use a simple search, without the wildcard option, to replace the marker with nothing:

If you wish to sort the list into alphabetical order, you can do so now, or later when you have converted the list to a table.

You can now select the list and again from the Word 2003 Table menu, convert the selected text back to a table.

In Word 2007/2016 the convert text to table command can be found on the Insert Tab of the ribbon after clicking Table:

Thereafter whichever Word version you use the dialogs are similar.

Add a title row at the top of the table. The names are not critical, just make them memorable and unique.

Because the original labels did not have a fixed number of lines, the different parts of the address do not line up vertically. This should not matter when you come to merge the addresses into the new label document. Simply include all the fields on the label.

Save the finished table and you have a data source that is easier to maintain and which Word can use to create a new label merge.

Better still copy the table to an Excel worksheet for even more versatility.

Process as above using a macro:

Below is a macro based which adopts a different approach and which includes a few corrections for matters that might pop up along the way, then saves the results as a sorted data source. The macro has been tested on a variety of (but not all possible) label layouts in both Word 2003 and Word 2007, so please test it on a COPY of your label document.

If you don't know what to do with the macro code see Installing a macro from a listing
For those uncomfortable with VBA programming - See also my Labels to Excel Worksheet add-in