Formatting Merge Fields

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.

Dates and Times

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 02/26/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

Numbers

A B C D
1 name date mobile balance
2 Joe 02/26/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.