Crafting Clean Strings: Essential Power Apps Text Functions for Real-World Data

Text data in Power Apps can be unpredictable. Whether it comes from an import, a manual form entry, or an external API, you’ll often need to slice it, clean it, and present it in a consistent way. Built‑in text functions let you do all that without writing complex expressions. This guide walks through a realistic scenario: transforming a raw customer import into polished, usable fields.

We’ll work with a SharePoint list named CustomerImport that contains these columns:

FullName – e.g., "kate bishop"
RawPhone – e.g., "212-555-0179"
Street – e.g., "123 Maple Dr."
Municipality – e.g., "Brooklyn"
Region – e.g., "NY"
PostalCode – e.g., "11201-1234"
Notes – free text with extra spaces

Goal: produce a unified address string, fix name capitalization, strip phone number dashes, and trim extra whitespace.

Splitting Strings with Precision

Extracting parts of a string is one of the most common tasks. The functions Left, Right, and Mid retrieve a fixed number of characters from the start, end, or middle of a string. When the length varies, combine them with Find and Len.

Fixed‑Length Extraction

Imagine you have a product code like "US-2026-001" where each segment has a known size. Left gets the country code, Mid extracts the year, and Right grabs the sequence.

powerfxExtract segments from a product code

Left("US-2026-001", 2)        // "US"
Mid("US-2026-001", 4, 4)       // "2026"
Right("US-2026-001", 3)        // "001"

Dynamic Splitting with Find and Len

Parsing a full name like "kate bishop" into first and last requires finding the space first. Find returns the position of the space, then Left and Right use that position – with a -1 shift and Len for the right side.

powerfxExtract first and last names dynamically

// First name: everything before the first space
Left(FullName, Find(" ", FullName) - 1)

// Last name: everything after the space
Right(FullName, Len(FullName) - Find(" ", FullName))

Position counting

Remember that Find returns a 1‑based index. Subtracting 1 removes the space itself from the Left result.

Cleaning Messy Inputs

Imported data often arrives with inconsistent casing, extra spaces, or unwanted characters. Three functions here become your best friends: Trim (or TrimEnds), Substitute, and the case converters Upper, Lower, and Proper.

Removing Unwanted Whitespace

Power Apps provides two trimming functions with different behaviours:

Trim(text) — removes leading and trailing spaces and collapses multiple internal spaces into a single space. This is the equivalent of Excel’s TRIM function.
TrimEnds(text) — removes leading and trailing spaces only. Internal runs of spaces are left intact.

Use Trim when you want fully normalised spacing:

powerfxTrim collapses internal spaces too

Trim("   Quick   brown fox   ")    // "Quick brown fox"
TrimEnds("   Quick   brown fox   ")  // "Quick   brown fox"  (internal spaces preserved)

Substituting Characters

Phone numbers often come with dashes, dots, or parentheses. Use Substitute to strip them all.

powerfxRemove dashes from phone numbers

Substitute(RawPhone, "-", "")      // "2125550179"

To replace multiple symbols, nest Substitute calls.

powerfxRemove dashes and parentheses

Substitute(Substitute(RawPhone, "-", ""), "(", "")

Normalizing Case

Capitalizing names consistently improves readability. Proper sets the first letter of each word to uppercase. To standardise an email for comparison, use Lower.

powerfxCapitalize a name and standardise an email

Proper("kate bishop")               // "Kate Bishop"

Lower(User().Email) = "admin@example.com"

Formatting Dates and Numbers

The Text function converts a date or number into a string with custom formatting. This is essential when you want to show a date in a specific pattern regardless of the user's device locale.

Custom Date Format

Use Text with a format string to control the output.

powerfxFormat the current date and time

Text(Now(), "dddd, mmmm d, yyyy")    // "Wednesday, June 3, 2026"
Text(Now(), "m/d/yyyy hh:mm:ss AM/PM") // "6/3/2026 10:34:00 AM"

Currency Formatting

For numeric values, add a currency symbol and thousand separators.

powerfxFormat a number as currency

Text(2056.20, "$#,##0.00")            // "$2,056.00"

Delegation caution

Most text functions – Left, Right, Mid, Find, Substitute – are not delegable. They operate locally on the data already retrieved. If you filter on a transformed string, the filter may run client‑side after the full dataset arrives. For large data sources, design your queries so the heavy work happens on the server.

Joining Data Seamlessly

Building a full address line from separate fields is a perfect use case for the & operator or Concatenate. You can also insert special characters like line breaks or tabs using Char.

Address Assembly

powerfxCombine address components into one line

// Using & (preferred)
Street & ", " & Municipality & ", " & Region & "  " & PostalCode

// Using Concatenate
Concatenate(Street, ", ", Municipality, ", ", Region, "  ", PostalCode)

Inserting Special Characters

Need a line break inside a label? Char(10) adds a newline. A hard tab is Char(9). Quotation marks? Char(34).

powerfxAdd a line break and a tab

"First Line" & Char(10) & "Second Line"   // Two lines
"Column1" & Char(9) & "Column2"            // Tab separated

Going Further: Encoding and Replace

Two less‑used but valuable functions are PlainText and EncodeUrl. PlainText strips HTML tags from a string – handy when you import content from a rich‑text source. EncodeUrl converts a string to a valid URL‑encoded format.

The Replace function is an alternative to Substitute when you need to swap a specific segment by position, not by matching text.

powerfxUse Replace for positional swaps

Replace("ABC-123", 5, 3, "456")     // "ABC-456"

Common Mistakes and Troubleshooting

Forgetting the -1 in Left(FullName, Find(" ", FullName)). This includes the space. Always subtract 1.
Mixing up Trim and TrimEnds. Trim collapses internal whitespace; TrimEnds only strips the ends. Use Trim when you want fully normalised output (equivalent to Excel’s TRIM). Use TrimEnds when the internal spacing is meaningful and must be preserved.
Relying on Proper for names with apostrophes. Proper("o'brien") returns "O'Brien" which is usually correct, but test with your data.
Hard‑coding locale‑sensitive formats. Instead of a fixed date format, consider using Text with a language tag like "es" for Spanish.
Nesting too many Substitute calls. For complex replacements, a one‑line expression can become unreadable. Break it into multiple Set variables or use a helper collection.

Final Recommendation

Create a small “utilities” screen or a set of global variables with cleaned versions of your data. This centralises logic and makes it easier to adjust formatting rules later. Use the With function to avoid repeating long text transformation expressions.

Example:

powerfxCentralised cleaning with With

With(
  {
      CleanedName: Proper(Trim(FullName)),
      CleanedPhone: Substitute(RawPhone, "-", "")
  },
  Patch(Customers, Defaults(Customers), {
      DisplayName: CleanedName,
      PhoneNumber: CleanedPhone
  })
)

Power Apps text functions are small but powerful. By combining them thoughtfully, you can turn raw, messy strings into data that looks clean and professional—no extra code required.

References

Original article by Matthew Devaney: Power Apps Text Functions (With Examples)
Microsoft Learn — Text function in Power Fx
Microsoft Learn — String functions overview (Left, Right, Mid, Find, Len, Substitute)
Microsoft Learn — Trim and TrimEnds functions