From Raw Strings to Structured Data: A Guide to Power Automate Text Functions

Every integration engineer eventually faces the messy string. A vendor sends an email instead of an API call. A legacy system outputs a flat file. Your beautifully structured data model meets raw text.

How do you tame it? Power Automate’s text functions are your complete toolkit for this job. This article skips the dry function catalog and instead walks you through a realistic parsing pipeline. By the end, you’ll know exactly how split, substring, replace, formatNumber, and their friends work together under pressure.

The Scenario: Parsing a Raw Order

Let’s assume an email arrives at Northwind Traders every time a customer places an order. The body is a semi-structured block with no JSON wrapper:

<start>
ref: ORD-9981
customer: sarah@example.net
line_items: SKU-A (4), SKU-B (1)
amount_due: 274.99
<end>

Our flow must extract OrderRef, CustomerEmail, LineItemsString, AmountDue, and a unique ProcessGUID, then write them to a data source.

1. Normalise the Input

Email text is notoriously inconsistent. Line endings can be \r\n or \n, and the sender might mix cases. Our first action cleans everything up.

replace(trim(toLower(outputs('RawEmailBody'))), '
', '
')

This single expression:

• Strips leading and trailing whitespace (trim).
• Forces all characters to lower case (toLower), making case-insensitive searching reliable.
• Standardises line endings (replace), so the next split works every time.

2. Break the Text Into Manageable Lines

Now we can split the entire body into an array. Each line becomes an element.

split(outputs('NormalizedBody'), '
')

It’s good practice to confirm the array length before indexing.

length(outputs('LinesArray'))

If LineCount is less than the expected number of lines, something went wrong upstream.

3. Extract the Order Reference

Line 2 of the array (index 1) contains ref: ord-9981. We can use a combination of split and trim to isolate the value.

trim(last(split(outputs('LinesArray')?[1], ': ')))

This splits "ref: ord-9981" at the colon-space, giving ["ref", "ord-9981"]. last picks the second element, and trim removes any accidental whitespace.

Alternative approach – if the value is always a fixed length:

substring(outputs('LinesArray')?[1], indexOf(outputs('LinesArray')?[1], ': ') + 2, 7)

Here substring works with a known length, while indexOf dynamically finds the start. Both are valid—choose the one that best handles your data variability.

4. Validate the Record Boundaries

Before extracting more data, check the structure matches what we expect. The text should begin with <start> and end with <end>.

and(startsWith(outputs('LinesArray')?[0], '<start>'), endsWith(last(outputs('LinesArray')), '<end>'))

startsWith, endsWith, and last together form a simple but effective guard clause.

5. Extract and Validate the Customer Email

The third line (index 2) is customer: sarah@example.net.

trim(last(split(outputs('LinesArray')?[2], ': ')))

Email addresses are a great place to practice endsWith for domain validation.

endsWith(outputs('CustomerEmail'), '.net')

6. Extract the Line Items

The line items are on a single line: line_items: sku-a (4), sku-b (1). We can keep it as a comma-separated string or convert it to an array.

split(trim(slice(outputs('LinesArray')?[3], indexOf(outputs('LinesArray')?[3], ': ') + 2)), ', ')

This uses slice (which takes a start index and goes to the end) and split to produce an array like ["sku-a (4)", "sku-b (1)"]. If you just need the raw string, skip the split.

7. Extract, Validate, and Format the Amount

Line 5 (index 4) contains amount_due: 274.99. Numeric values from text must be validated before they are used in calculations.

trim(last(split(outputs('LinesArray')?[4], ': ')))

isFloat(outputs('RawAmount'))

if(equals(outputs('IsAmountValid'), true), formatNumber(float(outputs('RawAmount')), '$#,##0.00', 'en-us'), '0.00')

isFloat prevents a runtime error if the sender typed unknown instead of a number. formatNumber converts the raw float to a display-ready currency string.

8. Generate a Unique Identifier

Every record needs a primary key. The guid() function supplies one instantly.

guid()

For compatibility with databases that require no dashes, use replace to strip them from the standard output:

replace(guid(), '-', '')

To additionally force uppercase: toUpper(replace(guid(), '-', '')). Note: guid() in Power Automate does not accept a format parameter — the ‘N’ format string is a .NET convention that is not available in workflow expression language.

9. (Bonus) Breaking Data Into Chunks

If the order has a very long item description, the chunk function can split it into equal-sized pieces for logging or processing limits.

chunk(outputs('LinesArray')?[3], 15)

This returns an array of 15-character substrings. It is especially useful when feeding data into legacy systems that accept fixed-width fields.

10. Assemble the Final Output

Finally, combine everything into a single JSON object or a formatted message.

concat('Order ', outputs('OrderRef'), ' from ', outputs('CustomerEmail'), ' — due ', outputs('FormattedAmount'))

Or, for structured storage:

{
"order_ref": @{outputs('OrderRef')},
"email": "@{outputs('CustomerEmail')}",
"items": "@{outputs('LineItemsArray')}",
"total_value": @{outputs('FormattedAmount')},
"source_guid": "@{outputs('ProcessGUID')}",
"raw_length": @{length(outputs('RawEmailBody'))}
}

Common Mistakes & Troubleshooting

• indexOf does not accept a start index. Unlike many programming languages, Power Automate’s indexOf and lastIndexOf search the entire string from the beginning. To search within a window, first use slice to create a subset.
• Empty array from split. When the delimiter is missing, split returns a single-element array containing the entire original string. Always check length(array) before indexing [1].
• Substring errors. If startIndex + length exceeds the string length, the expression fails. Use slice when you are unsure of the end point—it safely returns everything available.
• Null input. A text field from an HTTP trigger might be null. Wrap the input in coalesce(text, '') to prevent a crash when calling any text function.

Performance & Security Notes

• Expression size limits. A single Power Automate expression can consume a maximum of 8,192 characters (in some limits, the total definition is capped at around 100MB). When processing very large text blocks, split the work across multiple Compose actions rather than nesting everything into one monster expression.
• Masking sensitive data. If your flow logs raw email content, use replace to redact passwords or personal information before storing the variable.

Final Recommendation

Don’t treat text functions as isolated tools. Build a small library of reusable Compose actions—one for normalising input, one for splitting, one for validating—and chain them together. This transforms parsing from a fragile copy-paste exercise into a confident, testable layer of your automation.

References

• Original source inspiration: All Power Automate Text Functions (With Examples) by Matthew Devaney.
• Microsoft Learn: Workflow expression language functions reference — the authoritative list of split, substring, trim, replace, formatNumber, guid, and all other expression functions available in Power Automate.