Text expressions

A textual value is a sequence of letters, numbers, punctuation, emoji, spaces, or other characters that is not recognized as some other data type or expression keyword or operator.

A text expression is an expression that produces a textual value, or that uses one or more textual values to produce a value of any type.

The basic textual data type is Text, which may contain only one line of text. The LongText data type allows multiple lines of text. Several other column types, including Address, Email, and Name, have additional characteristics but are fundamentally textual values, and may be used as such within expressions.

New to expressions and formulas? See also Expressions: The Essentials.

Constructing text

Constructing a raw textual value

Within an expression, in general, AppSheet will treat any sequence of letters, numbers, punctuation, emoji, or other non-space characters as Text if not recognizable as something else, such as a Date, Decimal, Number, or Time value, or an expression keyword or operator.

To ensure AppSheet recognizes a raw textual value in an expression, the raw textual value should be enclosed in double quotes:

• "John Smith"
• "[email protected]"
• "Please choose an option below:"
• "Company Name"

Some words commonly used as column names may be confused with expression keywords. To avoid confusion entirely, best to always double-quote raw textual values:

• "Date"
• "DateTime"
• "Decimal"
• "Number"
• "Text"
• "Time"

To include spaces at the beginning or the end of a raw textual value, or multiple consecutive spaces within, the raw value must be double-quoted:

• " space before"
• "space after "

To spread text across multiple lines (as for a LongText value), the entire multi-line raw value must be double-quoted:

"This value
is spread
across lines."

When in doubt, if you want a raw value treated as a textual value, put double-quotes around it.

Constructing a textual value with concatenation

Two or more textual values can be combined to produce a new textual value using concatenation. The CONCATENATE() function does this:

• CONCATENATE("ABC", "123") gives ABC123.
• CONCATENATE("Hi, ", [First Name], " ", [Last Name], "!") might give Hi, John Smith!

The text-concatenate operator (&) is a short way to concatenate two values:

• ("ABC" & "123") gives ABC123.
• ("Hi, " & [First Name] & " " & [Last Name] & "!") might give Hi, John Smith!

Constructing a textual value with a function

A number of functions produce textual results:

• CONCATENATE() - Text from textual fragments.
• ENCODEURL() - Encoded textual value for use in a URL.
• INITIALS() - First character of each word in a textual value.
• LEFT() - Left-most consecutive characters from a textual value.
• LINKTEXT() - Textual component of hyperlink.
• LOWER() - Textual value with all letters lowercase.
• MID() - Consecutive characters from a textual value.
• RIGHT() - Right-most consecutive characters from a textual value.
• SUBSTITUTE() - Textual value with one fragment replaced by another.
• TEXT() - Textual representation of any value.
• TRIM() - Textual value with excess whitespace removed.
• UNIQUEID() - Random text value suitable for ID.
• UPPER() - Textual value with all letters uppercase.

Constructing a textual value from a non-textual value

In certain places within expressions and in other AppSheet features, textual values may be required but the data you want to use is stored in a column of a non-textual type, such as Decimal or a DateTime. There are several ways to convert non-textual values to a Text:

• TEXT() function: Using a non-textual value with the TEXT() function (e.g. TEXT([My Data])) creates a new textual value from the original value, formatted in a suitable way. The result will be localized, which may affect the appearance of dates, times, numbers, decimals, and currency. If the original value is a column value (e.g., not a raw value or the result of an expression), any column-specific display preferences, such as number of digits, decimal precision, time precision, and date verbosity, will also be applied.
• CONCATENATE() function: Using a non-textual value with the CONCATENATE() function (e.g. CONCATENATE([My Data])) or with the text-concatenate operator (e.g., ([My Data] & "")) creates a new textual value using the exact text of the original value (the raw value, or the value as stored in the spreadsheet or other data store beneath the app table). Note that using this method does not apply column-specific display preferences (such as number of digits, decimal precision, time precision, and date verbosity), nor is the result localized (which may affect the appearance of dates, times, numbers, decimals, and currency).
• Other functions: A number of other functions designed to work with textual values may accept non-textual values and produce reasonable textual results. Using non-textual values directly with functions intended for textual values is not recommended. Instead, consider using TEXT() or CONCATENATE() as described above to convert the non-textual value first.

Use text

Using text expressions to customize messages

The text of many of the messages AppSheet displays to the app user may be customized from the UX > Localize page in the app editor.

By default, custom message text is provided as a raw value, but can be generated with an expression by toggling from the pencil (raw) to the flask (expression):

A common use is to localize messages to the user's preferred language. In an app designed to accommodate multiple languages, there might be a table specifically containing translation text (such as, Translations), where each row is for a specific language (specified by the Language column, for example) and each column is the translation of a specific message. The following expression might then be used to provide the localized text for the Syncing the app message according to the user's preferred language (from the Language user setting):

LOOKUP(
  USERSETTINGS("Language"),
  "Translations",
  "Language",
  "Syncing the app"
)

See also: LOOKUP(), USERSETTINGS()

Use textual values in functions

A variety of functions accept textual input:

• APP() - App (deep-link) from a textual value.
• CONCATENATE() - Text from textual fragments.
• CONTAINS() - Does textual value contain fragment?
• DECIMAL() - Decimal from a textual value.
• ENCODEURL() - Encoded textual value for use in a URL.
• ENDSWITH() - Does text end with fragment?
• EXTRACT() - List of recognizable elements from a textual value.
• EXTRACTCHOICE() - Extract one Yes/No value from a textual value.
• EXTRACTDATES() - List of dates from a textual value.
• EXTRACTDATETIMES() - List of DateTimes from a textual value.
• EXTRACTDOMAINS() - List of email domains from a textual value.
• EXTRACTDURATIONS() - List of durations from a textual value.
• EXTRACTEMAILS() - List of email addresses from a textual value.
• EXTRACTHASHTAGS() - List of hashtags from a textual value.
• EXTRACTMENTIONS() - List of mentions from a textual value.
• EXTRACTNUMBERS() - List of numeric values from a textual value.
• EXTRACTPHONENUMBERS() - List of phone numbers from a textual value.
• EXTRACTPRICES() - List of prices from a textual value.
• EXTRACTTIMES() - List of times from a textual value.
• FIND() - Position of fragment in a textual value.
• INITIALS() - First character of each word in a textual value.
• ISBLANK() - Is value absent?
• ISNOTBLANK() - Is value present?
• LEFT() - Leftmost consecutive characters from a textual value.
• LEN() - Number of characters in a textual value.
• LOWER() - Textual value with all letters lowercase.
• MID() - Consecutive characters from a textual value.
• NUMBER() - Number from textual value.
• RIGHT() - Right-most consecutive characters from a textual value.
• SPLIT() - List from textual value.
• STARTSWITH() - Does text start with fragment?
• SUBSTITUTE() - Textual value with one fragment replaced by another.
• TEXT() - Textual representation of any value.
• TEXT_ICON() - Icon from text.
• TRIM() - Textual value with excess whitespace removed.
• UPPER() - Textual value with all letters uppercase.