Formula required to add country code to telephone number and remove preceding zero

**Richard Wilkinson** · 10-20-2022, 06:30 AM

Good morning everyone.

I'm currently working on a data cleanse and have thousands of contacts where they've put input as a standard phone number e.g 01234 567890.

I need to change these to +441234 567890 but rather than go through thousands of contacts and manually do it, I'd prefer to output a CSV, add a formula to change these and then re-upload the CSV to make bulk changes.

Can anybody assist with a formula to do this please?

So to clarify: I need a formula to remove the preceding 0 and replace it with +44

Thanks in advance

Rich

**Sam Capricci** · 10-20-2022, 06:47 AM

assuming all the numbers before the space will be replaced with +441234, this would work...
="+441234"&MID(A2,SEARCH(" ",A2),7)

Oh, and you may need to replace the commas with semicolons for your formulas?

**Richard Wilkinson** · 10-20-2022, 06:55 AM

Hi Sam.

The problem is I've got lots of different area codes plus mobile numbers.

Don't think this is going to be as simple a fix as I initially thought....

**Sam Capricci** · 10-20-2022, 06:59 AM

possibly uploading a workbook with more samples and expected results.
after rereading your first post I came up with a shorter version that I wonder if it would work if... all your examples are roughly the same length and it is simply replacing just the 0 with +44... ="+44"&RIGHT(A2,11)

**Phuocam** · 10-20-2022, 07:01 AM

Try this

="+44"&Right(A2,10)

**Richard Wilkinson** · 10-20-2022, 07:04 AM

Example formats.xlsx

I've attached a very brief example spreadsheet to show what formats I'm looking for/dealing with.

**HansDouwe** · 10-20-2022, 07:08 AM

Please try and copy down:

Formula:

Please Login or Register  to view this content.

**Sam Capricci** · 10-20-2022, 07:08 AM

here is your example using the formula I gave you in post #4, seems to return what you want.

**Sam Capricci** · 10-20-2022, 07:13 AM

Hans' might be easier, however if you want mine, I noticed you have different lengths so this would be my formula adjusted for the length...
=IF(LEN(A2)=12,"+44"&RIGHT(A2,11),"+44"&RIGHT(A2,10))

**Richard Wilkinson** · 10-20-2022, 07:16 AM

Brilliant - thanks everyone, that's really helped me out.

**Sam Capricci** · 10-20-2022, 07:33 AM

if that takes care of your issue, please mark the post as solved using the thread tools dropdown at the top of the post, AND don't forget you can thank any or all of those who've taken the time to help you by clicking on * Add Reputation under their post as that is how we advance on this forum.

Formula required to add country code to telephone number and remove preceding zero

LinkBack

Thread Tools

Rate This Thread

Display

Formula required to add country code to telephone number and remove preceding zero

Re: Formula required to add country code to telephone number and remove preceding zero

Re: Formula required to add country code to telephone number and remove preceding zero

Re: Formula required to add country code to telephone number and remove preceding zero

Re: Formula required to add country code to telephone number and remove preceding zero

Re: Formula required to add country code to telephone number and remove preceding zero

Re: Formula required to add country code to telephone number and remove preceding zero

Re: Formula required to add country code to telephone number and remove preceding zero

Re: Formula required to add country code to telephone number and remove preceding zero

Re: Formula required to add country code to telephone number and remove preceding zero

Re: Formula required to add country code to telephone number and remove preceding zero

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to insert telephone country code using ISO code

2 letter country code within a string to full country name?

How do I add country code before phone number?

[SOLVED] Formula to insert text in new column if preceding more than one of the preceding have val

[SOLVED] Country code maping with Caller number

how do i remove telephone number hyphens from a list in Excel?

Format the Cell into a telephone number but using country code

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions