modify column values that starts with 1

**spyrule** · 11-09-2007, 11:20 AM

Hello,

I am attempting to filter a 60,000+ column of phone numbers, and all I am trying to do, is run a macro that removes the 1 at the start of any numbers that have it. some do, many do not have it.

example :

original value : 18005551212

desired value : 8005551212

Is their a fast way to do this?

I am kinda clueless where to start with a macro like this.

thanks in advance,

spyrule.

**oldchippy** · 11-09-2007, 11:25 AM

If the numbers with a 1 in front are all 11 digits long, then with a formula

=IF(LEN(A1)=11,RIGHT(A1,10)*1,A1) copied down

**dominicb** · 11-09-2007, 11:32 AM

Good afternoon spyrule

You could use this formula, in a spare column - once that is done you could use Copy > Paste Special, paste as values over original range.

=IF(LEFT(A1,1)="1",RIGHT(A1,LEN(A1)-1),A1)

HTH

DominicB