Formatting Your Data

There are many different ways to format your data. In this post, when we talk about formatting, we don’t mean styling (font, color, etc) – we are talking about altering the format of your data. For example, you have created a document that includes numbers, but the data comes through without pretty formatted numbers that include commas to separate the thousands. Or you may want to convert a word to all lowercase. Much of this formatting can be done pretty easily.

To format the merge field data, we're going to use what is called a "modifier".  This is a little piece of code that is added after the merge field name, separated with a pipe "|".

Here is a list of available modifiers:

Name Description Example
abbreviation Convert text to abbreviation (grab first letter of each word)

{$Field|abbreviation:"suffix":"prefix"}
{$FullName|abbreviation}
John Smith = JS

{$FullName|abbreviation:"."}
John Smith = J.S.

age Convert date into age (years)

{$Birthdate|age}

age_to_words Convert date into age in words

{$Birthdate|age_to_words} = 2 years 3 days 4 hours

bitly Convert URL to Bitly link

{$Website|bitly:"username":"password"}

capitalize Capitalize the first letter of a merge field

{$FirstName|capitalize}

capitalize_sentences Capitalize the first word in each sentence

{$Description|capitalize_sentences}

cat Append text to the end of a merge field

{$Name|cat:" is ready"}

csv_to_array Convert CSV string (or URL) to an array

{$csv = $file_url|csv_to_array}

currency_format Format a currency value

{$TotalAmount|currency_format:"$"} = $1,298.99

International Format:
{$TotalPrice|currency_format:'€':2:',':'.'}

date_format Change the format of a date

{$StartDate|date_format:"m/d/Y"}
{strtotime("+ 5 days", strtotime($_date))|date_format:"m/d/Y"}
For more options, check out www.php.net/date

decimal_to_words Convert a decimal/number to words

{$Ratio|decimal_to_words} = three and 7 tenths

default Set a default value for a merge field

{$FavoriteColor|default:"None"}

expand_url Get real URL (follow redirects) from a shortened URL

{$short_url|expand_url}

get_file Download file from URL

{$text = $url|get_file}

html2office Convert HTML to Office doc formatting

{$Description|html2office}

html2text Convert HTML to plain text

{$Description|html2text}

implode Combine list of array values into single string with a separator

{implode(", ", $Colors)}
comma separated list of the $Colors

list List array values in a commas separated list

{$Colors|list:",":"and"} = Red, Green, and Blue

lower Convert all characters to lowercase

{$Name|lower}
WebMerge = webmerge

money_to_words Convert price to words

{$Total|money_to_words}
10.50 = ten dollars and 50 cents

$Total|money_to_words:"euro"}
11 = eleven euros

nl2 Convert new-line characters to given string

{$Description|nl2:", "}

nl2br Convert new-line characters to HTML line breaks <br>

{$Description|nl2br}

nl2p Convert new-line characters to HTML paragraph

{$Description|nl2p}

number Convert merge field to a number (for calculations)

{$Total|number}
10,231 = 10231

int_number Convert international number to US format (for calculations)

{$Total = $Subtotal|int_number + $Tax|int_number}

 number_format Change the format of a number (commas and decimals)

{$TotalPrice|number_format:2}
5234.35 = 5,234.35

International format (comma for decimal place)
{$TotalPrice|number_format:2:',':'.'}

number_to_words Convert number to words

{$Total|number_to_words}

pad Pad merge field with the given string, so it is the given length

{$AccountNumber|pad:12:"0"}
123456 = 00000012345

phone_format Format a phone number

{$WorkPhone|phone_format:"(%3) %3-%4"}
123-555-7890 = (123) 555-7890

{$phone|phone_format:"%3.%3.%4"} = 123.456.7890

replace Search and replace a string in a merge field

{$Address|replace:"Street":"St"}
123 S Main Street = 123 S Main St

round Round a number to number of decimal places provided

{$Price|round:2}
{$Price|round}

spacify Insert a space (or string) in between each character in merge field

{$Name|spacifiy}
Thomas = T h o m a s

{$AccountNumber|spacify:"-"}
123456 = 1-2-3-4-5-6

state_abbreviation Convert US State name to abbreviation

{$State|state_abbreviation}

strip Replace all new lines, spaces, and tabs with a single space

{$Description|strip}

strip_tags Remove all HTML tags from merge field

{$Description|strip_tags}
{$Description|strip_tags:"<br>"} (allow <br>)

substr Create a sub-string of the merge field

{$Description|substr:start:length}
{$Description|substr:0:40}

text2html Convert text to HTML

{$Description|text2html}

translate Translate text into another language

{$Description|translate:"de"}

via Google Translate - Languages

truncate Truncate/shorten the merge field to the given length

{$Description|truncate:140}    (140 characters)

ucfirst Capitalize the first letter in the first word

{$FirstName|ucfirst}

ucwords Capitalize the first letter in each word

{$Name|ucwords}

upper Convert all characters to uppercase

{$Name|upper}
WebMerge = WEBMERGE

xml_safe Encode merge field data for XML (Office) docs

{strftime("%d %B %Y", strtotime($_date))|ucwords|xml_safe}

 

  • International Date/Translation
    • {$_ = setlocale(LC_TIME, "spanish")}
    • Then you can print a date like this: {strftime("%d %B %Y", strtotime($_date))|ucwords}
    • More formatting options for strftime: http://php.net/strftime

 

For more information on using variable modifiers, please visit http://www.smarty.net/docs/en/language.modifiers.tpl

Have more questions? Submit a request

106 Comments

  • Avatar
    Jeremy Clarke

    Hi Mike,

    You'll want to setup your Word template like this for the image: https://support.webmerge.me/hc/en-us/articles/206526946-Embed-Images-Signatures-in-Word-Excel-PowerPoint

  • Avatar
    Scott Taylor

    Hi Jeremy,

    I'm creating documents that have a very long footer. I would like to use the footer in Webmerge, but the text doesn't seem to fit. is there a way to wrap the footer text, use a merge field in the footer, or add the text as an image file to the footer?

  • Avatar
    Jeremy Clarke

    Hi Ben,

    If you use HTML code in the footer, it should wrap the text and format it better. So you could do something like this:


    TEXT HERE

    Or you can use an image like this:

  • Avatar
    Hans Stolk

    Hi Jeremy,

    Decimals are automatically shown even if there aren't any. Can I change that?
    So I do not want 2,0000 but only 2. If there are any decimals, I would like it to be shown exactly like it is.

  • Avatar
    Jeremy Clarke

    Hi Hans,

    The decimals are coming over from wherever you are sending data from, but you can hide them like this in your template: {$FieldName|replace:",0000":""}

  • Avatar
    Jeremy Turner

    I have have added currency and date modifiers on the webmerge update mapping page to the salesforce fields.

    {$Salesforce_Field|currency_format:"$"}
    {$Salesforce_Field|date_format:"m/d/Y"}

    But after i click save the salesforce fields update to --None--, what am i doing wrong?

  • Avatar
    Jeremy Clarke

    Hi Jeremy,

    These formatting codes actually go inside your template (or the Field Map: https://support.webmerge.me/hc/en-us/articles/206526086-Field-Map). You will not use them in your Salesforce (or other integration) settings.

    Thanks!

  • Avatar
    Dinesh Kumar

    Hi Jeremy

    i need to display time HH:MM:SS in salesforce webmerge

    please help me on this

    Edited by Dinesh Kumar
  • Avatar
    Dinesh Kumar

    i got the solution by substr:0:5

    Edited by Dinesh Kumar
  • Avatar
    Jeremy Clarke

    Hi Dinesh,

    You can also do this: {$FieldName|date_format:"H:i:s"}

  • Avatar
    William Kim

    Hi Jeremy,
    I'm having trouble trying to add a certain variable number of days {$timeframe} to a date {$date} to give a deadline {$deadline}.
    For example, input into data capture the {$date} = 24/12/2018
    input {$timeframe} = 30 days
    how would you do the formula to calculate the {$deadline}?

  • Avatar
    Jeremy Clarke

    Hi William,

    Yes, you can use strtotime() in your template like this:

    {strtotime("+ "|cat:$timeframe|cat:" days", strtotime($date))|date_format:"d/m/Y"}

  • Avatar
    William Kim

    Hi Jeremy,
    Thanks for the reply.
    I've tried the formula you provided, however when I test it, and use 26/12/2018 as the $date and 30 days as the $timeframe, I get 31/01/1970 as the output. Is there something else I'm missing?

  • Avatar
    Jeremy Clarke

    Hi William,

    Yes, when you are using slashes in the date, the system expects an mm/dd/yyyy format. You'll need to use dd-mm-yyyy format if you want the date to come first. If you don't have the ability to convert the slashes to dashes in your merge data, you can replace them like this:

    {strtotime("+ "|cat:$timeframe|cat:" days", strtotime($date|replace:"/":"-"))|date_format:"d/m/Y"}

  • Avatar
    Jane Isaac

    My table has 2 TIME fields in it. This is from a Salesforce record. They are coming out like this: 10:30:00.000Z

    Is there some formatting I can add so that it comes out as 10:30 AM?

    And if any afternoon hours are logged, the resulting merged documents display as 24 hour clock time, such as 14:45:00.000Z. This org is on the 12 hour clock so it should come out as 2:45 PM

    Edited by Jane Isaac
  • Avatar
    Jeremy Clarke

    Jane,

    Yes, you can format times like: {$FieldName|date_format:"h:i A"} -- more options: www.php.net/date

Please sign in to leave a comment.
Powered by Zendesk