TrueData TrueData Sheets
search
Searching...
functions Formulas
menu_book Pages
manage_search Content Match
search_off

No results for ""

Loading Content
transform

TEXT() Formula

Converts a value to text in a specific format. Use it to control exactly how numbers, dates and times appear in labels, reports and joined strings.

Syntax
=TEXT(value, "format_text")
=TEXT(1234.567, "$#,##0.00") arrow_forward $1,234.57
value

A number, a formula that returns a number, or a reference to a cell containing a number.

format_text

The display pattern in double quotes. Same codes as Custom Format.

ANumber Formats BLeading Zeros & Digit Placeholders CCurrency Formatting DPercentage Formatting ENegative Numbers & Zero Handling FDate Formatting GTime Formatting HDate & Time Combined IScientific Notation & Fractions JCustom Text, Symbols & Units

spellcheck Format Code Cheat Sheet

Code Meaning Example
0 Digit, pads with zero 00 → 05
# Digit, no padding (hides zero) ### → 123
? Digit, pads with space ???.?? → 12.3
, Thousands separator #,##0 → 1,234
% Multiplies by 100, adds % 0% → 25%
E+00 Scientific notation 0.00E+00 → 1.23E+06
# ?/? Fraction # ?/? → 1 3/4
d dd ddd dddd Day, padded, short name, full name dddd → Monday
m mm mmm mmmm Month number, padded, short, full mmm → Oct
yy yyyy 2-digit / 4-digit year yyyy → 2026
h hh HH Hours (padded, 24-hour) HH:mm → 23:59
mm / ss Minutes (after h) / seconds hh:mm:ss → 09:05:07
AM/PM 12-hour clock marker h:mm AM/PM → 2:30 PM
[h] Elapsed hours (can exceed 24) [h]:mm → 12:00
"text" Literal text (double the quotes inside a formula) 0 ""items""
? $ € £ Literal currency prefix ?#,##0 → ?1,500

science Examples by Category

Type the formula in a cell and it returns the result shown next to it.

tag

A. Number Formats

10 examples
Formula Result
=TEXT(123,"0") arrow_forward 123
=TEXT(123.456,"0") arrow_forward 123
=TEXT(123.456,"0.0") arrow_forward 123.5
=TEXT(123.456,"0.00") arrow_forward 123.46
=TEXT(123.4,"0.000") arrow_forward 123.400
=TEXT(1234.56,"#,##0.00") arrow_forward 1,234.56
=TEXT(1234567,"#,##0") arrow_forward 1,234,567
=TEXT(12.5,"0.00") arrow_forward 12.50
=TEXT(0,"0.00") arrow_forward 0.00
=TEXT(0.456,"0.0000") arrow_forward 0.4560
pin

B. Leading Zeros & Digit Placeholders

8 examples
Formula Result
=TEXT(5,"00") arrow_forward 05
=TEXT(25,"0000") arrow_forward 0025
=TEXT(123,"000000") arrow_forward 000123
=TEXT(0,"000") arrow_forward 000
=TEXT(12.5,"000.00") arrow_forward 012.50
=TEXT(123,"###") arrow_forward 123
=TEXT(0,"###") arrow_forward (empty)
=TEXT(12.3,"???.??") arrow_forward 12.3
currency_rupee

C. Currency Formatting

6 examples
Formula Result
=TEXT(1500,"?#,##0") arrow_forward ?1,500
=TEXT(2500,"$#,##0.00") arrow_forward $2,500.00
=TEXT(2500,"€#,##0.00") arrow_forward €2,500.00
=TEXT(1000,"£#,##0.00") arrow_forward £1,000.00
=TEXT(1500,"Rs. #,##0.00") arrow_forward Rs. 1,500.00
=TEXT(-250,"?#,##0.00") arrow_forward -?250.00
percent

D. Percentage Formatting

7 examples
Formula Result
=TEXT(0.25,"0%") arrow_forward 25%
=TEXT(0.256,"0.0%") arrow_forward 25.6%
=TEXT(0.2567,"0.00%") arrow_forward 25.67%
=TEXT(1,"0%") arrow_forward 100%
=TEXT(0,"0.00%") arrow_forward 0.00%
=TEXT(-0.15,"0.0%") arrow_forward -15.0%
=TEXT(1.256,"0.00%") arrow_forward 125.60%
exposure

E. Negative Numbers & Zero Handling

6 examples
Formula Result
=TEXT(-123,"0") arrow_forward -123
=TEXT(-123.456,"0.00") arrow_forward -123.46
=TEXT(-1234.5,"#,##0.00") arrow_forward -1,234.50
=TEXT(0,"0") arrow_forward 0
=TEXT(0,"0.00") arrow_forward 0.00
=TEXT(-0.005,"0.00") arrow_forward -0.01
calendar_today

F. Date Formatting

16 examples
Formula Result
=TEXT(DATE(2026, 10, 10),"dd-mm-yyyy") arrow_forward 10-10-2026
=TEXT(DATE(2026, 10, 10),"dd/mm/yyyy") arrow_forward 10/10/2026
=TEXT(DATE(2026, 10, 10),"yyyy-mm-dd") arrow_forward 2026-10-10
=TEXT(DATE(2026, 1, 5),"d/m/yy") arrow_forward 5/1/26
=TEXT(DATE(2026, 1, 5),"dd/mm/yy") arrow_forward 05/01/26
=TEXT(DATE(2026, 10, 10),"mmmm d, yyyy") arrow_forward October 10, 2026
=TEXT(DATE(2026, 10, 10),"mmm d, yyyy") arrow_forward Oct 10, 2026
=TEXT(DATE(2026, 10, 10),"mmmm") arrow_forward October
=TEXT(DATE(2026, 10, 10),"mmm") arrow_forward Oct
=TEXT(DATE(2026, 10, 12),"dddd") arrow_forward Monday
=TEXT(DATE(2026, 10, 10),"ddd") arrow_forward Sat
=TEXT(DATE(2026, 10, 10),"dd") arrow_forward 10
=TEXT(DATE(2026, 10, 10),"mm") arrow_forward 10
=TEXT(DATE(2026, 10, 10),"yy") arrow_forward 26
=TEXT(DATE(2026, 10, 10),"yyyy") arrow_forward 2026
=TEXT(DATE(2026, 1, 1),"dddd, mmmm d, yyyy") arrow_forward Thursday, January 1, 2026
schedule

G. Time Formatting

11 examples
Formula Result
=TEXT(TIME(9, 5, 0),"hh:mm:ss") arrow_forward 09:05:00
=TEXT(TIME(9, 5, 0),"h:mm") arrow_forward 9:05
=TEXT(TIME(14, 30, 0),"hh:mm AM/PM") arrow_forward 02:30 PM
=TEXT(TIME(14, 30, 0),"h:mm AM/PM") arrow_forward 2:30 PM
=TEXT(TIME(0, 0, 0),"hh:mm AM/PM") arrow_forward 12:00 AM
=TEXT(TIME(12, 0, 0),"hh:mm AM/PM") arrow_forward 12:00 PM
=TEXT(TIME(23, 59, 0),"HH:mm") arrow_forward 23:59
=TEXT(TIME(9, 5, 7),"hh:mm:ss") arrow_forward 09:05:07
=TEXT(TIME(9, 5, 7),"h:mm:ss AM/PM") arrow_forward 9:05:07 AM
=TEXT(TIME(12, 34, 56),"mm:ss") arrow_forward 34:56
=TEXT(0.5,"[h]:mm") arrow_forward 12:00
event

H. Date & Time Combined

4 examples
Formula Result
=TEXT(DATE(2026, 10, 10)+TIME(14, 30, 0),"dd-mm-yyyy hh:mm") arrow_forward 10-10-2026 14:30
=TEXT(DATE(2026, 10, 10)+TIME(14, 30, 0),"dd/mm/yyyy hh:mm AM/PM") arrow_forward 10/10/2026 02:30 PM
=TEXT(DATE(2026, 1, 5)+TIME(9, 5, 7),"ddd, dd mmm yyyy hh:mm:ss") arrow_forward Mon, 05 Jan 2026 09:05:07
=TEXT(DATE(2026, 12, 31)+TIME(23, 59, 59),"yyyy-mm-dd hh:mm:ss") arrow_forward 2026-12-31 23:59:59
science

I. Scientific Notation & Fractions

5 examples
Formula Result
=TEXT(1234567,"0.00E+00") arrow_forward 1.23E+06
=TEXT(0.00123,"0.00E+00") arrow_forward 1.23E-03
=TEXT(0.5,"# ?/?") arrow_forward 1/2
=TEXT(0.25,"# ?/?") arrow_forward 1/4
=TEXT(1.75,"# ?/?") arrow_forward 1 3/4
text_snippet

J. Custom Text, Symbols & Units

4 examples
Formula Result
=TEXT(123,"0.00 kg") arrow_forward 123.00 kg
=TEXT(50,"0 ""items""") arrow_forward 50 items
=TEXT(0.25,"0% ""complete""") arrow_forward 25% complete
=TEXT(1234,"#,##0 ""units""") arrow_forward 1,234 units
lightbulb
Pro tips

TEXT() returns text, not a number, so use it for the final display only. mm means month beside d/y and minutes beside h/ss. To add words, double the quotes: "0 ""items""".

Truedata Icon Sheets by Truedata

Empowering traders and analysts with high-performance real-time market data directly within their spreadsheets.

Resources

Support

© 2026 TrueData Market Data Services LLP. All rights reserved.

Privacy Policy Terms of Service