Power Apps Text Functions (With Examples)
Overview:
The world of Power Apps offers a diverse range of text functions to enhance app-building experiences. These functions, though often underestimated, offer powerful tools for manipulating and formatting text data, enabling developers and businesses to handle data more efficiently. The blog post delves into a variety of functions, such as LEFT, RIGHT, MID, FIND, LEN, SUBSTITUTE, text case functions like UPPER, LOWER, and PROPER, CONCATENATE, and TEXT. Each function’s utility is explained with practical examples, illustrating how they can be leveraged for diverse scenarios in Power Apps projects.
LEFT, RIGHT, MID Functions for Text Manipulation
In Power Apps, you can employ the Left, Right, and Mid functions to extract specific segments from a text string with ease
- Left(String, NumberOfCharacters) extracts characters from the left side of the text.
- Right(String, NumberOfCharacters) extracts characters from the right side of the text.
- Mid(String, StartingPosition, NumberOfCharacters) retrieves characters from a specified starting position.
Why You Should Use These Functions ?
Companies frequently use account codes to organize their records. These codes often consist of multiple parts, such as State (e.g., NY), Year (e.g., 2021), and Sequence Number (e.g., 003). The Left, Right, and Mid functions are essential tools for extracting and manipulating these parts efficiently.
Without Function:
I can get each segment of the Code like this!
FIND Function for Text Positioning
In Power Apps, the Find function serves as a valuable tool for determining the position of one text string within another.
- Find(FindString, WithinString [, StartingPosition]) pinpoints the location of a specific text string within another text.
Why You Should Use This Function:
Consider a scenario where you have a table of employee names, and you need to extract just the first names. The challenge is that each name varies in length, making it unsuitable for the Left or Right functions. In such cases, the Find function becomes your go-to choice to accurately locate and extract the desired text portion.
Without Find Function
With Find Function
// Use the Left function to extract text from
ThisItem.Employees
// The length to extract is determined by finding the position of the space (" ") and subtracting 1 to exclude the space itself
Left(
ThisItem.Employees, // The source text is ThisItem.Employees
Find(" ", ThisItem.Employees) - 1 // Calculate the length based on the position of the space (excluding the space)
)
LEN Function for Text Length
In Power Apps, the Len function is your tool for finding out the length of a text string.
- Len(String) provides you with the length of a given text string.
Why You Should Use This Function:
Imagine you need to extract the last name from a list of employee names. To achieve this, you can calculate the length of the full name, subtract the position of the space character, and then use the Right function to isolate the desired portion. The Len function becomes invaluable in determining the length of the text string, enabling you to perform these operations accurately.
// Use the Right function to extract text from
ThisItem.Employees
// The length of the text to extract is determined by subtracting the position of the space (" ") from the total length of ThisItem.Employees
Right(
ThisItem.Employees, // The source text is ThisItem.Employees
Len(ThisItem.Employees) - Find(" ", ThisItem.Employees) // Calculate the length based on the position of the space
)
SUBSTITUTE Function for Text Replacement
In Power Apps, the Substitute function is a handy tool for swapping specific sections of a text string with another value.
- Substitute(String, OldString, NewString [, InstanceNumber]) allows you to replace occurrences of OldString with NewString within the given text String.
Why You Should Use This Function:
Consider the scenario where phone numbers are stored in various formats. Some may have dashes separating number blocks. In such cases, the Substitute function becomes invaluable. It enables you to replace these dashes or other specific elements with your desired format, providing consistency and clarity in your data.
Without Find Function
With Find Function
// Use the Substitute function to replace dashes ("-") with an empty string ("") in
ThisItem.'Phone Number'
Substitute(
ThisItem.'Phone Number', // The source text is ThisItem.'Phone Number'
"-", // The OldString to find and replace
"" // The NewString, which is an empty string to remove the dashes
)
Text Case Functions: UPPER, LOWER, and PROPER
In Power Apps, you have three handy functions for manipulating the case of text strings:
Lower(String) converts all characters in a text string to lowercase.
Upper(String) changes all characters in a text string to uppercase.
Proper(String) capitalizes the first letter of each word in a text string.
Why Use These Functions:
These functions are incredibly useful for ensuring consistency and readability in your text data. Whether you want text in all uppercase, all lowercase, or properly capitalized, these functions make it easy to achieve the desired text case format.
Upper Function
// Use the Upper function to convert all characters in
ThisItem.Employees to uppercase
Upper(
ThisItem.Employees // The source text is ThisItem.Employees
)
Lower Function
// Use the Lower function to convert all characters in
ThisItem.Employees to lowercase
Lower(
ThisItem.Employees // The source text is ThisItem.Employees
)
Proper Function
// Use the Proper function to capitalize the first letter of each word in the given text string
Proper(
"power APP Developers" // The source text is "power APP Developers"
)
Combine Text with CONCATENATE
In Power Apps, the CONCATENATE function is your go-to tool for combining multiple text strings into one cohesive string.
- Concatenate(String1 [, String2, …]) lets you join together various text strings, creating a single, merged string.
Why Utilize This Function:
When dealing with customer address information or other data stored in different columns within a data source (e.g., SharePoint, Dataverse), the CONCATENATE function proves invaluable. It enables you to unify these separated text components into a complete and easily digestible format.
// Use the Concatenate function to combine multiple text strings, including spaces, from ThisItem fields
Concatenate(
ThisItem.Employees, // Combine the 'Employees' field from ThisItem
" ", // Add two spaces for separation
ThisItem.Code, // Combine the 'Code' field from ThisItem
" ", // Add three spaces for separation
ThisItem.'Phone Number' // Combine the 'Phone Number' field from ThisItem
)
Format Text with TEXT
In Power Apps, the TEXT function plays an important role in converting the numerical or datetime values into text format and also allows you to customize the formatting.
- Text(NumberOrDateTime, CustomFormat [, ResultLanguageTag ]) empowers you to transform numbers or DateTime data into formatted text, with the option to specify a custom format and result language.
// Get the current date and time in a short format.
Text(Now(),DateTimeFormat.ShortDateTime)
// Result: 10/2/2023 12:20 AM
// Get the current date and time in a custom format.
Text(Now(), "m/d/yyyy hh:mm:ss AM/PM")
// Result: 10/2/2023 12:22:07 AM
Another way I use often is to format a decimal number as a currency.
// Use the Text function to convert the number 40000.20 into a formatted text with a currency symbol and two decimal places.
Text(
40000.20, // The number to be formatted
"$#,##0.00" // The custom format: $ sign, comma as a thousand separator, and two decimal places
)
Conclusion:
Power Apps text functions are indispensable tools for app developers, offering versatility in handling and formatting text data. From extracting specific portions of text strings, ensuring text consistency, to formatting data for better readability, these functions cater to a broad spectrum of text manipulation needs. By mastering these functions, developers can optimize text handling in their Power Apps projects, ensuring efficient and streamlined processes. Whether you’re a seasoned developer or just starting with Power Apps, integrating these functions into your toolbox will undoubtedly elevate your app-building capabilities.
