Phone numbers - consistent formatting

Hi

I have phone numbers within applications that can be in many different formats for landlines and mobiles. For eg:

Mobile

+61 401123 456

401 123 456

0401123456

Landlines

+613 8899 8899

(03) 88998899

0388998899

88998899

132132 (six digit company numbers)

1800333333

I would like the format to be:

Mobiles - 0401 123 456

Landlines 03 8899 8899

I have been able to do this by removing noise - takes out +() and call mum

Then format phone numbers using australia and national format which will supply format as (03) xxxx xxxx and mobiles as xxxx xxxx xxxx - which is perfect and then last remove noise is to just take out () it supplies.

However, for numbers as per above it works but not for 88998899 - we want this to be (03) 8899 8899 or worse case 8899 8899 - all other phone formats work. I was thinking of putting in a step whereby if number 8 or 9 in length, add an 03 in front. This could be different - could be a NSW number in australia but l want to show its wrong by not having format anyway so this is an option

Is there another way to do this? Everything else is really good

thanks

Carolyn

Comments

  • Clinton JonesClinton Jones Experian Elite

    HI @Carolyn this is an interesting problem. If I understand it correctly, the phone handler library is correctly handling phones where the country, carrier or exchange number is correctly provided but not handling the short number, where country and exchange or carrier are not provided. While the number is not wrong, it isn't being handled correctly or at least isn't being handled well.

    I assume that you want to retain these numbers and neither discard them, nor separate them from the rest of the data, you simply want to flag the up as problematic?

    You've a couple of ways of doing this, one is by adding a boolean(true/false) validation column, the other is doing as you suggest, which is to check the length of the number.

    I think the answer lies in what you would do with or how you would handle the results

    In this view based on the data you provided I have created two columns, one is the existing number converted to the ITU international E164 format.

    I did this using the quick transform for Format Phone Number but youo can also use the canvas version, the result is the same, it doesn't require denoising if you use this approach it simply does a raw conversion of whatever you provide and then bluntly applies the format.

    The second part is then validating what came out of the basic reformat. for this i created another new column and used the validate phone number function, again via the quick functions.

    You can see that the short number without branch exchnage failed the validation

    You could of course combine all of this and do the transform of the data in place

Sign In or Register to comment.