Word Mail Merge Format Switches

[marka87uk]

Hi,

I have a Word mail-merge linked to an Excel document. The Excel file is outputted automatically from a program so I would rather not apply any formatting in the spreadsheet itself.

I'd like to display the following fields as below. Please can you advise me on how to do this?

Phone number - 00000 000000 (they may appear as a number or text in the Excel file)
NI number - AA-00-00-00-A

Also dates are appearing in American format even though they appear correctly in the Excel file - How can I change this?

Thanks!

 

[Trevor G]

This link should sort this out.

http://www.gmayor.com/formatting_word_fields.htm

And for the date

http://office.microsoft.com/en-us/word-help/field-codes-date-field-HP005186143.aspx

The only thing to remember is once you have it sorted you switch back the view and move onto another record otherwise you don't see the updated field formats.

 

[marka87uk]

Thanks Trevor,

I still can't seem to get the switches right for the phone number and NI number fields.

The phone number displays correctly using \# "00000 000000" when the data is a straight number in Excel, but if it is already entered with the space (and treated as text), it causes it to be a totally different number! Is there a way to do this without formatting in Excel?

For the NI number I have had no luck as it's a text field.

 

[marka87uk]

Sorry, and the Microsoft link didn't help too much... I have tried { MERGEFIELD "date_joined" \@ "dd/mm/yyyy" } but the dates do not appear correctly.

 

[marka87uk]

Sorry, and the Microsoft link didn't help too much... I have tried { MERGEFIELD "date_joined" \@ "dd/mm/yyyy" } but the dates do not appear correctly.

Got it; didn't realise the mm needed to be capitalised!

 

[Trevor G]

Just back at PC, pleased to read you have a solution.

 

[Macropod]

The phone number displays correctly using \# "00000 000000" when the data is a straight number in Excel, but if it is already entered with the space (and treated as text), it causes it to be a totally different number! Is there a way to do this without formatting in Excel?

For this one, you can use a field coded along the lines of:
{QUOTE{SET Val{MERGEFIELD Phone_No}}{IF{REF Val}= {=Val \# 00000000000} {=Val \# "000000' '00000"} {REF Val}}}

For the NI number I have had no luck as it's a text field.

What do you see when you merge this field with no switches?

 

[chazz]

For this one, you can use a field coded along the lines of:
{QUOTE{SET Val{MERGEFIELD Phone_No}}{IF{REF Val}= {=Val \# 00000000000} {=Val \# "000000' '00000"} {REF Val}}}

What do you see when you merge this field with no switches?

====
Old thread, I know. Can someone please explain to this VBA programmer what this gibberish means?

Thank you.

 

[Macropod]

Can someone please explain to this VBA programmer what this gibberish means?

Well, it's not gibberish and it's not VBA - it's a Word field code that tests whether a string is the same as a numerical evaluation of the string and, depending on the results, outputs either the original string or a formatted version of it.