Reformat Merge Field Results -- Unusual Situation!

[Bryan L]

Our CRM DB stores all phone numbers in the same (annoying) format: there can
be no space between the parenthesis and the 7-digit number. For example,
(800) 555-1212 is stored as (800)555-1212. Many of us think it looks ugly
and want that space added in when we merge Form Letters that include ph/fax
numbers. However, I haven't found a way to do this programmatically. I've
tried the obvious, like using {MERGEFIELD "UserFax" \# "(###) ###'-'####"},
but when such formatting is applied to a field, Word doesn't recognize the
field when merging, giving an "Invalid Merge Field" error that states the
merge field does not exist in the data source.

Apparently the CRM system presents the data to Word as already-formatted
text, so I've been looking for ways to parse the text of a merge field
result, but haven't found a way to accomplish this yet. In a nutshell, is
there a way I can consistently add that space into the merge field result of
all my phone number fields?

Thanks in advance,

Bryan

 

[Graham Mayor]

You cannot format a text field with a number switch and sadly there are no
MID/LEFT/RIGHT etc function available in fields. The best plan might be to
merge to a new document then use replace (with a macro if preferred) to
reformat the numbers ie

Replace
(\([0-9]{3}\))([0-9]{3}-[0-9]{4})
with
\1 \2
with the wildcard option set

 

[Peter Jamieson]

If the data source is e.g. a delimited text file that Word can read using
ODBC or OLE DB then you may be able to do this by making the connection
using VBA and using SQL to insert the space in the field. To get some idea
of whether or not that might be feasible in this case, I suggest you try to
follow the article at

http://tips.pjmsn.me.uk/t0004.htm

which does not cover quite the same ground but should be enough to tell you
whether you can connect using an approach that lets you do that.