Make the Switch!


This is wrong!

When you want to merge data from Excel into a Word document it might not return the result you were hoping for. During my classes I regularly hear complaints about the date being wrong, number formats that are incorrect etc. I know that my friends from the United States always tell me that the european people don’t use the correct dateformat. We always blame them that their dates are incorrect. What are we talking about. If you merge data from Excel which contains datefields it will show up in the US format. For instance 6/18/2014. In Europe we read this as the 6th day from the 18th month of the year 2014. This has to be corrected in 18/6/2014.

Fortunately for us Microsoft Word has the Field Switch option to help us with this situation. Switch


Let’s take a look at the example below:

The first number is formatted as €20.000, the second field is formatted as 11/6/2014 and the last field is formatted as 18/5/2014. So in our source the formatting is correct. Now when we merge this data to Word it gets changed to the formatting as below in the picture!.

 Mergefields_I


How do we change this:

First switch to the field code view by pressing the Alt-key and then the F9 key. This is a toggle combination so if you click this key combination again it will switch back to its original view.

 Mergefields


Now we add some additional information to the mergefields to correct the display in our document:

 Mergefields_II


There are a couple of simple things you should know when you work with switches.

  • First when converting a number you start your switch with \#
  • When you need to convert datefields you use the \@ switch
  • When you need to convert characters you use the \* switch.
  • With that in mind you can start experimenting. I just want to leave you with one final comment. If you need to convert a date field bare in mind that the MM as you see it in the example is on purpose. Word makes a distinction between minutes and month displays. So if you need a correct month view you must use capitalized letters MM to get the correct result. If you forget this you will end up with a couple of zeros.


    Custom format
    If you have a specific number format you are looking for just copy paste it from the cell properties in the numbers tab from within Excel. You can find them under the option Custom.

    So no more wrong dates, no hassle with regional settings or what ever. Just make the switch!

    🙂

    Advertisements

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s