Add area code to phone numbers in ranges

  • Thread starter Thread starter JimDandy
  • Start date Start date
J

JimDandy

I have a worksheet that has telephone numbers in two seperate columns.
I'd like to locate all the 7-digit numbers (those without area codes)
in both columns and then add a specific area code to all those numbers.
Any number encountered that already has 10 digits I'd like to ignore and
leave alone.
 
asumptions:
#'s in column a starting row 1
7 digit numbers are of from xyz-abcd (length is 8)

in an empty column, row:

=if(len(a1)>8,a1,"(xxx) "&a1) where xxx is the area code

copy down, then change to values and then overlay original data
 
Dim i as long, j as long, rng as Ragne
Dim cell as Range, v as Variant
v = array(10,15)
for j = lbound(v) to ubound(v)
i = v(j)
set rng = Range(cells(1,i),cells(1,i).End(xldown))
for each cell in rng
if len(cell.Value) = 7 then
cell.value = "456" & cstr(cell.Value)
end if
Next rng
Next j

If your columns are adjacent, then it could be simpler. Change 10 and 15
to indicate your columns.
 
Back
Top