Microsoft Office Forums Phone Number Replace ( ) and - with periods    (I'm ALMOST there)

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-17-2019, 01:14 PM
trikky trikky is offline Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Windows 10 Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Office 2019
Novice
Phone Number Replace ( ) and - with periods    (I'm ALMOST there)
 
Join Date: Jul 2019
Location: Vancouver, Canada (expat Aussie)
Posts: 3
trikky is on a distinguished road
Default Phone Number Replace ( ) and - with periods (I'm ALMOST there)

I have a mail merge document where the source phone number is (123) 456-7890. I want the phone number on the completed document to appear as 123.456.7890 .



I have used some info I found elsewhere from Macropod with a potential fix, but it doesn't seem to work in the application I'm using for the merge, so I'm thinking I'm missing something. I've attached some files to help understand what i'm getting here.

On the document I'm designing, when I add the field, there is not a visible reference to the fieldname (mailmerge_entered.jpg). I'm thinking this may be the cause of the error in the application I'm using.

When I expand the mailmerge field (Shift+F9), I can see the QUOTE tag and nothing much else). (mailmerge_expanded.jpg), so it's there.

When I Update the field, the number formatting seems to be formatted correctly. (mailmerge_update.jpg)

I would appreciate someone looking at the attached file (the macro is one from here that converts text to the mailmerge format) and let me know if I'm missing anything.

Thanks in advance for any assistance you can offer.
Attached Images
File Type: jpg mailmerge_entered.jpg (10.2 KB, 6 views)
File Type: jpg mailmerge_expanded.jpg (9.2 KB, 6 views)
File Type: jpg mailmerge_updated.jpg (10.0 KB, 6 views)
Attached Files
File Type: docx mail merge replace parenthese with dot CODE.docx (14.5 KB, 1 views)
Reply With Quote
  #2  
Old 07-18-2019, 12:27 AM
gmayor's Avatar
gmayor gmayor is offline Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Windows 10 Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,898
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

Did you actually attach the data source to the document and complete the merge? It's a bit tortuous but Paul's field codes usually work as intended. This lot is no exception.

Press ALT+F9 to toggle the fields and it shows:

{QUOTE{SET Phone {MERGEFIELD LeadBrokerHtcPhone}}
{IF{REF Phone}= “?1*” 1 {IF{REF Phone}= “?2*” 2 {IF{REF Phone}= “?3*” 3 {IF{REF Phone}= “?4*” 4 {IF{REF Phone}= “?5*” 5 {IF{REF Phone}= “?6*” 6 {IF{REF Phone}= “?7*” 7 {IF{REF Phone}= “?8*” 8 {IF{REF Phone}= “?9*” 9 0}}}}}}}}}
{IF{REF Phone}= “??1*” 1 {IF{REF Phone}= “??2*” 2 {IF{REF Phone}= “??3*” 3 {IF{REF Phone}= “??4*” 4 {IF{REF Phone}= “??5*” 5 {IF{REF Phone}= “??6*” 6 {IF{REF Phone}= “??7*” 7 {IF{REF Phone}= “??8*” 8 {IF{REF Phone}= “??9*” 9 0}}}}}}}}}
{IF{REF Phone}= “???1*” 1 {IF{REF Phone}= “???2*” 2 {IF{REF Phone}= “???3*” 3 {IF{REF Phone}= “???4*” 4 {IF{REF Phone}= “???5*” 5 {IF{REF Phone}= “???6*” 6 {IF{REF Phone}= “???7*” 7 {IF{REF Phone}= “???8*” 8 {IF{REF Phone}= “???9*” 9 0}}}}}}}}}

{IF{REF Phone}= “?????1*” “.1” {IF{REF Phone}= “?????2*” “.2” {IF{REF Phone}= “?????3*” “.3” {IF{REF Phone}= “?????4*” “.4” {IF{REF Phone}= “?????5*” “.5” {IF{REF Phone}= “?????6*” “.6” {IF{REF Phone}= “?????7*” “.7” {IF{REF Phone}= “?????8*” “.8” {IF{REF Phone}= “?????9*” “.9” “.0”}}}}}}}}}
{IF{REF Phone}= “??????1*” 1 {IF{REF Phone}= “??????2*” 2 {IF{REF Phone}= “??????3*” 3 {IF{REF Phone}= “??????4*” 4 {IF{REF Phone}= “??????5*” 5 {IF{REF Phone}= “??????6*” 6 {IF{REF Phone}= “??????7*” 7 {IF{REF Phone}= “??????8*” 8 {IF{REF Phone}= “??????9*” 9 0}}}}}}}}}
{IF{REF Phone}= “???????1*” “1.” {IF{REF Phone}= “???????2*” “2.” {IF{REF Phone}= “???????3*” “3.” {IF{REF Phone}= “???????4*” “4.” {IF{REF Phone}= “???????5*” “5.” {IF{REF Phone}= “???????6*” “6.” {IF{REF Phone}= “???????7*” “7.” {IF{REF Phone}= “???????8*” “8.” {IF{REF Phone}= “???????9*” “9.” “0.”}}}}}}}}}

{IF{REF Phone}= “*1???” 1 {IF{REF Phone}= “*2???” 2 {IF{REF Phone}= “*3???” 3 {IF{REF Phone}= “*4???” 4 {IF{REF Phone}= “*5???” 5 {IF{REF Phone}= “*6???” 6 {IF{REF Phone}= “*7???” 7 {IF{REF Phone}= “*8???” 8 {IF{REF Phone}= “*9???” 9 0}}}}}}}}}
{IF{REF Phone}= “*1??” 1 {IF{REF Phone}= “*2??” 2 {IF{REF Phone}= “*3??” 3 {IF{REF Phone}= “*4??” 4 {IF{REF Phone}= “*5??” 5 {IF{REF Phone}= “*6??” 6 {IF{REF Phone}= “*7??” 7 {IF{REF Phone}= “*8??” 8 {IF{REF Phone}= “*9??” 9 0}}}}}}}}}
{IF{REF Phone}= “*1?” 1 {IF{REF Phone}= “*2?” 2 {IF{REF Phone}= “*3?” 3 {IF{REF Phone}= “*4?” 4 {IF{REF Phone}= “*5?” 5 {IF{REF Phone}= “*6?” 6 {IF{REF Phone}= “*7?” 7 {IF{REF Phone}= “*8?” 8 {IF{REF Phone}= “*9?” 9 0}}}}}}}}}
{IF{REF Phone}= “*1” 1 {IF{REF Phone}= “*2” 2 {IF{REF Phone}= “*3” 3 {IF{REF Phone}= “*4” 4 {IF{REF Phone}= “*5” 5 {IF{REF Phone}= “*6” 6 {IF{REF Phone}= “*7” 7 {IF{REF Phone}= “*8” 8 {IF{ }= “*9” 9 0}}}}}}}}}

}

Don't even try to make that into fields without using Import and export field constructions as text. Copy it from the document.

Or you could simply add another column to your data sheet with the number as text in the correct format and merge that field instead.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 07-18-2019, 01:59 PM
trikky trikky is offline Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Windows 10 Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Office 2019
Novice
Phone Number Replace ( ) and - with periods    (I'm ALMOST there)
 
Join Date: Jul 2019
Location: Vancouver, Canada (expat Aussie)
Posts: 3
trikky is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
Did you actually attach the data source to the document and complete the merge? It's a bit tortuous but Paul's field codes usually work as intended. This lot is no exception.
Yes. I tested it on the application I'm using and it gave an error. A merge with my original document, that uses {MERGEFIELD LeadBrokerHtcPhone \*MERGEFORMAT} , worked fine (although the phone number formatting is not what I want, which of course is why I'm looking at making the change. When I uploaded the new document with the revised code, I get an error.

Quote:
Press ALT+F9 to toggle the fields and it shows...
Thanks for the Alt+F9. At least I know the correct code is there. I was a little confused before because I couldn't see it.

Quote:
Don't even try to make that into fields without using Import and export field constructions as text. Copy it from the document.
Yes. Did that, using a macro referred to in Macropod's post, and it converted the fields without giving an error, so as far as that is concerned, I THINK it's good to go.

Quote:
Or you could simply add another column to your data sheet with the number as text in the correct format and merge that field instead.
Alas, it's a database somewhere else in the system and I don't think that it's something that I can easily change. I'm looking further into that.

Ultimately, if this code that I'm using SHOULD work, then it's the application that is the issue. I'm thinking it's the {QUOTE} before the rest of the code that's causing the issue, but I wanted to make sure I hadn't made an error somewhere else.

Thanks again for your help.
Reply With Quote
  #4  
Old 07-18-2019, 07:48 PM
gmayor's Avatar
gmayor gmayor is offline Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Windows 10 Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,898
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

Without seeing the actual merge document and some sample data it is not possible to guess the cause of the error, but the example you posted does work.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 07-19-2019, 04:03 PM
trikky trikky is offline Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Windows 10 Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Office 2019
Novice
Phone Number Replace ( ) and - with periods    (I'm ALMOST there)
 
Join Date: Jul 2019
Location: Vancouver, Canada (expat Aussie)
Posts: 3
trikky is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
Without seeing the actual merge document and some sample data it is not possible to guess the cause of the error, but the example you posted does work.
Thanks, Graham!

If the example I posted works, then it's likely how the application I'm using to create the mail merge document is the issue. I suspect it doesn't like the anything but the MAILMERGE tag, since the word document is saved to the application and it's basically an automatic internal process that creates the doc as needed.

I'll see if there are other solutions, since it's apparently not really to do with the word doc itself.

I appreciate your taking the time to review and answer.

Cheers!

T
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add custom phone number field to Outlook 2013 Contact? King Mustard Outlook 0 06-10-2015 08:12 AM
Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Extracting a phone number from a string that contains text and numbers. hommi16 Excel 2 06-05-2013 09:19 PM
Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Extract phone number from word file donlincolnmsof Word VBA 12 06-19-2012 05:21 PM
Phone Number Replace ( ) and - with periods    (I'm ALMOST there) Phone number formatting Mark Micallef Outlook 1 08-04-2010 02:38 PM
in WORD, how do i type a 9-digit phone number "xxxxxxxxx" and have it automatically.. jay8962 Word 0 04-08-2010 11:08 AM


All times are GMT -7. The time now is 02:02 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft