updating phone number formatting

Hi everyone,
I need help, again. I have several worksheet that I need to combine the
information in order to upload into a access database. I problem that
I'm having is the phone numbers are formatted differently therefore
they are not all of the are uploading.  The Access data base field is
classified as text. The phone numbers that did upload correct are
formatted as special / phone numbers. I tried changing the cells
formatting but it doesn't update the existing information. It does
update if I type the number in by hand. 

Is there away of updating the existing numbers to be formatted like the
example below.
(555) 777-4444.

All suggestions are always appreciated.
Thanks
Lostinformulas


-- 
lostinformulas
------------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35229
View this thread: http://www.excelforum.com/showthread.php?threadid=569094

lostinformulas
8/7/2006, 5:30:00 PM

Hi,


The format you want is (###) ###-####.

However if your numbers has spaces it will read as text. You could us
a extra column with this formula to remove one space 

=SUBSTITUTE(A1," ","",1)

Drag down and covert to number then custom format as above.

If this doen't help a sample of the data would be useful

VBA Noob

--
VBA Noo
-----------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=56909

VBA Noob
8/7/2006, 6:10:17 PM
This is how the numbers are formatted in column "G"
800-942-5590
484-553-2066
254-715-2503
952-294-2990
715-284-5732
734-326-7844
405-387-9415
205-792-1208

Thanks
lostinformula

--
lostinformula
-----------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3522
View this thread: http://www.excelforum.com/showthread.php?threadid=56909

lostinformulas
8/7/2006, 7:06:24 PM
So say in Col H1 enter the below and drag down

=SUBSTITUTE(G1,"-","")

Then copy and paste special values.

Next you should get excel paste box options. Select and change t
number.

Next custom format cells as (###) ###-####

Hopefully job done.

VBA Noo

--
VBA Noo
-----------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=56909

VBA Noob
8/7/2006, 7:19:11 PM
Thanks that worked great!
Lostinformulas


-- 
lostinformulas
------------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35229
View this thread: http://www.excelforum.com/showthread.php?threadid=569094

lostinformulas
8/7/2006, 7:59:44 PM
lost,

i wish that excel had masking similar to Access, so that numbers that will 
never get calculated, like phone, zip or part numbers could be entered (as 
text) but be displayed with  parentheses, dashes, points in the correct 
places.

Does anyone know if MS has plans or this in future release?

Beege

"lostinformulas" 
<l*****************************15.9888@excelforum-nospam.com> wrote in 
message news:l*****************************15.9888@excelforum-nospam.com...
>
> Hi everyone,
> I need help, again. I have several worksheet that I need to combine the
> information in order to upload into a access database. I problem that
> I'm having is the phone numbers are formatted differently therefore
> they are not all of the are uploading.  The Access data base field is
> classified as text. The phone numbers that did upload correct are
> formatted as special / phone numbers. I tried changing the cells
> formatting but it doesn't update the existing information. It does
> update if I type the number in by hand.
>
> Is there away of updating the existing numbers to be formatted like the
> example below.
> (555) 777-4444.
>
> All suggestions are always appreciated.
> Thanks
> Lostinformulas
>
>
> -- 
> lostinformulas
> ------------------------------------------------------------------------
> lostinformulas's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=35229
> View this thread: http://www.excelforum.com/showthread.php?threadid=569094
> 


Beege
8/7/2006, 9:23:33 PM