extract "all" numerics from text string

Question

I have a Sharepoint Online list with a text field for a contact called Mobile Phone... I want to extract the numerics only...

(111) 222-3333 would be 1112223333
+99 111-222-3333 would be 991112223333
111-222-3333 would be 1112223333
111 222 3333 would be 1112223333

I was expecting to be able to do this with a calculated column, but it's not as obvious as I thought. Ultimately our dialing system needs a numerics only version of the value (no special characters or spacing). I have seen similar questions answered here but not this exact need. Thanks in advance...

Answer 1

I don’t think it is possible using a calculated column since the text field values are not in the same format.

I’ve also tried using Flow and split() function, similar to this thread, yet it fails because it cannot identify different separators in the strings.

Consider using custom code to achieve this requirement, for example, JavaScript.

Here is a reference for you: How to Get Numbers from a String in JavaScript.

Answer 2

After playing around with this for awhile...I decided to do the following...

=CONCATENATE(IF(ISERR(MID([Mobile Number],1,1)*1),"",MID([Mobile Number],1,1)),IF(ISERR(MID([Mobile Number],2,1)*1),"",MID([Mobile Number],2,1)),IF(ISERR(MID([Mobile Number],3,1)*1),"",MID([Mobile Number],3,1)),IF(ISERR(MID([Mobile Number],4,1)*1),"",MID([Mobile Number],4,1)),IF(ISERR(MID([Mobile Number],5,1)*1),"",MID([Mobile Number],5,1)),IF(ISERR(MID([Mobile Number],6,1)*1),"",MID([Mobile Number],6,1)), etc....keep going for however many characters you think you need to handle. ))

It works in so much that I get back the desired answers but it's not very flexible with the variable length. I go up to 23 characters in my code but the good news is I get back..