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.

add_days Add days to date

{$Date|add_days:10}

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"}

calendar Make calendar from array of dates

{json_encode($dates)|calendar}

$dates = [{"name":"Make Offer", "date":"3/5/2017"}, {"name":"Submit Escrow  Check", "date":"3/21/2017"}, {"name":"Close Deal", "date":"4/15/2017"}]

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}

html_safe Convert characters to HTML safe characters

{$word|html_safe}

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

int_number Convert international number to US format (for calculations) 

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

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

markdown2html Convert markdown to html

{$Description|markdown2html}

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

number_eu_to_us Converts a number from EU format to US

{$eu_number|number_eu_to_us}

number_us_to_eu Converts a number from US format to EU

{$us_number|number_us_to_eu}

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

parse_url Searches string for a URL

{$image|parse_url}

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}

time_to_words Creates a text representation of the number of seconds

{$time_in_seconds|time_to_words}

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
    • 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

123 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

  • Avatar
    Paul Campbell

    Hey there,

    Trying to use html2text function for a field in an excel merge (as a table). merge works fine without the html2text but as soon as i add it doesnt work?

    Thanks

  • Avatar
    Jeremy Clarke

    Hi Paul,

    Can you please try converting the field value to text in the Field Map (https://support.webmerge.me/hc/en-us/articles/206526086-Field-Map)? By the time the data gets into your Excel document, we've already removed the HTML. So in your Field Map, you would use something like:

    {$FieldName|html2text}

  • Avatar
    Dov Goldberg

    Is there a modifier to format the way an address shows on a document?

    Right now, it's showing as:
    Address Line 1
    City, Province Postal Code
    Country

    I would like it to show as:
    Address line 1, City, Province, Postal Code

    Also, the province is abbreviated and I would like it to show the complete province name.

    Is that possible?

  • Avatar
    Jeremy Clarke

    Hi Dov,

    Unfortunately, we don't have a way to convert the abbreviation, but you should be able to do something like this to remove that line break:

    {$Address|nl2:", "}

  • Avatar
    Dov Goldberg

    Hi,

    How do I add commas between a bunch of merge fields that are not part of a list and aren't always all filled in? Right now, commas are appearing when placed between the merge fields but I would only like them to appear when the preceding merge field is filled and for the space to remain blank when the preceding merge field is empty.

    It's currently set up like this:
    {$A}, {$B},{$C},{$D},{$E},{$F}

    and showing like this:
    A,B,C,,, (because D,E,F were purposely left blank when completing the form).

  • Avatar
    Jeremy Clarke

    Hi Dov,

    Try this:

    {$A}{if !empty($B)}, {$B}{/if}{if !empty($C)},{$C}{/if}{if !empty($D)},{$D}{/if}{if !empty($E)},{$E}{/if}{if !empty($F)},{$F}{/if}

  • Avatar
    Darius Grimes

    I'm bringing a 4 digit number in for year built. The data is 1983 but the report is showing 198300000 how do I eliminate the extra zeros?

  • Avatar
    Jeremy Clarke

    Hi Darius,

    The easiest thing to do is use substr() to create a substring like this:

    {substr($year, 0, 4)}

  • Avatar
    Darius Grimes

    Im using JSON to Document Builder in WM

  • Avatar
    Darius Grimes

    Also tried this, it didnt work either

    '{foreach from=$_0.record.proprecords item=_comment}{$_comment.record.correct_yrblt|substr:0:4}{/foreach}'

  • Avatar
    Jeremy Clarke

    Darius,

    I bet the other 0's are coming from the other items in your array... Try this:

    {foreach from=$_0.record.proprecords item=_comment}{if !empty($_comment.record.correct_yrblt)}{$_comment.record.correct_yrblt|substr:0:4}{/if}{/foreach}

  • Avatar
    Darius Grimes

    Makes sense didn't think of that. Yep that did it, thanks.

  • Avatar
    Admistrative Systs Stanford University

    Hi Team,
    I need to the below format for child records
    {$CHILD_RECORD|list:",":"and"} (List array values in a commas separated list)
    I have written soql to retrieve the data
    Select name from customobject where parentid = ':recordId'.

    Thanks

  • Avatar
    Jeremy Clarke

    Hello,

    You'll need to first create the array of just the names (and SOQL statement creates an array of objects), like this:

    {foreach from=$CHILD_RECORD item=_row}{$_names[] = $_row.Name}{/foreach}{$_names|list:",":"and"}

Please sign in to leave a comment.
Powered by Zendesk