Write the formula in cell D2. =CONCATENATE (A2,'.' ,B2,'@',C2,'.com') Press Enter on your keyboard. The function will create the email address. To create the Email ID for all users, copy the same formula by pressing the key Ctrl+C and paste into the range C3:C5 by pressing the key Ctrl+V. Note: - We can add any text, symbol and values in. To see the recipients you want to include or remove from your excel file for the directory merge, on the Start Mail Merge group, click Edit Recipient List. Click OK To insert merge fields, position the insertion point at the proper place in your document. In the Write & Insert Fields group, click Insert Merge Field, select the desired field.
MailMerges can save an awful lot of time when it comes to writing down and printing addresses, but there is often an issue in the way addresses were collected that make the way we have them written down quite tricky to work with.
Some of his dates, derived from an Excel spreadsheet, were appearing as numbers in his Word document: Solution: While we were not able to look directly at the file, I came to the conclusion that the problem did not lie within the Word mail merge document, but with the formatting in the Excel spreadsheet itself. What probably happened was that. Concatenate is simply a fancy way ot saying “to combine” or “to join together” and there is a special CONCATENATE function in Excel to do this. This function allows you to combine text from different cells into one cell. For example, we have a worksheet containing names and contact information.
If you have a list, or spreadsheet, of addresses, often you’ll have the name in one column, and the address in the other, with each line separated by commas. Or you’ll have the whole lot with commas.
If this happens, it makes it a little tricky to perform a standard mail merge with Microsoft Word (note it is possible to set rules for this, but the following is probably going to be easier!).
If you have addresses in the form of:
name, address 1, town, county, postcode
name 2, address 1, town, county, postcode, country
Excel Workbook Text Converter For Mail Merge
The following Excel formulas will get you from this list to an Excel spreadsheet you can mail merge from with Word.
Step One – Start a New Excel Spreadsheet
Open a brand new excel spreadsheet and copy / paste in your list of names and addresses into the first sheet.
Step Two – Start a New Sheet
At the bottom of the spreadsheet, it should read “sheet 1” you want to press the “+” symbol and add a sheet 2 – this is where we’ll be building the address spreadsheet.
Step Three – Put Headers into Sheet Two
Put the following in the top of each column: Name, Address 1, Address 2, Adress 3, Address 4, Address 5, Address 6
Note you may have more than 6 or 7 in the address, but if you do, there’s a chance your labels won’t fit anyway! Also, you can try to be clever and put “City” “State” “Postcode” etc, but only if your addresses are very regular, and all in the same country – mine usually are not!
Step Four – Import The Content Using Formulas
In the “name” part, we want everything up to the first comma (Mr and Mrs X, Some House, Some street…), so in the box under “Name” we enter the following formula:
=LEFT(Sheet1!A1,(FIND(',',Sheet1!A1,1)-1))
Excel Text Converter For Mail Merge
Then we drag down the little right hand bit to apply this to every cell which has a corresponding address over on sheet 1.
In the next column, we want everything between comma one and comma two. The formula for this is as follows:
=SUBSTITUTE(MID(SUBSTITUTE(','&Sheet1!B1&REPT(' ',6),',',REPT(',',255)),2*255,255),',',')
In the next row, we want to do the same, but with the third comma (and so on), so we change the 2* part to be 3*, as follows:
=SUBSTITUTE(MID(SUBSTITUTE(','&Sheet1!B1&REPT(' ',6),',',REPT(',',255)),3*255,255),',',')
We repeat this, substituting 3 fo r4, 4 for 5, and so on, until we have entries for each of our columns.
Step Five – Save Your Excel Workbook & Complete Your Mail Merge
Save your Excel workbook, then do the mailmerge in Word, adding each of the addresses into your rules (i.e. setup name as name, address 1, address 2 and etc). If you don’t know how to do this, try Googling!