#1
|
|||
|
|||
Mail Merge Formatting Phone numbers
I have a large excel spreadsheet with several columns for phone numbers. In excel, the phone numbers are all typed as 1234567890 but are formatted as "special" and thus appear as (123) 456-7890.
When doing a WORD mail merge for address labels, I use the numeric picture switch (\#) to change the format to (123) 456-7890. This works perfectly for 2 out of 3 merge fields. It works for my { MERGEFIELD Home_Number \# "(000) 000'-'0000'} as well as { MERGEFIELD Cell_Number \# "(000) 000'-'0000'} It does not work for { MERGEFIELD Work_Number \# "(000) 000'-'0000'} It seems to pull random numbers that I can't find in my database at all. (the original database clearly has the correct phone number in the corresponding cell) When i take out the numeric picture switch, i'm left with the correct phone number but it displays 403319-3926 instead of 4033193926. (obviously i want it to display (403) 319-3926 ultimately) Where is it getting the hyphen from without the use of a numeric picture switch!? I'm so confused, i hope this is a weird glitch because I've tried EVERYTHING! this is my last resort, asking the computer gods! ALSO! i've just noticed that although the original excel cells are correct, while editing the recipient list, the numbers are displaying just as mail merge is incorrectly displaying them. as 403319-3926... but it doesn't say that in the original! How do I change it!? |
#2
|
||||
|
||||
Your description suggests some of the phone numbers have been input into the Excel workbook with the hyphens, whilst others have not. Consequently, you have a mix of numeric and non-numeric data. That, in turn causes the mailmerge to read the non-numeric data as formulae (i.e. the second part of the phone # gets subtracted from the first part). The solution is to remove all the hyphens in Excel.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
I've already used a formula then pasted just the values to remove all hyphens from the phone numbers, as previously stated, all the cells show the correct data in 1234567890 format.... it just doesn't merge correctly.
|
#4
|
||||
|
||||
The ones that are coming through with hyphens when you don't use the switch have the hyphens in the Excel data.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
Cross-posted at: http://www.techsupportforum.com/foru...ch-859769.html
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
can you change department field in phone numbers | pspec | Outlook | 2 | 04-05-2014 02:51 AM |
Extracting a phone number from a string that contains text and numbers. | hommi16 | Excel | 2 | 06-05-2013 09:19 PM |
Converting Phone Numbers Question | certified_techy | Excel | 8 | 06-08-2012 02:27 AM |
Create Custom Labels for Phone numbers | Tonistep | Outlook | 0 | 03-26-2012 02:57 PM |
Continuous page numbers with mail merge? | Jennifer Murphy | Mail Merge | 1 | 08-11-2011 05:27 AM |