+ Reply to Thread
Results 1 to 9 of 9

Removing 1- prefix in phone #'s

  1. #1
    Registered User
    Join Date
    04-08-2009
    Location
    Oceanside, CA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Removing 1- prefix in phone #'s

    It's me again...

    I read the thread below on how to utilize the Subsitute function to remove periods and thought about being able to use it for this. However, I have some phone #'s in my list that contain multiple 1- scenarios in them because the area code or 3-digit prefix sometimes include a 1- also. How do I make the formula only look at the 1- for long distance and not any other 1- found in the phone #? I want to remove all of the 1- for long distance because we are trying to use a new autodialer that is pre-programmed with the 1-.
    Last edited by TBeverson; 04-09-2009 at 06:18 PM.

  2. #2
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Removing 1- prefix in phone #'s

    =if(left(Number,1)=1,Mid(Number,2),Number)

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Removing 1- prefix in phone #'s

    thats missing an argument in mid Mid(Number,2),
    try
    =IF(--LEFT(A1,1)=1,MID(A1,2,LEN(A1)-1),A1)
    with number in a1
    edit
    put missing bracket on end ,doh, as pointed out by shg
    Last edited by martindwilson; 04-09-2009 at 02:56 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Removing 1- prefix in phone #'s

    Nice touch with the --, Martin. You're missing a paren.

    If you're formatting the result as a phone number (Format > Cells > Number > Special > Phone Number), then

    =IF(--LEFT(A1) = 1, MID(A1, 2, 99), A1) + 0
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Removing 1- prefix in phone #'s

    shg i didnt change back to No. with +0 in case second digit is zero 102345
    would be 2345 not 02345

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Removing 1- prefix in phone #'s

    I think 0 is universally (?) the international dialing prefix, and therefore not the lead digit of any area code or exchange.

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Removing 1- prefix in phone #'s

    Quote Originally Posted by shg View Post
    I think 0 is universally (?) the international dialing prefix, and therefore not the lead digit of any area code or exchange.
    er not in the uk
    mine is 0208 XXXXXXX for any national call from outside london!
    and a lot of ip phone systems especially on large networks require the full code even if dialled locally because the breakout point may not be in the same area
    then again we dont have leading 1 no's either (exept 123 for the time.100 operator and such))
    Last edited by martindwilson; 04-09-2009 at 03:17 PM.

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Removing 1- prefix in phone #'s

    Interesting, thanks, Martin.

  9. #9
    Registered User
    Join Date
    04-08-2009
    Location
    Oceanside, CA
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Removing 1- prefix in phone #'s

    THANKS BUNCHES!!! The only thing I tweaked slightly was the mid reference. I increased it to 3, so it would delete the 1 and the -.
    Last edited by TBeverson; 04-09-2009 at 06:16 PM.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1