# Removing 1- prefix in phone #'s

1. ## 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-.

2. ## Re: Removing 1- prefix in phone #'s

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

3. ## 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

4. ## 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

5. ## 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. ## 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. ## Re: Removing 1- prefix in phone #'s

Originally Posted by shg
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))

8. ## Re: Removing 1- prefix in phone #'s

Interesting, thanks, Martin.

9. ## 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 -.

##### Users Browsing this Thread

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

#### 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