Separate fixed headphones from furniture to add ninth digit

Question

Separate fixed headphones from furniture to add ninth digit

Navigation

#1 by (1 votes)
#2 by (0 votes)

-1

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

In both handset columns I have fixed and mobile numbers and for me the logic is simple: Fixed handset is a number that is less than 8 digits. In logic the formula is "if the number is less than such, enter 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 anyone help me?

excel excel-vba

asked by anonymous 11.12.2017 / 16:02

2 answers

Convert value received as int to char type in C Align controls with bootstrap

score 1 · Answer 1

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

Do this:

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

Being F1 is the cell phone number.

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

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

score 0 · Answer 2

Solution

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

Regex

Enable Regex in Excel

RegEx needs to be enabled, Enable Developer mode

In the 'Developer' tab, click 'Visual Basic' and the VBA window will open.

Go to 'Tools' - > 'References ...' and a window will open.

Look for 'Microsoft VBScript Regular Expressions 5.5', as in the image below. And enable this option.

Expression

Theexpressionforfindingmobilenumbersisasfollows:^[6|7|8|9](?:\d{7}|\d{3}\s*-\s*\d{4})$

Findnumbersthatbeginwith6,7,8and9andhave7digitsafterstartingwiththesenumbersor3digits-(hyphen)4digits.

Note:InSãoPaulothenumbersthatstartwith5arehybridsbetweenfixedandmobiletelephony.( teleco ). So there is no clear rule for numbers starting with 5.

The Regex101 demo can be viewed and 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

Result

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

Explanation Code

Creates the ValidarCelular function with MyRange as the input variable, that is, an input variable cell.

Add the Regex pattern to% with%

The Regex entry is strPattern , which uses the strInput function to remove the leading and trailing blanks.

The Trim() is the string that will replace what is in the cell, if it is a cell number. Adding the number 9 on the front with strReplace