To understand what a merge field is see our explanation of basic field merging. Here we explain how to direct ExcelSend to customize the formatting of the data when it is merged.
You can use a date value from your spreadsheet and alter the way it looks in your text message. The following examples work with a spreadsheet like this.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | name | date | mobile | balance |
| 2 | Joe | 26/02/2021 15:11:29 | 078 00000000 | 12.50 |
A straight merge is just the name of the spreadsheet column between double-carats ^^. e.g.
^^date^^
Formatting instructions are added inside curly brackets { }. For date and time fields the instruction always starts with the letter d followed by a colon : . This is then followed by the actual format instruction. For example:
^^date{d:M}^^
This tells ExcelSend the field is a date field and the M is the requested output format in the text message.
Straight merge - no formatting:
^^date^^ = 2/26/2021 3:11:29 PM
Date only:
^^date{d:yyyy-MM-dd}^^ = 2021-02-26
^^date{d:MM/dd/yyyy}^^ = 02/26/2021
^^date{d:M}^^ = February 26
^^date{d:Mx}^^ = February 26th
Time only:
^^date{d:h:mm}^^ = 3:11
^^date{d:hh:mm}^^ = 03:11
^^date{d:HH:mm}^^ = 15:11
^^date{d:h:mmtt}^^ = 3:11PM
^^date{d:t}^^ = 3:11 PM
Here is a table of common specifiers that can be used for a datetime value:
| Specifier | Type | Example | Output |
| d | Short date | {d:d} | 26/10/2021 |
| D | Long date | {d:D} | Friday February 26, 2021 |
| t | Short time | {d:t} | 3:11 PM |
| T | Long time | {d:T} | 3:11:29 PM |
| f | Full date & time | {d:f} | Friday February 26, 2021 3:11 PM |
| F | Full date & time (long) | {d:F} | Friday February 26, 2021 3:11:29 PM |
| g | Default date & time | {d:g} | 26/12/2021 3:11 PM |
| G | Default date & time (long) | {d:G} | 26/12/2021 3:11:29 PM |
| M | Month day pattern | {d:M} | February 26 |
| Mx | Month day with ordinal indicator st/nd/rd/th | {d:Mx} | February 26th |
| s | Sortable date string | {d:s} | 2021-02-26T15:11:29 |
| u | Universal sortable, local time | {d:u} | 2021-02-26 15:11:29Z |
| U | Universal sortable, GMT | {d:U} | February 26, 2021 3:11:29 AM |
| Y | Year month pattern | {d:Y} | February, 2021 |
You can customize your format using a combination of the specifiers in the table below.
| Specifier | Type | Example | Output |
| dd | Day | {d:dd} | 26 |
| x | Day with ordinal indicator st/nd/rd/th | {d:ddx} | 26th |
| ddd | Day name | {d:ddd} | Fri |
| dddd | Full day name | {d:dddd} | Friday |
| f, ff, … | Second fractions | {d:fff} | 932 |
| gg, … | Era | {d:gg} | A.D. |
| hh | 2 digit hour | {d:hh} | 03 |
| HH | 2 digit hour, 24hr format | {d:HH} | 15 |
| mm | Minute 00-59 | {d:mm} | 11 |
| MM | Month 01-12 | {d:MM} | 02 |
| MMM | Month abbreviation | {d:MMM} | Feb |
| MMMM | Full month name | {d:MMMM} | February |
| ss | Seconds 00-59 | {d:ss} | 29 |
| tt | AM or PM | {d:tt} | PM |
| yy | Year, 2 digits | {d:yy} | 21 |
| yyyy | Year | {d:yyyy} | 2021 |
| zz | Timezone offset, 2 digits | {d:zz} | -05 |
| zzz | Full timezone offset | {d:zzz} | -05:00 |
| : | Separator | {d:hh:mm:ss} | 3:11:29 |
| / | Separator | {d:dd/MM/yyyy} | 26/02/2021 |
For example:
^^Date{d:yyyy-MM-dd}^^
to give an output of
2021-02-26
Another example for appointment reminders can be made to be more user friendly:
Please don't forget your appointment on ^^Date{d:Mx}^^ at ^^Date{d:HH:mm}^^
would yield the following
Please don't forget your appointment on February 26th at 15:11
| A | B | C | D | |
|---|---|---|---|---|
| 1 | name | date | mobile | balance |
| 2 | Joe | 26/02/2021 15:11:29 | 078 00000000 | 12.50 |
A straight merge is just the name of the spreadsheet column between double-carats ^^. e.g.
^^balance^^
Formatting instructions are added inside curly brackets { }. For number fields the instruction always starts with the letter n followed by a colon : . This is then followed by the actual format instruction. For example:
^^balance{n:c}^^
This tells ExcelSend the field is a number field and the c is the requested output format in the text message.
Straight merge - no formatting:
^^balance^^ = 12.5
With formatting:
^^date{n:c}^^ = $12.50
Here is a table of common specifiers that can be used for a number field:
| Specifier | Type | Format | Example Merge Field | Sample Output for 12.5 |
| c | Currency | {n:c} | ^^balance{n:c}^^ | $12.50 |
| d |
Decimal (Works with whole numbers only) |
{n:d} | ^^balance{n:d}^^ | 12 |
| e | Scientific | {n:e} | ^^balance{n:e}^^ | 1.250000e+001 |
| f | Fixed point | {n:f} | ^^balance{n:f}^^ | 12.50 |
| g | General | {n:g} | ^^balance{n:g}^^ | 12.5 |
| n |
Number with commas for thousands |
{n:n} | ^^balance{n:n}^^ | 12.50 |
For most of the specifiers in the table above, you can add in a precision value. For example to show a currency value with 3 decimal places you would use
^^balance{n:c3}^^
You can customize your number formatting using the specifiers below:
| Specifier | Type | Example | Sample Output for 1500.42 | Note |
| 0 | Zero placeholder | {n:00.0000} | 1500.4200 | Pads with zeroes. |
| # | Digit placeholder | {n:(#).##} | (1500).42 | |
| . | Decimal point | {n:0.0} | 1500.4 | |
| , | Thousand separator | {n:0,0} | 1,500 | Must be between two zeroes. |
| ,. | Number scaling | {n:0,.} | 2 | Comma adjacent to Period scales by 1000. |
| % | Percent | {n:0%} | 150042% | Multiplies by 100, adds % sign. |
| e | Exponent placeholder | {n:00e+0} | 15e+2 | Many exponent formats available. |
