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.


--
JimDandy
------------------------------------------------------------------------
JimDandy's Profile: http://www.excelforum.com/member.php...o&userid=16578
View this thread: http://www.excelforum.com/showthread...hreadid=514634

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=514634

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.

--
Regards,
Tom Ogilvy

"JimDandy" wrote in
message ...

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.


--
JimDandy
------------------------------------------------------------------------
JimDandy's Profile:
http://www.excelforum.com/member.php...o&userid=16578
View this thread: http://www.excelforum.com/showthread...hreadid=514634