##### ExcelSend
This information relates to the ExcelSend Desktop and Portal versions and not to the newer ExcelSend Microsoft Excel Add-in

## 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 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:

would yield the following

## Numbers

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.