Start a new topic

Regex and telephone numbers

Hi Chaps!

Am building an IOM profile to import telephone numbers. Unfortunately the data we receive can be very messy and we like our telephone numbers to be very tidy!

So I am looking for regex to do the following:

Strip any spaces

Remove +44 (if there)

Add a leading zero (if not there)

 

As I am a regex numpty, step by step instructions would be great!

 

Thank you in anticipation!

 

Gemma


What is the full format you are looking for in the end?  Are these international numbers?

Hi Wayne

 

So we need telephone numbers as 07712345678 or 02081234567 or similar. These won't be international numbers, but some people will put +44 7712 345 678.

If there is a way to get Regex to deal with this in a multiple entry dictionary it would save us a lot of clean up work.

 

Thanks for coming back so quickly!

 

Gemma

Here is what I came up with

 

image

 

Note that the first two rows have a BLANK first column (i.e. --BLANK-- replacement value).  Also the second row, second column is a space character.  This replaces all spaces with blank.

 

Here is a link to how to import dictionary entries https://omatic-importomatic.helpdocsonline.com/import-dictionary-entries

Thank you Wayne! I shall give this a go!
Hi Wayne

Apologies, the first image isn't displaying properly, so I can't see what you have done there. I can see the second one, but not the first. Could you repost the first image please?

Many thanks

Gemma

I'm guessing Wayne had something like what you see below setup.

image


 

The Excel of the exported dictionary is below.  As Wayne noted in his explanation above the value in B3 is actually a space, it is not blank (this removes all spaces from the phone number. A1 through A3 are blank.

 

image

 

Explanation of how it works.  First we strip +44 off, next we strip off the leading zero if it's there, then we remove spaces.  After all of this we add the zero back on.  This is also setup to only work for 10 digit phone numbers.

 

Thanks,

 

John

Hi John

Many thanks for your reply, I shall try this :)

Gemma
Login or Signup to post a comment