Separate fixed headphones from furniture to add ninth digit

Question

I have a spreadsheet with almost 60 thousand lines, with 3 columns: Name | Phone 1 | Phone 2

In the two speaker phones I have fixed and mobile phone numbers and for me the logic is simple: Fixed phone is a number LESS than 8 digits. In logic the formula is "if the number is less than such; put the ninth digit; if not just copy the number". But, I’m not getting

=SE((F1>E4);D4&" "&E4;D4&" 9"&E4)

Where "D4" is the area code. Can someone help me?

Answer 1

The old cell phone also has 8 digits, so it is having this difficulty.

Do it this way:

=SE(F1<55555555;D4&" "&E4;D4&" 9"&E4)

F1 being the cell phone number.

I suggest however, after having this data, copy and paste values and use conditional formatting, leaving only the number, no spaces.

[>9999999999]"("##") "#-####-####; "("##") "####-####

Answer 2

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: ^[6|7|8|9](?:\d{7}|\d{3}\s*-\s*\d{4})$

Where you find numbers that start with 6,7,8 and 9 and have 7 digits after starting with these numbers or 3 digits - (hyphen) 4 digits.

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 starting with 5.

The demo of Regex101 can be seen and the Debuggex also.

UDF (User Defined Function) in Excel VBA

Code

First insert this function into a module.

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

    strPattern = "^[6|7|8|9](?:\d{7}|\d{3}-\d{4})$"

    If strPattern <> "" Then
        strInput = Trim(Myrange.Value)
        strReplace = "9" & strInput

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With
            If regEx.test(strInput) Then
                ValidarCelular = regEx.Replace(strInput, strReplace)
            Else
                ValidarCelular = Myrange.Value
            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. The Regex entry is strInput, using the function Trim() to remove blanks at start and end.
4. The strReplace is the string that will replace what is in the cell if it is a cell number. Adding the number 9 in front with "9" & strInput