How to remove all unnecessary characters from a phone number in Excel?

Question

excel

How to remove all unnecessary characters from a phone number in Excel? Such as: dot, hyphen, quotation marks and so on.

Answer 1

vdasus.com/2011/11/03/regularnye-vyrazheniya-v-excel

Answer 2

I advise the HARDWARE to use NOT this variant, but the variant with regexp from entermix. It's more elegant and flexible, although it can be problematic if the brain refuses to work with regexps (like mine). If there is trouble with regexps, you are too lazy/no time to learn them yourself, you can use the following method - with reservations.
===
Formally, your criteria will be met by a code that leaves only numbers from the string fed to it, based on the literal interpretation of your TK - including the phrase "and so on".
However, in a telephone number, some characters that are not numbers are still needed. This is, for example, "+" in the first position, sometimes brackets (although not always).
Throw the function code into any available module (for example, into the Personal.xlsb module, if you have to work often), enter the function "=KeepNumbers (A1)", where A1 is the address of the cell with the "raw" number, get processed. ATTENTION! I recommend reading the comment below the code.

Public Function KeepNumbers(InputString As String)
    Dim i As Long, newString As String
    
    For i = 1 To Len(InputString)
        If IsNumeric(Mid(InputString, i, 1)) Then
            Debug.Print True
            newString = newString & Mid(InputString, i, 1)
            Debug.Print newString
        ElseIf i = 1 And Mid(InputString, i, 1) = "+" Then
            newString = newString & Mid(InputString, i, 1)
        End If
    Next i
    KeepNumbers = newString
End Function

This code does ONLY the following - leaves a plus if it is in the source cell as the first character of the source number; removes all other characters except numbers. It must be finished based on your full requirements.
This code does not convert the number to normal form (does not convert "8910...->+7910", does not check the phone number for correctness in length, does not check the correct use of brackets - it just throws them out). If you need to normalize phone numbers, you need to complicate the code with appropriate checks.

Answer 3

this is done quite simply using several nested SUBSTITUTE() functions in each of which you simply indicate which character you want to remove from the text string - in your case, from the phone number