Help

Convert to standard mobile format (AirTable formulas)

Topic Labels: Formulas
752 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Kosta_Kondraten
7 - App Architect
7 - App Architect

Hi,

So I’m using PHP which has this functionality built in (basically I give it a number and it converts it to how I would like it…) specifically I’m in Australia but the format I’ve chosen to go with is like this:

+61415932797

so let’s break this down

+61 = country code

415932797 < number obviuosly

In Australia though when you call someone on mobile you dial 04… so it would be like

0415932797

if that makes sense (since you don’t use country codes when dialling within the country)… so I’ve got a bunch of these mobiles like this in my AirTable:

0402 930 888 - like this which I want to convert to

+61402930888 - as you can see no spaces (random spacing in my original) - ideally also skip any fields that have text in them but that’s an easy thing… so…

yeh long story short how do I regex

04 01 933 833 to

+61401933833

also keep in mind a number could all ready be country code… but then again I could just filter those with + and fix manually or something but would be good if for example

+61415933933 is left untouched by the regex

Hope that makes sense.

Thanks!

1 Reply 1
Karek_Rami
5 - Automation Enthusiast
5 - Automation Enthusiast

AI Answer:

To achieve this transformation using regular expressions in PHP, you can use the following approach:

Match a number starting with '04', followed by any combination of digits and optional spaces.
Replace the matched number by removing all spaces and adding the country code '+61' at the beginning, while ensuring that numbers already containing the country code are left untouched.

Here's a PHP code snippet that you can use:

<?php

function normalizeAustralianPhoneNumber($number) {
// Check if the number already contains the country code +61 and return it unmodified if it does
if (preg_match('/^\+61\d+$/', $number)) {
return $number;
}

// Regex to match an Australian number starting with '04' followed by any digits and optional spaces
$pattern = '/^04\s*(\d\s*){8}$/';

// Replacement pattern to format the number with country code +61 and without spaces
$replacement = '+614$1$2$3$4$5$6$7$8$9';

// Perform the regex replacement
$normalizedNumber = preg_replace_callback($pattern, function($matches) {
// Remove all spaces from the matched number
$digitsOnly = preg_replace('/\s+/', '', $matches[0]);
// Add the country code +61 to the number
return '+61' . substr($digitsOnly, 1);
}, $number);

return $normalizedNumber;
}

// Examples
echo normalizeAustralianPhoneNumber('0402 930 888') . "\n"; // Outputs: +61402930888
echo normalizeAustralianPhoneNumber('+61415933933') . "\n"; // Outputs: +61415933933
echo normalizeAustralianPhoneNumber('04 01 933 833') . "\n"; // Outputs: +61401933833

?>

This function first checks if the provided number already contains the country code '+61'. If it does, the number is returned as is. Otherwise, it applies a regular expression to find numbers that match the Australian mobile format starting with '04', followed by any digits interspersed with optional spaces. It then formats these numbers correctly, without spaces, adding '+61' at the beginning.

Please ensure that you test this code with various input scenarios to confirm that it handles all cases you expect in your application.