+ Reply to Thread
Results 1 to 6 of 6

Add Standard Prefix To Cells

  1. #1
    Registered User
    Join Date
    12-03-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2011 For Mac
    Posts
    6

    Add Standard Prefix To Cells

    \1

    Hi friends,

    I'm doing some housekeeping for my friends & family contact numbers. I want to have a standard prefix for them. The prefix that I want is: +60 (Malaysia Country Code telephone number).

    In my worksheet now, I have 3 types of data (phone numbers):

    First type: 123456789 (no "+60" prefix)
    Second type: 0123456789 (no "+6" prefix)
    Third type: 60123456789 and +60123456789 (yes, this is what I want)

    My question:

    How do I check for all First type & Second type data and add the missing "+60" or "+6" prefix for them?

    Note:

    In my country Malaysia, standard telefon number is like this: +60123456789 or 60123456789. This happened to me because before this, I recorded this data using cell phone and I just type '0123456789'. When I transfer the contacts to Excel and sync the contacts via some softwares, they ignore zero '0' in front the telephone number, unless I'm saving the telephone number starting by six '6'.

    Thank you for your helps guys!
    Last edited by zulhfreelancer; 06-08-2013 at 11:38 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Add Standard Prefix To Cells

    If your data is in A1 down, use this in B1, copied down...
    =IF(LEN(A1)=9,60&A1,IF(LEN(A1)=10,6&A1,A1))

    You can then copy/paste values over those formulas to get just the numbers
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Add Standard Prefix To Cells

    or perhaps
    ="60"&right(a1,9)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Add Standard Prefix To Cells

    This returns a number rather than a text string
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    It also handles +60123456789 entered, or formatted as text
    as would
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    To show a leading plus sign format the cell Number > Custom
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Marcol; 06-09-2013 at 04:32 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Add Standard Prefix To Cells

    imo a phone number should always be stored as text ;-)

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Add Standard Prefix To Cells

    Quote Originally Posted by JosephP View Post
    imo a phone number should always be stored as text ;-)
    I have no issue with that.
    Just showing an alternative way.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1