Recently, while working on a Power Automate project, I faced a requirement to extract numbers from a string. This came up when dealing with a SharePoint list where some fields contained a mix of text and numbers, and I only needed the numeric values.
After researching and testing different approaches, I found an effective way to achieve this using Power Automate.
In this tutorial, I will show you how to extract a number from a string in Power Automate. This method works whether you are working with order numbers, invoice IDs, or any text field with numbers.
Extract Number from String in Power Automate
You have a SharePoint list named Order Requests with a column called Order Details (Single line of text). This column contains order numbers mixed with text, such as:
- “Order #12345 has been placed.”
- “Your order ID is 98765. Thank you!”
- “Ref: 56789 – Processing”
You need to extract only the numeric order ID from Order Details and store it in a separate Order Number column (Number) for further processing.
Now, to do this, follow the below steps:
1. Create an Automated Cloud Flow using the SharePoint connector’s When an item is created trigger. Select the SharePoint site where your Order Requests list is present, and choose the Order Requests list.
2. Add a Compose action and provide the below expression chunk() converts the string into an array of individual characters.
chunk(triggerBody()?['OrderDetails'],1)
3. Add Select action and provide the below parameters to filter only numbers:
- From: Provide the above compose action outputs.
outputs('Compose')- Map (Switch to Text Mode): Provide the below expression:
if(isInt(item()),item(),null)
This Select action loops through each character, checks if it’s a number, keeps the numbers, and replaces everything else with null.
4. Add Compose and provide the parameters below to join numbers into a single string.
join(body('Select'),'')
The output of Select is an array with numbers and null values. The above join function will join all numbers together into a single string.
5. Then, add the compose action below the expression again to convert the string to an integer.
int(outputs('Compose_1'))
6. Add Update item action to save the extracted number to SharePoint and provide the below parameters:
- Site Address: Select a specific SharePoint site from drop-down.
- List Name: Choose a particular list.
- Id: Select Id from dynamic content under When an item is created.
- Updated Order Number: Provide the output from dynamic content compose 2.
Save the flow and run it manually. Then, go to the SharePoint list and add a new item.
After the flow runs successfully, refresh the SharePoint list. The value will be updated in the Order Number column.
Conclusion
This tutorial covered extracting numbers from a string in Power Automate using a SharePoint list. We split the text into characters, filtered out non-numeric values, joined the numbers, converted them to an integer, and updated the SharePoint list.
- Convert CSV to Array in Power Automate
- Replace Commas With New Lines in Power Automate
- Use the Skip() Function in Power Automate
- Extract String After Character in Power Automate
- Extract Numbers from Email Subjects in Power Automate
After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.
