There was a question this week in the Salesforce Learners Facebook group asking how to remove text characters from a mixed string of text characters and digits, leaving just the numeric digits.
How I can seperate out only numbers from a TEXT using formula field. Ex- field contain value as A1234CGH, G7654 and i want to separate only numbers like 1234 Or 7654 and update it in another field
At first, I thought I could use a REGEX function to do this but this is not supported in Flow formulas. The next best option was to loop through the characters in the original string, one by one testing if they are text or numeric and only keeping the numerics. The standard Flow loop requires a collection variable so we could either split the string into separate chars and put into a collection variable or do it this way:
Starting with a Record Triggered Flow:
(Note: It could probably be a before save triggered flow but I was concerned about the length of time it would take to loop through all the characters in the string, test and assign them so I opted for the after save trigger.)
For our object, I have used the contact object and I have created two new fields:
- Licence Number String (type = string) – this contains the original value with a mix of letters and digits.
- Licence Number Numeric (type = string) – yes it is a string – containing just the remaining digits.
Note: If you are working in a Summer ’21 environment then you should be able to change the conditions to ISCHANGED (True) to detect when this flow needs to run.
Create a new Variable to hold the value that is currently in Licence Number String on the contact record:
Assign the value from the record to our new variable:
Create a variable to hold the LENgth of the input string:
Create a formula to get the LENgth of the string:
Assign this LENgth value to a variable:
Check the length of the string:
Create a formula to get the 1st character in the string:
Create a new variable to hold this 1st char:
Assign the 1st character to v_char:
Create another formula, this time to test if the char is a number:
Note: The return value is Boolean (True/False)
Check if the character is a number (using our new boolean formula):
Create a new variable to hold our new Licence Number with only numeric digits (LNN):
For YES in our previous decision (Is 1st char a number), add the new char (digit) to our LNN:
Now we remove this 1st char from our input string:
First we create a formula to trim the string from the right, leaving all but the last char (which is the left most char). Effectively this removes the first character from our string.
Remove the 1st char (using our new formula):
Reduce the LENgth counter by 1:
Loop through again and again testing each character and adding the numeric digits to our LNN.
Finally, (when the LENgth = 0) and all characters have been looped through, update the contact record:
The final result looks like this: