Formula - converting values into a field to another column


I would like to convert columns that we receive from an application and place into columns that represent the data.

What l mean by this is:

I have a column called Telephone 1 and Telephone 2. Data might be like this when received:

Telephone 1 Telephone 2

0401 756 321 9645 7567

9811 3256

811 5456 0401 646 123

Regardless of what field the data is in, Telephone 1 or Telephone 2, l would like to move it to a column called

Landline phone and mobile phone.

So first l remove noise and spaces so it looks like this.

Telephone 1 Telephone 2

0401756321 96457567


8115456 0401646123

I was thinking based on this, as the data can be missing digits for home or mobile, is write a rule to say if 9 digits or less, this loads in landline phone and if 10 or more, loads into mobile column.

Is there a way to put a formula in to to load the data, create 2 new columns and have it look at both telephone 1 and 2 fields to determine

Please advise




  • Sueann SeeSueann See Administrator
    edited June 22

    Hi @Carolyn, there is a definitely a way to do this, but the question is probably what is the most efficient/elegant way of doing this.

    As an example, using the Transform step, i've created a new column called "Landline Phone" and created a custom function to check if either Telephone 1 or 2 is a Landline. Within the custom function, i have also created a re-usable function called CheckLandline so that i can easily check the length for either Telephone 1 or 2.

    You will have to repeat similar steps for a "Mobile Phone" column.

    You may also want to consider the following:

    • Is there a possibility that both Telephone 1 and Telephone 2 can be both landline or both mobile numbers, and if so, how do you want to represent that? Would it be a list of comma-delimited numbers?
    • Is it alright to alter the original value in Telephone 1 and Telephone 2 when removing the noise? If so, it is okay to apply the "Remove Noise" transformation on the existing Telephone 1 and 2 columns.
    • Would you want a more definite answer on the phone type? Have you heard of our real-time phone validation service that can provide you with the phone type ?

  • Ian BuckleIan Buckle Administrator

    @Sueann See really good approach to this scenario. I think we could also be a little less blunt in the approach when identifying mobile and landlines. There is a function called "Format Phone number" which enables you to output the phone type identified. It is really good for mobile identification and can identify landlines if you have the dialing code. In the data scenario above you could use it to identify mobiles and assume anything else is a landline, or you could make an assumption about the state based on the address and add the dialing code first before using the Format Phone function.

    Below is an example of the transformation with phone type assigned:

  • Nigel LightNigel Light Contributor

    Hi @Carolyn

    Just a thought - are you stripping off the leading zeros when you load the data? eg 96457567 on the above example?

    (been there, done that - really annoying)

    From memory, this is fixed in Data Explorer (or whatever the v2.0 equivalent is)


  • Ian BuckleIan Buckle Administrator

    Hi @Nigel Light

    In Australia the landline phone numbers are often captured without the dialing code (state code) as you do not need these to call the number if you are dialing from that state. It does present a particular nuanced challenge in the region where any validation engine/code requires the dialing code to correctly validate.

    The most common way to solve this is to utilize the address to dictate the dialing code via the state it belongs as it is incredibly likely the landline belongs to the address captured (obviously not always but its a start)

  • CarolynCarolyn Contributor

    Hi everyone

    thank you very much for your answers l have been able to include this in my steps.

    Appreciate your help



Sign In or Register to comment.