Use the TEXT function for Base

Help Center

AI Assistant

Help Center AI Assistant is now available

Got questions about Lark? Use our AI chat to find the answers.

Click and hold to drag

Got It

Try Now

Use the TEXT function for Base

2 min read

I. Intro

The TEXT function converts numbers to text in the specified format. It can be used to convert field results to text or to combine numbers with text or symbols.

Arguments:

TEXT(value, format)

value: The value to be converted.

format: The format you want to convert the specified value to. This can be customized.

Supported formats

Format and placeholders	Description	Example
#	Digit placeholder that represents optional digits and does not display extra zeros	TEXT(23.1,"##.#")=23.1 TEXT(23.17,"##.#")=23.2 TEXT(23.17,"###.#")=23.2 TEXT(23.1,"##.##")=23.1
0	Digital placeholder that displays insignificant zeros	TEXT(23.1,"0.00")=23.10 TEXT(23.17,"0.00")=23.17 TEXT(23.1,"000.00")=023.10
,	Thousands separator, used between # or 0	TEXT(23100,"0,0")=23,100
%	Percent sign, used with # or 0	TEXT(23,"0%")=2300%
YYYY	Year unabbreviated	TEXT("2022-4-15","YYYY")=2022
YY	Year abbreviation	TEXT("2022-4-15","YY")=22 TEXT("2022-4-15","Y")=22
MMM	Month unabbreviated	TEXT("2022-4-15","MMM")=April
MM	Month number unabbreviated	TEXT("2022-4-15","YYYY/MM")=2022/04
M	Month number abbreviated	TEXT("2022-4-15","M")=4
DD	Date unabbreviated	TEXT("2022-4-1","DD")=01
D	Date abbreviated	TEXT("2022-4-1","D")=1
DDDD	Week unabbreviated	TEXT("2022-4-1","DDDD")=Friday
DDD	Week abbreviation	TEXT("2022-4-1","DDD")=Fri
hh	Hours	TEXT("17:30","hh")=17
mm	Minutes	TEXT("17:30","hh:mm")=17:30
ss	Seconds	TEXT("17:30:29","ss")=29

II. Steps

Open the base and click the + icon on the right-most side of the table. Enter a field type and change the field type to Formula.

250px|700px|reset

In the formula editor, enter the TEXT function. Select the table or field to which you want to apply the formula, and enter the format you need.

For example, in a sales report, if the sales rep wants to use the date as the contract code, they can use TEXT([Date],"YYMMDD"). As shown in the figure below, the date format is converted into the required format.

250px|700px|reset

III. Use cases

Combine dates with text

Scenario: Combine the date with stock level to create a unique string.

Formula: TEXT([Date purchased],"MM/DD/YYYY")&"purchased"&"-"&[Stock]

Note: The & symbol in the formula can be used to concatenate characters or strings.

250px|700px|reset

Calculate working hours

Scenario: Calculate working hours based on when staff arrive and leave the office and display it in hours and minutes.

Formula: TEXT([Clock out time]-[Clock in time],"hh:mm")

250px|700px|reset

Create a standardized format

Scenario: Create an Employee ID by combining an employee's department and employee code.

Formula: [Department]&"-"&TEXT([Code],"0000")

250px|700px|reset

Calculate the difference in hours between two dates

Scenario: When scheduling a meeting, you may need to calculate the time difference between participants in different countries and regions.

Description: When calculating the difference, you can specify the format shown in the final result.

Formula: TEXT([Time 1]-[Time 2],"[h]")

Note: Time 1 cannot be earlier than Time 2.

250px|700px|reset

IV. FAQs

If the return value contains both date and time [yyyy-mm-dd hh:mm], how can it be adjusted to only have the date [yyyy-mm-dd]?

Method 1: Create a new formulas field and enter TEXT([Return value to be adjusted],"YYYY-MM-DD").

Method 2: Double-click the current formulas field, select Date as its field format, and select the date format you need.