Clean Up Strings in Power Automate: Remove Special Characters

Data from various sources like forms, APIs, SharePoint, Excel, and external systems often contains special characters. These characters can cause significant issues when processing records, generating filenames, creating folders, or integrating with other systems. For instance, names like 'John@Doe#123!', invoice references like 'Invoice/2025:001', or project names like 'Project*Name?' can break automation workflows.

Many organizations need to remove or replace these special characters before storing or processing data. Power Automate offers efficient ways to clean and transform strings, ensuring smoother data handling and more reliable automation.

Why Remove Special Characters?

Removing special characters is crucial for several reasons:

Generate Valid Filenames: Many operating systems and cloud storage solutions have restrictions on characters allowed in filenames.
Create Valid Folder Names: Similar to filenames, folder names have character limitations.
Standardize Data: Ensuring data consistency across different systems and fields.
Improve Integrations: Preventing errors when data is passed between systems with different character handling capabilities.
Prevent System Errors: Special characters can sometimes be misinterpreted by systems, leading to unexpected behavior or failures.
Clean Imported Records: Ensuring data imported from external sources is clean and usable.

Common Scenarios

Special characters frequently appear in various contexts:

SharePoint Documents: Characters like *, :, ", <, >, ?, |, /, \ are invalid in SharePoint filenames and paths.
Dataverse Records: Cleaning custom names or other text fields to ensure data integrity.
Email Subjects: Creating valid and consistent document references or subject lines.
Excel Imports: Normalizing data before processing to avoid errors in Power Automate.

Understanding the Replace Function

Power Automate provides the replace() function, a powerful tool for string manipulation. Its syntax is straightforward:

replace(text, oldValue, newValue)

Where:

text is the string you want to modify.
oldValue is the character or string you want to find.
newValue is the character or string you want to replace it with.

Example: To remove the '@' symbol from 'John@Doe':

replace('John@Doe', '@', '')

Result: 'JohnDoe'. The special character is effectively removed by replacing it with an empty string.

Replacing Multiple Characters

When dealing with strings containing multiple special characters, you can chain replace() functions together. For example, to clean 'John@Doe#123!':

replace(
  replace(
    replace('John@Doe#123!', '@', ''),
    '#', ''
  ),
  '!', ''
)

Result: 'JohnDoe123'.

Example: Cleaning SharePoint File Names

Imagine you have an input string like 'Project:Design/2025?' and you need to create a valid SharePoint filename. You can replace invalid characters with hyphens or remove them entirely.

Input: 'Project:Design/2025?'

Expression:

replace(
  replace(
    replace(outputs('Compose'), ':', '-'),
    '/', '-'
  ),
  '?', ''
)

Output: 'Project-Design-2025'. This approach is commonly used before creating SharePoint folders and documents.

Using an Array of Special Characters

For a large number of characters to remove, chaining individual replace() functions can become cumbersome. A more scalable approach is to use an array and an 'Apply to each' control.

Initialize Variable: Create a variable (e.g., 'CleanText') initialized with your input string and another variable (e.g., 'SpecialChars') as an array containing all the special characters you want to remove:
```
[
      "@", "#", "$", "%", "&", "*", "!", "?", "/", "\", ":"
    ]
```
Apply to Each: Loop through the 'SpecialChars' array. Inside the loop, use the replace() function to replace the current item from the array (items('Apply_to_each')) with an empty string, updating the 'CleanText' variable in each iteration.
```
replace(variables('CleanText'), items('Apply_to_each'), '')
    
```

This method automatically removes all configured special characters from the string.

Example Flow: Invoice Cleaning

Input: 'Invoice#2025/001?'

Processing: Remove '#', '/', '?' using the array method.

Output: 'Invoice2025001'.

Creating Safe Folder Names

When user input is used to create folder names, special characters can cause failures. For instance, if a user enters 'Customer:ABC/XYZ?', you need to sanitize it before creating a SharePoint folder.

Using: replace(triggerBody()?['FolderNameInput'], ':', '') and then replace(outputs('Compose'), '/', '') and finally replace(outputs('Compose_2'), '?', '').

Result: 'CustomerABCXYZ'. This prevents folder creation failures.

Using Compose Action

The Compose action is excellent for simplifying complex expressions and making them easier to maintain and debug. You can use it to apply a single replace() function or a chain of them.

Example:

Compose
replace(triggerBody()?['Title'], '@', '')

Store the result in a variable or use it directly for further processing.

Real-World Example: Employee Onboarding

During employee onboarding, users might enter their email address like 'John.Doe@Company.com'. To generate consistent folder names, user IDs, or document references, you might want to clean this input.

Desired Output: 'JohnDoeCompanycom'.

This ensures consistent naming conventions across your systems, regardless of the input format.

Advanced Scenario: Generate Clean Document IDs

For generating unique and clean document identifiers, special characters must be removed.

Input: 'DOC#2025/001?'

Expression using chained replace():

replace(
  replace(
    replace('DOC#2025/001?', '#', ''),
    '/', ''
  ),
  '?', ''
)

Output: 'DOC2025001'. This provides a clean document reference number suitable for databases or file systems.

Best Practices

Use Variables: Store intermediate results in variables, especially when performing multiple transformations. This makes your flow easier to read and debug.
Use Compose Actions: As mentioned, Compose actions simplify complex expressions and aid in debugging by showing the output of each step.
Handle Null Values: Ensure your expressions can handle null or empty inputs gracefully. Use functions like coalesce(triggerBody()?['Title'], '') to provide a default empty string if the input is null.
Remove Only Necessary Characters: Be mindful of which characters you remove. Removing characters that are part of meaningful data (e.g., a hyphen in a product code) can lead to data loss or confusion.
Test Multiple Input Types: Validate your solution with various inputs, including edge cases, emails, filenames, folder names, and customer names.
Standardize Output: Consider converting the cleaned string to a consistent case using toLower() or toUpper() if needed for further processing or comparison.

Benefits of Cleaning Special Characters

Cleaner Data: Automatically remove unwanted characters, improving data quality.
Better Integrations: Prevent API and system errors caused by invalid characters.
Improved File Management: Generate valid and consistent file and folder names.
Faster Processing: Automated data cleansing reduces manual effort and speeds up workflows.
Better User Experience: Reduce the need for manual corrections by users.

Common Use Cases

SharePoint Document Libraries: Clean document names before upload to avoid errors.
Dataverse Imports: Normalize customer data or other records before importing.
CRM Integrations: Prepare records for external systems that have stricter data formatting requirements.
Email Processing: Create clean reference IDs from email subjects or bodies.
Reporting Systems: Generate consistent and valid names for reports.

Conclusion

Removing special characters is a fundamental data-cleaning requirement in Power Automate. By effectively utilizing the replace() function, arrays, loops, variables, and Compose actions, organizations can automate string transformations, significantly improve data quality, and prevent errors across SharePoint, Dataverse, Microsoft 365, and external integrations.

Power Automate provides a simple yet powerful way to standardize text, ensuring consistent and reliable automation processes.