Extracts a string of characters from text, starting from the left.
For example, if a product SKU is prefixed with a code of fixed length, you can use the LEFT function to extract this from the rest of the text.
Syntax
LEFT(Text [, Number of characters])
Arguments
| Argument | Data type | Description |
| Text | Text Can be a line item, text constant, or general expression. | The text to extract characters from. |
| Number of characters (optional) | Number Can be a line item, property, or expression. | The number of characters to extract from the string. If you use a negative number or zero for this argument, the LEFT function returns a blank result. If this argument is greater than the number of characters in the Text argument, the LEFT function returns only the characters in the string. |
The LEFT function returns a text-formatted result.
Note: If you omit the Number of characters argument, LEFT returns only the first character of the text string.
Calculation engine functionality differences
In Polaris, the LEFT function behaves as expected with all text values, including composite characters or characters from outside the Basic Multilingual Plane (BMP), the standard range of characters that covers most modern languages.
In the Classic Engine, the LEFT function considers the length of:
- Unicode characters from within the BMP to be 1
- Unicode characters from outside the BMP to be 2
- Composite characters (such as those with accents or diacritic marks) to be equal to the number of components
Syntax example
LEFT(Product SKU, 3)
This formula returns the first three characters of each Product SKU, identifying the product category.
Excel equivalent
Examples
Extract characters from text line items
In this example, a Clothing list displays on columns. It contains four items, Hats, Shirts, Shorts, and Trousers.
Three text-formatted line items display on rows, Red Product, Blue Product, and Yellow Product. These contain product names. There is also one list-formatted line item, Clothing List, which contains product types. These line items contain the values used for the Text argument.
The bottom four rows contain formulas that use the LEFT function to extract characters from the left of the text strings.
| Hats | Shirts | Shorts | Trousers | |
| Red Product | Red Hats | Red Shirts | Red Shorts | Red Trousers |
| Blue Product | Blue Hats | Blue Shirts | Blue Shorts | Blue Trousers |
| Yellow Product | Yellow Hats | Yellow Shirts | Yellow Shorts | Yellow Trousers |
| Clothing List | Hats | Shirts | Shorts | Trousers |
LEFT(Red Product, 3) | Red | Red | Red | Red |
LEFT(Blue Product, 7) | Blue Ha | Blue Sh | Blue Sh | Blue Tr |
LEFT(Yellow Product, 10) | Yellow Hat | Yellow Shi | Yellow Sho | Yellow Tro |
LEFT(NAME(Clothing List), 3) | Hat | Shi | Sho | Tro |
Use LEFT with items from list dimension
You can use the ITEM function to return the list-formatted column names. Then, you can use the NAME function to convert the names to be text-formatted, which is compatible with the LEFT function.
In this example, the Products list displays on columns, and two line items display on rows. Both line items contain formulas.
| Apples | Peaches | Bananas | Pears | Carrots | Cucumbers | Lettuce | |
ITEM(Products) | Apples | Peaches | Bananas | Pears | Carrots | Cucumbers | Lettuce |
LEFT(NAME(ITEM(Products)), 5) | Apple | Peach | Banan | Pears | Carro | Cucum | Lettu |
In this example, 5 is used for the the Number of characters argument, so the formula returns the first five letters of the list-formatted Products strings.
Example with text entered directly into formula
LEFT("Favorite Team", 8)
This example uses the LEFT function with a text string typed directly into the formula. It uses 8 for the Number of characters argument. This means it returns Favorite, the first eight characters of the text string.
