CODEWITHSUNDEEP
excelexcel-formulaexcel-2013
Himanshu Agarwal
Himanshu Agarwal

Reputation: 33

TEXT operator in Excel

Why does =TEXT(16,"TEMP000") return a #VALUE error? I am trying to get TEMP016 as the result.

I have tried it with other alphanumeric examples, like =TEXT(16, A092000) which should show A092016, and it works fine there.

Upvotes: 2

Views: 43

Answers (2)

fvu
fvu

Reputation: 32973

It accepts some strings and not others, but at least in my version of Excel (2016 32 bits) escaping the literal string TEMP works fine.

=TEXT(16;"""TEMP""000")

Some (non-exhaustive, unscientific) experimentation later it seems to get utterly confused if one of the date format codes is present in the format string, meaning that at least m, mm, mmm, mmmm, d, dd, ddd, dddd, yy, yyyy, h, hh, m, mm, s, ss are off-limits unescaped in a TEXT format string.

Upvotes: 2

Carsten Massmann
Carsten Massmann

Reputation: 28206

You need to mask the format string like

=TEXT(16;"\t\e\mp000")

since otherwise the letters t, e, and m would have some special meaning in this format string.

Upvotes: 3

Related Questions