Insert a semi-colon every ten numbers
In this tutorial blog I will not only show you how to insert a semi-colon every ten numbers within a string, I will also explain in great detail my thought process and the potential issues we could face even with such a simple request.
This blog post is essentially an explanatory course in various Power Apps functions and their applications within Power Fx code (which is most-frequently used in Canvas Power Apps).
(EDIT: UPDATE, due to changes to how Distinct and Split output in the newest editor versions, these formulas have been updated – read more here)
Introduction
Thank you to DAVIDPOWELL who was the inspiration for this post. The query starts with this:
I have a textbox.
DAVIDPOWELL (https://powerusers.microsoft.com/t5/user/viewprofilepage/user-id/89300)
If someone copys and paste numbers into this textbox i need to insert a Semi colon after every 10th digit
i.e.
1421836968 1426031888 1426333858 1426110561 1426419152 1426316934 1426313930 1421800000 1456473342
becomes
1421836968;1426031888;1426333858;1426110561;1426419152;1426316934;1426313930;1421800000;1456473342
Thanks
fordraiders
First Thoughts
Initially when you look at this, you might think the solution is really simple. It looks like we just need to replace the ‘space’ character with a semi-colon.
We can do this by using the Substitute function in the Default property:
Substitute(Self.Text, " ", ";")However, if you try this, you will see that this cannot be done directly as it creates a circular reference – i.e. if you update the text, the Self.Text changes, which it then changes to use the Substitute value, but then that changes the Text value so it substitutes again… this endless circular loop is what defines a circular reference – you will come across this often if you are manipulating strings of text or referencing multiple other controls between each other.
So how do we solve this issue?
You can probably think of two basic routes we can take here, but effectively they are the same process of abstracting the value outside of the original Default property:
- Create a second textbox, have it contain the Substitute function, then make the Default property of the first TextInput be the Text value of the second input
- Create a variable on the TextInput’s OnChange event property, then reference this variable when doing our Substitute function
Let’s take a look at the first approach.
We create two Textboxes, then we set the Second to Substitute values from the first:
Then we set the YourFirstTextInput‘s Default value to reference YourSecondTextInput‘s Text value:
Oh no! We’ve run into the same circular reference issue! This is a good learning moment for those of you that have not seen this before – we may have created a second Text Input, but the circular flow of the data is still happening. We add a value to YourTextInput, YourSecondTextInput then substitutes the spaces within it, then YourTextInput‘s Default changes because there’s now a value in YourSecondTextInput‘s Text Property, which changes the Text value of YourTextInput, which then causes YourSecondTextInput to read the change… and so it goes infinitely, which is why it then becomes a circular loop that never ends.
So what have we found? We can actually only take one route with this, and its a pattern I suggest you learn now as abstracting values to variables can become immensely useful throughout your Power Apps career.
Create a second textbox, have it contain the Substitute function, then make the Default property of the first TextInput be the Text value of the second input- Create a variable on the TextInput’s OnChange event property, then reference this variable when doing our Substitute function
To do this, we no longer need our second Text Input, we only work with YourTextInput.
We set the Substitute function we created as a variable during the OnChange event property of YourTextInput to:
Set(gblOriginalTextValue, Substitute(Self.Text, " ", ";"))Then we set the Default property of YourTextInput to the variable we are creating whenever it changes:
gblOriginalTextValueThis gives us a Textbox that every time it changes, it updates its own value by substituting the values within it.
The next problem
Having done this, we would assume this is resolved? But we then receive further details, letting us know that the text input didn’t replace all of the characters, and that the text input box is actually a multi-line text with the example showing numbers on multiple lines and with multiple spaces between characters.
As some of you may have rightly guessed, since this is a Text Input control set to ‘Text’ rather than ‘Number’ (as we cannot have the ; symbol in a number-only field), there can be any number of issues regarding the text that is inserted by the users.
We need to think carefully ‘like a user’ and try to predict some of the things a user might do when using this Text Input.
Some examples of this are:
User types more than one space between characters:
4718263902 4172839568
Which then using our substitute formula would become:
4718263902;;;;4172839568
User types a semi-colon-spaced list, thinking they are helping:
4718263902 ; 4172839568
Which then using our Substitute formula would become:
4718263902;;;4172839568
User types the orders in multiple lines (using line feed or carriage return [Shift+Enter or Enter])
4718263902 4172839568 5172846287 1234567890
Which then using out Substitute formula would become:
4718263902;4172839568 5172846287 1234567890
User types notes on each order line as comments
4718263902 Nina's order for Thursday 4172839568 Claire's order for Monday 5172846287 Charlotte's order for Tuesday 1234567890 Amy's order for Wednesday
Which then using our Substitute formula would become:
4718263902;Nina's;order;for;Thursday 4172839568;Claire's;order;for;Monday 5172846287;Charlotte's;order;for;Tuesday 1234567890;Amy's;order;for;Wednesday
User types other characters like brackets or special characters
[4718263902 & 4172839568 & 5172846287] & ( 1234567890 )
Which then using our Substitute formula would become:
[4718263902;&;4172839568; &;5172846287]; &;(;1234567890;)
Thoughts on how to approach this
Ok, so we know we have all of these potential issues that we will have to mitigate in order to prevent the user from breaking the system and causing our data to become invalid (as we need ten numbers, split by a semi-colon).
This is a crazy number of things to mitigate, so its ok if you’re feeling like this is way too much and you don’t know where to start.
The first thing you will likely think about, is using Substitute again, substituting other characters with ‘nothing’, to essentially remove them.
Using Substitute
Let’s start down that path of using Substitute for the time being, using the last example I gave above.
So we want to remove the left-square-brackets, to do that, we can wrap the existing Substitute function we used in another Substitute:
Set(gblOriginalTextValue,
Substitute(
Substitute(YourTextInput.Text,
" ", ";"
),
"[", ""
)
)Okayyy, cool, cool. That’s working, and we end up with an output that looks like this:
4718263902;&;4172839568; &;5172846287]; &;(;1234567890;)
Ok, so now we want to remove any line feed or carriage return characters (Enter and Shift-Enter), but how do we do that?
This is where we can look at the Char function.
The Char function will return the character defined by its number within the ASCII table – in our case we are looking to replace the characters represented by decimal numbers 10 and 13 (line feed and carriage return)
To do this, we use Substitute again, wrapping the existing item with a check for Char(10) and then wrapping that again with another Substitute for Char(13)
Set(
gblOriginalTextValue,
Substitute(
Substitute(
Substitute(
Substitute(
YourTextInput.Text,
" ",
";"
),
"[",
""
),
Char(10),
""
),
Char(13),
""
)
)Excellent, this now gives us an output that looks like the following:
4718263902;&;4172839568;&;5172846287];&;(;1234567890;)
Ok, so now we do the same, wrapping Substitute again and again and again for each of the special characters in the example text:
Set(
gblOriginalTextValue,
Substitute(
Substitute(
Substitute(
Substitute(
Substitute(
Substitute(
Substitute(
Substitute(
YourTextInput.Text,
" ",
";"
),
"[",
""
),
Char(10),
""
),
Char(13),
""
),
"]",
""
),
"(",
""
),
")",
""
),
"&",
""
)
)This gives us a result that is close enough, but would still need more Substitute wraps to replace ;;; with ; and ;; with ; and we’d have to do that for any number of potential ;;;;;;;;;;
4718263902;;4172839568;;5172846287;;;1234567890;
…I’m sure you can see why this is going to be a problem. This massive code block above is just to replace 8 characters that we don’t want users to use, but the entire list of characters that people could use encompasses not only the 255 characters in the ASCII table of characters, but now also the Unicode Table of characters which has 25000+ characters.
Even if we just accounted for the ASCII table characters, that would mean we’d be doing 245 Substitute wraps (leaving only 10 characters: the numbers 0123456789), before we started Substituting for the duplicated semi-colon characters.
There must be a better way!
Let’s think about this.
Instead of trying to create exceptions for every character, why don’t we try remove ANY character that is not a number?
So how do we do this? We start by breaking the task into small digestible pieces.
We want to evaluate each character within the string of text within the Text Input control, so we know we want to use the ForAll function, which will do something (whatever we tell it to), For All rows in a table/collection.
Creating a Table from a Text string
Looking at the ForAll function, it takes in a table or collection of values. If we try use this against YourTextInput.Text it will throw an error, as the function is looking for a input that is a table of records, not the ‘single string of text’ we have given it.
So, to start debugging (and so that we don’t have to keep pasting in the text), let’s move this logic to a button.
In our App, create a new Text Input (so that it doesn’t have any code and won’t overwrite itself with formatted values).
Insert our example text that we’ve been using as the ‘text to clean up’.
Then create a button:
Ok, now we know we want the Text in YourTextInput to become a table of values that we can evaluate individually.
We can do this, by using the Split function, and instead of splitting by a particular character (as is normally the case), we are going to split by nothing or “”
This will effectively split the string at every character, regardless of what that character is.
So let’s set our button’s code to do that:
Set(gblOriginalTextValue, Split(YourTextInput.Text, ""))Now click the button.
We can see that the Split action has happened, and that it has created a table of values:
Now we know from earlier that we can use the ForAll to do something to every row within a Table, and what we want to do to every row, is to evaluate whether it is a number or not, and then remove everything that is not a number.
In this case, we will want to use the IsMatch function, which matches one piece of text (any length) to a piece of text that you provide it. In our case we will be comparing just one character, rather than a whole string, but it is useful to know for other potential applications of the function that you could check for a match against whole words if you wanted to.
IsMatch can also match against different types, allowing you to match a piece of text with, for example, an email format (useful for validation within your Apps!)
Anyway, back to our problem at hand:
We want to, For All items in the table of characters, only match those items that are numbers, and when it does match a number, keep that item, discarding all other characters. (we will deal with the inserting of the semi-colon every tenth character later)
We can do this in two ways, I am going to show both, as one is easier, but the other allows you to do so much more if you are willing to use regex101 or regexr to understand regex a bit more
So the easier way, is to use the default Matching option of Match.Digit – this will check if the character we are looking at is a digit, then we will store/use it, creating a new table of only digits in order from first to last:
Set(
gblOriginalTextValue,
ForAll(
Split(YourTextInput.Text,""),
If(
//If this item matches a digit
IsMatch(
Value,
Match.Digit
),
//Then use that value
Value
)
)
)Then press the button.
You can now see if you select your variable in the formula bar (you could also look in the menu, under variables), that we have a table, consisting of many rows, and that only the rows with numbers in them have values.
Now this is good, but it could be better, so instead of using a ForAll here, we can use the Concat function.
Why? The Concat function will, for all items passed into it, return the result of the formula (for each item) that you put in the second parameter of the function, so our code from above becomes:
Set(
gblOriginalTextValue,
Concat(
Split(YourTextInput.Text,""),
If(
//If this item matches a digit
IsMatch(
Value,
Match.Digit
),
//Then use that value
Value
)
)
)Then press the button.
Now you’ll see that the variable, is now a long string of numbers, pulled exactly in order from the Text Input with not a single one of the other characters returned.
Now the other way to do this is to use Regex (Regular Expressions), this will give you a much finer degree of control over what can and can’t be matched.
For Example, if we want to match the characters 0 to 9, we only need modify our existing formula to the following to get the same value as above:
Set(
gblOriginalTextValue,
Concat(
Split(YourTextInput.Text,""),
If(
//If this item matches a digit
IsMatch(
Value,
//Here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
)
)…and we can use regexr or regex101 to confirm what this is trying to do by taking the string of regex [0-9] and putting it in the code window, then the explanation window will advise us on what that particular string of regex characters is trying to do:
Using the character string to iterate
Now we have a string of characters (that in this case happen to only be numbers), and we want to be able to after every tenth character insert a semi-colon.
So how do we go about this?
We will want something we can iterate over, so we will need to Split out that string again into a table of values.
Set(
gblOriginalTextValue,
Split(
Concat(
Split(
YourTextInput.Text,
""
),
If(
//If this item matches a digit
IsMatch(
Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
),
""
)
)This then gives us a Table of values, with each value being one of the characters (numbers in this case) in the string.
Using ForAll, we know we can iterate over the table of values and do something for every value within,
but how do we know which Position within the string we are currently dealing with?
(its numeric position i.e. character number 6 out of 37)
This is where our Friend the Sequence function comes in handy. Sequence effectively creates a Tabled sequence of numbers, based on a starting number of your choosing, with its second parameter determining how much it should increase or decrease by (i.e. should we add 1 every time or maybe we want to subtract units of 3 or maybe even increase in units of 0.315).
Sequence is an incredibly powerful function.
To use Sequence we need a number though, not a table. So we need to find a way to know how many items we need to do this for.
I can immediately think of two possible ways to do this. The first way, is to use the CountRows function to count how many rows are in the Table, like this:
Set(
gblOriginalTextValue,
CountRows(
Split(
Concat(
Split(
YourTextInput.Text,
""
),
If(
//If this item matches a digit
IsMatch(
Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
),
""
)
)
)This gives us a value of 40. There are 40 characters in the string we are trying to evaluate.
The other way to do this is to go back up a level, and instead of splitting it again, we use the Len function to count how many characters are in the string. This functions returns how many characters are in the string it is given.
Set(
gblOriginalTextValue,
Len(
Concat(
Split(
YourTextInput.Text,
""
),
If(
//If this item matches a digit
IsMatch(
Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
)
)
)
Creating the final result (string)
Now that we have the number of rows/items we need to perform tasks over, we can create a new string by going through each of these with our Sequence function and then use the number of the current sequence to determine whether we are at the tenth character.
So how do we know we are at the tenth character? We use a function that is often overlooked, (both in math and certainly in Power Apps), called Mod [this stands for Modulus].
The Mod function takes a number, divides it by another number, and then returns the remainder. We can use this by dividing the current item number that we are on by 10, if the remainder is not zero, then we have not cleanly divided by ten, so we are not at the tenth character.
In our example we will use this to then insert a semi-colon once we reach the tenth character.
We will start by creating a looping/iterating structure by using ForAll and Sequence:
Set(
gblOriginalTextValue,
ForAll(
Sequence(
Len(
Concat(
Split(
YourTextInput.Text,
""
),
If(
//If this item matches a digit
IsMatch(
Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
)
)
),
//Thing that happens for every item
//In this case we are just using the Value of the Sequence
//Which will create us a table of items with the current iteration
Value
)
)
Ok, so we have our loop (which is looping over a set of row numbers only, not the actual items).
Now what do we want it to DO when going through each item?
We want it to go through each item and then we want it add a semi-colon on the tenth, but how can we know we are on the tenth?
We use a combination of functions.
The FirstN function, gets us the First N number of rows in a table.
The Last function, gets us the Last row in all of the rows within the current context.
So for example, if I wanted the First 1 Rows of our example we’ve been working with, I would get:
I can then Expand that same logic for the rest of the rows. We get the Last as we are going through 1,2,3,4 and we want to get THAT row (1/2/3/4), in this way we can iterate through the values.
Using this Last(FirstN()) pattern, we can numerically iterate through using the row numbers we are currently going through in the ForAll. Then we can use the Mod function to check if we are in the tenth character and add a semi-colon to that row (which we will reform into a string afterwards):
Set(
gblOriginalTextValue,
ForAll(
Sequence(
Len(
//This code gets us a single long string of valid characters
Concat(
Split(
YourTextInput.Text,
""
),
If(
//If this item matches a digit
IsMatch(
Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
)
)
),
//Thing that happens for every item
//We Take the FirstN Characters
//Then we get the Last of that
Last(
FirstN(
//We split the valid string into characters
// then take the character (Value)
Split(
//This is the same string of characters from above
Concat(
Split(
YourTextInput.Text,
""
),
If(
//If this item matches a digit
IsMatch(
Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
),
""
),
Value
)
).Value
&
//Check if we are on the tenth character
// by dividing by 10 and checking for the modulus (remainder)
If(
Mod(ThisRecord.Value,10) = 0,
";",
""
)
)
)This gets us the array of characters with their semi-colon added every tenth number:
Now all we need to do is use Concat to get that table of values back into a string of characters:
Set(
gblOriginalTextValue,
Concat(
ForAll(
Sequence(
Len(
//This code gets us a single long string of valid characters
Concat(
Split(
YourTextInput.Text,
""
),
If(
//If this item matches a digit
IsMatch(
Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
)
)
),
//Thing that happens for every item
//We Take the FirstN Characters
//Then we get the Last of that
Last(
FirstN(
//We split the valid string into characters
// then take the character (Value)
Split(
//This is the same string of characters from above
Concat(
Split(
YourTextInput.Text,
""
),
If(
//If this item matches a digit
IsMatch(
Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
),
""
),
Value
)
).Value
&
//Check if we are on the tenth character
// by dividing by 10 and checking for the modulus (remainder)
If(
Mod(
ThisRecord.Value,
10
) = 0,
";",
""
)
),
Value
)
)As you can see from this image, our variable is now doing exactly what we want.
However, there is still a trailing semi-colon after the last item, as this is also a tenth character item. We can fix this by adding a check as part of the semi-colon addition, based on the same Len calculation we did before:
Set(
gblOriginalTextValue,
Concat(
ForAll(
Sequence(
Len(
//This code gets us a single long string of valid characters
Concat(
Split(
YourTextInput.Text,
""
),
If(
//If this item matches a digit
IsMatch(
Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
)
)
),
//Thing that happens for every item
//We Take the FirstN Characters
//Then we get the Last of that
Last(
FirstN(
//We split the valid string into characters
// then take the character (Value)
Split(
//This is the same string of characters from above
Concat(
Split(
YourTextInput.Text,
""
),
If(
//If this item matches a digit
IsMatch(
Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
),
""
),
Value
)
).Value
&
//Check if we are on the tenth character
// by dividing by 10 and checking for the modulus (remainder)
If(
Mod(
ThisRecord.Value,
10
) = 0
&&
//Only add ; if this item is also not the last character
!(
ThisRecord.Value=
Len(
//This code gets us a single long string of valid characters
Concat(
Split(
YourTextInput.Text,
""
),
If(
//If this item matches a digit
IsMatch(
Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
)
)
),
";",
""
)
),
Value
)
)
Cleaning up the code
This Code is very lengthy and re-uses many of the same sections of logic. We can make this not only more efficient, but also easier to read by reviewing our code and using the With() function to define temporary variables that only exist within the scope of that point-in-time formula execution.
Let’s start by looking at the building blocks for our code.
We have code that:
- Creates a valid string of characters
- Creates an array from the valid string
- Measures the length of the Array
- Uses those values to create a new string.
Each of these relies on the previous and is using values from those within itself, thus duplicating code.
Let’s start with moving our string creator to a temporary variable, and then replacing it throughout our code:
Set(
gblOriginalTextValue,
With(
{
CharacterString:
Concat(
Split(YourTextInput.Text,""),
If(
//If this item matches a digit
IsMatch(Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
)
},
Concat(
ForAll(
Sequence(
Len(
//This is the string of characters from above
CharacterString
)
),
//Thing that happens for every item
//We Take the FirstN Characters
//Then we get the Last of that
Last(
FirstN(
//We split the valid string into characters
// then take the character (Value)
Split(
//This is the same string of characters from above
CharacterString,
""
),
Value
)
).Value
&
//Check if we are on the tenth character
// by dividing by 10 and checking for the modulus (remainder)
If(
Mod(
ThisRecord.Value,
10
) = 0
&&
//Only add ; if this item is also not the last character
!(
ThisRecord.Value=
Len(
//This code gets us a single long string of valid characters
CharacterString
)
),
";",
""
)
),
Value
)
)
)Next we can take the number of valid characters determination code and turn it into a temporary variable (note how we are layering the With functions Inside of each other, so that we can refer to the variables in the layer above them):
Set(
gblOriginalTextValue,
With(
{
CharacterString:
Concat(
Split(YourTextInput.Text,""),
If(
//If this item matches a digit
IsMatch(Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
)
},
With(
{
NumberOfValidCharacters: Len(CharacterString)
},
Concat(
ForAll(
Sequence(
NumberOfValidCharacters
),
//Thing that happens for every item
//We Take the FirstN Characters
//Then we get the Last of that
Last(
FirstN(
//We split the valid string into characters
// then take the character (Value)
Split(
//This is the same string of characters from above
CharacterString,
""
),
Value
)
).Value
&
//Check if we are on the tenth character
// by dividing by 10 and checking for the modulus (remainder)
If(
Mod(
ThisRecord.Value,
10
) = 0
&&
//Only add ; if this item is also not the last character
!(
ThisRecord.Value=NumberOfValidCharacters
),
";",
""
)
),
Value
)
)
)
)Then we take the code that creates an Array of the valid characters, and turn that into a temporary variable using the With function, layering it inside the other two With functions we have used:
Set(
gblOriginalTextValue,
With(
{
CharacterString:
Concat(
Split(YourTextInput.Text,""),
If(
//If this item matches a digit
IsMatch(Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
)
},
With(
{
//Getting the number of valid characters to iterate through
NumberOfValidCharacters: Len(CharacterString)
},
With(
{
//We split the valid string into characters
// then take the character (Value)
ValidCharacterArray: Split(CharacterString,"")
},
Concat(
ForAll(
Sequence(NumberOfValidCharacters),
//Thing that happens for every item
//We Take the FirstN Characters
//Then we get the Last of that
Last( FirstN( ValidCharacterArray, Value ) ).Value
&
//Check if we are on the tenth character
// by dividing by 10 and checking for the modulus (remainder)
If(
Mod(
ThisRecord.Value,
10
) = 0
&&
//Only add ; if this item is also not the last character
!(
ThisRecord.Value=NumberOfValidCharacters
),
";",
""
)
),
Value
)
)
)
)
)This is about as efficient as we can make it – any further pulling of items into With statements would not serve any purpose, as the inner layer is now the final layer and whether we are doing the function within a With statement or whether it’s written out explicitly equates to the same number of steps/calls.
Adding this code back to our Text Input
Put all of the code from the last block we created back into the OnChange of the Text Input control:
Set(
gblOriginalTextValue,
With(
{
CharacterString:
Concat(
Split(YourTextInput.Text,""),
If(
//If this item matches a digit
IsMatch(Value,
//here's where our regex goes
"[0-9]"
),
//Then use that value
Value
)
)
},
With(
{
//Getting the number of valid characters to iterate through
NumberOfValidCharacters: Len(CharacterString)
},
With(
{
//We split the valid string into characters
// then take the character (Value)
ValidCharacterArray: Split(CharacterString,"")
},
Concat(
ForAll(
Sequence(NumberOfValidCharacters),
//Thing that happens for every item
//We Take the FirstN Characters
//Then we get the Last of that
Last( FirstN( ValidCharacterArray, Value ) ).Value
&
//Check if we are on the tenth character
// by dividing by 10 and checking for the modulus (remainder)
If(
Mod(
ThisRecord.Value,
10
) = 0
&&
//Only add ; if this item is also not the last character
!(
ThisRecord.Value=NumberOfValidCharacters
),
";",
""
)
),
Value
)
)
)
)
)Set your Text Input control’s Default property to:
gblOriginalTextValue
This will probably be your brain right now after absorbing all of this mountain of information and thoughts, but if you can digest these piece-by-piece I guarantee you will be a better coder in your Power Apps!
Conclusion
In this tutorial and teaching blog, we have covered the explanation of and use of many different functions:
We have also created a clean code solution that will get only the numbers from a Text Input control that accepts both numbers and text, and then strip out any other characters, after which it does insert a semi-colon every 10 numbers.
Bonus – most efficient solutions!
Solution by thomas
I was really hoping someone would use the better regex string for this (why I hinted at it above) and one of the other match functions, and within 3 hours of publishing this we’ve already got a great solution submitted to us by ‘thomas’
The Solution
Set(gblOriginalTextValue,
Coalesce(
// create string with 10 digits followed by a semicolon for all rows
Concat(
// create table with always 10 digits in a row
MatchAll(
// rebuild the string with only numeric values in it
Concat(
// remove all non numeric values
RemoveIf(
// split string into table with single characters
Split(YourTextInput.Text, ""),
// finding the value in the string
IsBlank(Find(Value, "0123456789"))
),
Value
),
"\d{10}"
), FullMatch, ";"
),
""
)
);So let’s break this down and see how this works:
First, we Split the string to get a table of values, then we use RemoveIf to strip out any values that are not numbers, then we Concat that back into a string of character, then we use the MatchAll function which will return Values based on our matching criteria (in this case 10 digits defined by a regex string), then we Concat that all together with the returned FullMatch Value and a semi-colon, then we Coalesce that to ensure no blanks are returned with our results.
This is a fantastic solution! This is definitely more efficient than the solution I provided and goes to show that there are many, many ways we can get the same results within Power Apps, as we have such a vast array of functions to choose from when writing our code 🙂
Thank you so much for taking the time to read through to this point, I really do hope you have gained some new knowledge or at the very least some inspiration for your Apps! (Always dig deeper!)
If you want to check out my other blog articles you can have a look around here: iAm_ManCat Blog
You can find links to my various social media accounts at the bottom-right of this page 🙂
Hope you have a great day!
Hi Sancho,
I appreciate your wonderful blog post.
My suggested solution would be the following:
Coalesce(Concat(MatchAll(inpStr.Text, “\d{10}”), FullMatch, “;”), “”)
Thanks for suggesting a solution Thomas, I have appended yours to the bottom of the blog!
It works, but it does assume the user will be perfect at typing/pasting in the numbers in exact 10-number blocks
I have changed the formula slightly to include non-numeric values.
The disadvantage is that the formula can thus no longer be used in non-behavior properties.
Set( _resultStr,
Coalesce(
// create string with 10 digits followed by a semicolon for all rows
Concat(
// create table with always 10 digits in a row
MatchAll(
// rebuild the string with only numeric values in it
Concat(
// remove all non numeric values
RemoveIf(
// split string into table with single characters
Split(inpStr.Text, “”),
// finding the value in the string
IsBlank(Find(Result, “0123456789”))
),
Result
),
“\d{10}”
), FullMatch, “;”
),
“”
)
);
That’s fantastic – thank you for sharing! I have amended the article to reflect the new code – it covers all edge cases I can think of and is more efficient 🙂
There is always room for improvement… 😉
If the number of digits found is not divisable by 10, an error will be thrown.
Set( _resultStr,
IfError(
With(
{
numStr:
Concat(
Split(inpStr.Text, “”),
If(IsMatch(Result, “[0-9]”), Result)
)
},
If(Mod(Len(numStr), 10) = 0,
Concat(
MatchAll( numStr, “\d{10}”),
FullMatch, “;”
),
// throw error – will be catched by IfError()
Error({ Kind: ErrorKind.Validation, Message: “the number of digits found is not divisable by 10.” })
)
),
// handle error
Notify($”Please check incoming string: {FirstError.Message}”, NotificationType.Error);
SetFocus(inpStr);
“”
)
);