Check with REGEX VBA if phone is fixed or mobile (checking 3rd number in regex)

Question

I have to do a REGEX VBA that check the phone number in this pattern, I thought I would try to identify the 3rd number and check if it is different from 9 or 8, getting like this:

Cellular 51992656588 - returning TRUE

Cellular 5192656588 - returning TRUE

Landline 5133542737 - returning FALSE

• Note: I am treating the withdrawal of "()" and "-" with this Regex here: \\[\-()]\g

Answer 1

Regex problem

The problem with your Regular Expression is that it does not validate, for example if a fixed number starts with 4? Many commercial phones start with 4, mainly in São Paulo, which has exhausted its numbers starting with 3.

What if the number is in the old pattern? With 8 digits?

Solution

You can use Regex as a VBA function to validate this.

Regex

Enable Regex in Excel

1. Regex needs to be enabled, Enable the Developer mode
2. In the 'Developer' tab, click 'Visual Basic' and the VBA window will open.
3. Go to 'Tools' -> 'References...' and a window will open.
4. Search for 'Microsoft Vbscript Regular Expressions 5.5', as in the image below. And enable this option.

Expression

The expression to find cell phone numbers is as follows: ^(?:\d{2})?(?:[6-9]\d{7}|9\d{8})$ together with some checks in the code.

Where you find numbers that start with two digits or not and then with 6.7.8 and 9 (mobile numbers) and have 7 digits after starting with these numbers or starting with 9 and having 8 digits after.

Note: In São Paulo the numbers that start with 5 are hybrid between fixed and mobile telephony. (Telek). Therefore, there is no clear rule for numbers that start with 5 and are considered fixed.

The demo of Regex101 can be seen.

If you just want to check if you have the number 9 and then 8 digits, use the following expression: ^(?:\d{2})?9\d{8}$

UDF (User Defined Function) in Excel VBA

Code

First insert this function into a module.

Option Explicit
Public Function ValidarCelular(Myrange As Range) As String
    On Error GoTo ErrHandler:
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String

    'Padrão RegEx
    strPattern = "^(?:\d{2})?(?:[6-9]\d{7}|9\d{8})$"

    If strPattern <> "" Then
        'Retirar espaços
        strInput = Replace(Myrange.Value, " ", "")
        'Retirar '-'
        strInput = Replace(strInput, "-", "")
        'Retirar parênteses
        strInput = Replace(strInput, "(", "")
        strInput = Replace(strInput, ")", "")

        'Verifica o comprimento da string, para verificar um número inválido
        If Len(strInput) > 11 And IsNumeric(strInput) Then
            GoTo ErrHandler
        Else
            'RegEx
            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With
                If regEx.Test(strInput) Then
                    ValidarCelular = "Celular"
                Else
                    ValidarCelular = "Fixo"
                End If
        End If
    End If
Exit Function
ErrHandler:
    ' Tratamento de Erro
    ValidarCelular = CVErr(xlErrNA)
    On Error GoTo 0
End Function

Upshot

The test was done with validation data in column A and inserting the function =ValidarCelular(A1) in column B.

Explanation Code

1. Creates the function ValidarCelular with MyRange as an input variable, i.e., an input variable cell.
2. Adds the Regex pattern in strPattern.
3. Checks whether strInput no spaces, parentheses and hyphens.
4. Checks if it has more than 11 characters and is numerical.
5. Validates with regular expression whether it is a mobile or fixed number.

Answer 2

To check only the third digit, without worrying about the validation of the full number (assuming it has been done before), you can use the following Regex:

/^..(8|9)/g

The circumflex detects the beginning of the string, the dot serves as a wildcard and the characters inside the parentheses will be checked to determine the presence of a or ( | ) another.