+ Reply to Thread
Results 1 to 4 of 4

Greetings and Formula to amend telephone numbers imported from Excel

  1. 04-09-2013, 10:47 AM #1
    stevebarham1
    stevebarham1 is offline
    Registered User
    Join Date
    04-08-2013
    Location
    Newcastle Upon tyne
    MS-Off Ver
    Excel 2010
    Posts
    1

    Talking Greetings and Formula to amend telephone numbers imported from Excel

    Good Afternoon. As a new member to the forum, I am looking forward to learning from the vast amount of expertise that is on the web. Also in return, I will also post possible fixes.

    My question this time relates to a formula for amending phone numbers.

    A contacts list has been exported from Outlook contacts and the phone number formats are a mess. These include the following formats:

    01912791234
    0191 279 9123
    0191279 9123
    0191 2799741
    07123 561 789
    07123561789
    07123 561789
    +4401912791234
    +44 0191 279 1234

    Basically they are all a mess.

    What I need is a formula to remove the leading 0, then add +44 to the beginning, keep the space after the +44 but remove all others.

    Is this possible?????? I have found ways of doing this once by one, but Ideally I would like one formula to do the whole lot in one go

    Any help is appreciated
    Reply With QuoteRegister To Reply
  2. 04-09-2013, 11:01 AM #2
    dredwolf
    dredwolf is offline
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Greetings and Formula to amend telephone numbers imported from Excel

    assuming data starts in A1, this in B1:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Drag down

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post
    Reply With QuoteRegister To Reply
  3. 04-09-2013, 11:18 AM #3
    AZ-XL
    AZ-XL is offline
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Greetings and Formula to amend telephone numbers imported from Excel

    ="+44 "&right(value((substitute(a1," ",""))),10)
    Appreciate the help? CLICK *
    Reply With QuoteRegister To Reply
  4. 04-09-2013, 11:39 AM #4
    Kevin UK
    Kevin UK is offline
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Greetings and Formula to amend telephone numbers imported from Excel

    Hi stevebarham1

    Try the following, assuming your data is in column A in B1 and copy down:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Format the cells as "+44 "0000000000

    Or for text values: ="+44 "&--TRIM(SUBSTITUTE(SUBSTITUTE(A1," ",""),"+44",""))
    Last edited by Kevin UK; 04-09-2013 at 11:43 AM.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1