Forum Discussion

Iron Contributor

Sep 17, 2020

Solved

Help simplifying this DAX code

I am trying to find a way to simplify this code since it annoying to add nested characters and I think it inefficient when it comes to performance The point is, to leave only number 0-9 character...

BI & Data Analysis

excel

Formulas and Functions

SergeiBaklan

Sep 17, 2020

Ocasio27

To my knowledge in DAX there is no equivalent for sequential SUBSTITUTE() in such cases. As a comment, these days such transformation usually are doing in Power Query with M-script, that will be one short string.

As for DAX, I'd adjust formula a bit like

=
VAR phoneNumber=
   IF(
     ISBLANK([CELULAR]),
     [TELEFONO],
     [CELULAR]
    )
VAR cleanText=
   SUBSTITUTE (
     SUBSTITUTE (
       SUBSTITUTE (
         SUBSTITUTE(
           SUBSTITUTE(
             SUBSTITUTE(
               phoneNumber, "-", "" 
             ), "(", ""
            ),")",""
          )," ",""
        ),"/",""
      ),"\",""
    )
RETURN cleanText

Bennadeau

Iron Contributor

Sep 17, 2020

Hi Ocasio27,

I don't know much about DAX but if you want to give it a shot with VBA, you can use this function.

Function PhoneNumber(sWord As String)
    Dim sChar As String
    Dim x As Integer
    Dim sTemp As String

    sTemp = ""
    For x = 1 To Len(sWord)
        sChar = Mid(sWord, x, 1)
        If Asc(sChar) >= 48 And _
          Asc(sChar) <= 57 Then
            sTemp = sTemp & sChar
        End If
    Next
    PhoneNumber = "(" & Left(sTemp, 3) & ") " & Left(Right(sTemp, 7), 3) & "-" & Right(sTemp, 4)
End Function

So if your "bastardized" number is in cell A1, you can type in cell B1

=PhoneNumber(A1)

You will probably want to add some sort of verification that there is 10 digit in the phone number though otherwise this won't work properly.