Multiple '.' in a format should be treated as an escaped character #2584

New Issue

2020-09-29T22:38:05Z

snoopyjc commented

2020-09-29 22:38:05 +00:00

(Migrated from github.com)

If a format contains multiple '.', Excel treats it as if each '.' after the first one is escaped. For example (from valid.tsv):

console.log(SSF.format('00.00.00.000', 12.3456789))
12.35

Excel gives:

12.34.56.789

If a format contains multiple '.', Excel treats it as if each '.' after the first one is escaped. For example (from valid.tsv): console.log(SSF.format('00.00.00.000', 12.3456789)) 12.35 Excel gives: 12.34.56.789

SheetJSDev commented

2022-04-15 22:21:38 +00:00

(Migrated from github.com)

Excel 2019 behavior appears to be the following:

If there is a date token in the format (like hh:mm:ss.000), .0 .00 .000 are interpreted as sub-second values. All other . are literal and bare 0 tokens are invalid (UI does not allow and TEXT returns a #VALUE! error) The actual sub-second rendering depends on the longest sequence: with value 12.3456789, the text for h .0 .0 is 10 .6 .6 while the text for h .0 .00 is 10 .5 .57

If there is no date token, the first . is the decimal position and the subsequent . are literal. The standard # also applies: 1234567.89 with format ####.##.##.##### is 1234567.89..

Excel 2019 behavior appears to be the following: If there is a date token in the format (like `hh:mm:ss.000`), `.0` `.00` `.000` are interpreted as sub-second values. All other `.` are literal and bare `0` tokens are invalid (UI does not allow and `TEXT` returns a `#VALUE!` error) The actual sub-second rendering depends on the longest sequence: with value `12.3456789`, the text for `h .0 .0` is `10 .6 .6` while the text for `h .0 .00` is `10 .5 .57` If there is no date token, the first `.` is the decimal position and the subsequent `.` are literal. The standard `#` also applies: `1234567.89` with format `####.##.##.#####` is `1234567.89..`

Sign in to join this conversation.