cut & paste

[kbkst]

I am cutting & pasting phone numbers onto my excel spreadsheet. When they
are pasted, they look like this: (216) 433-7623. I need them to look like
this: 2164337623, which means I need to remove the ( ), the space and the
dash. It takes forever doing thousands of these. Is there a formula that
can be added to these cells to automatically delete these when I paste?

Hope someone can help...I am loosing my mind!

Thanks,
kbkst

[Ron de Bruin]

Try this formula in B1
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")","")," ","")

And copy down

--
Regards Ron de Bruin
http://www.rondebruin.nl


"kbkst" <u18755@uwe> wrote in message news:5bef1c9a443b5@uwe...
>I am cutting & pasting phone numbers onto my excel spreadsheet. When they
> are pasted, they look like this: (216) 433-7623. I need them to look like
> this: 2164337623, which means I need to remove the ( ), the space and the
> dash. It takes forever doing thousands of these. Is there a formula that
> can be added to these cells to automatically delete these when I paste?
>
> Hope someone can help...I am loosing my mind!
>
> Thanks,
> kbkst

[George Gee]

Look at: Edit > Find and Replace.
For example:
To remove the minus sign:
Highlight all the phone numbers,
On the 'Find' tab, type -
On the 'Replace' tab, leave blank.
Click 'Replace all'.
Repeat for the () and space.
Practise on a copy of your file first!

If you need more help with this, post back.

George Gee



"kbkst" <u18755@uwe> wrote in message news:5bef1c9a443b5@uwe...
>I am cutting & pasting phone numbers onto my excel spreadsheet. When they
> are pasted, they look like this: (216) 433-7623. I need them to look like
> this: 2164337623, which means I need to remove the ( ), the space and the
> dash. It takes forever doing thousands of these. Is there a formula that
> can be added to these cells to automatically delete these when I paste?
>
> Hope someone can help...I am loosing my mind!
>
> Thanks,
> kbkst

[George]

If they are exactly in (xxx) xxx-xxxx format then this might help
Assume A1 contains the phone number
=MID(A1,2,3)&MID(A1,7,3)&MID(A1,11,4)
Then just copy down to all the cells

This returns a 10 digit Text value
If you need it as a number excel should automatically do this for you
Or use the VALUE function
=VALUE(MID(A1,2,3)&MID(A1,7,3)&MID(A1,11,4))

George

kbkst wrote:
> I am cutting & pasting phone numbers onto my excel spreadsheet. When they
> are pasted, they look like this: (216) 433-7623. I need them to look like
> this: 2164337623, which means I need to remove the ( ), the space and the
> dash. It takes forever doing thousands of these. Is there a formula that
> can be added to these cells to automatically delete these when I paste?
>
> Hope someone can help...I am loosing my mind!
>
> Thanks,
> kbkst

[kbkst via OfficeKB.com]

George Gee:
Thank you so much for your help. This worked wonderfully, and you are a hero
in the office.

kbkst

George Gee wrote:
>Look at: Edit > Find and Replace.
>For example:
>To remove the minus sign:
>Highlight all the phone numbers,
>On the 'Find' tab, type -
>On the 'Replace' tab, leave blank.
>Click 'Replace all'.
>Repeat for the () and space.
>Practise on a copy of your file first!
>
>If you need more help with this, post back.
>
>George Gee
>
>>I am cutting & pasting phone numbers onto my excel spreadsheet. When they
>> are pasted, they look like this: (216) 433-7623. I need them to look like
>[quoted text clipped - 6 lines]
>> Thanks,
>> kbkst

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200602/1

[George Gee]

Glad to have helped.
Hero? ... hardly!

George Gee


"kbkst via OfficeKB.com" <u18755@uwe> wrote in message
news:5bfb06c24b7f8@uwe...
> George Gee:
> Thank you so much for your help. This worked wonderfully, and you are a
> hero
> in the office.
>
> kbkst
>
> George Gee wrote:
>>Look at: Edit > Find and Replace.
>>For example:
>>To remove the minus sign:
>>Highlight all the phone numbers,
>>On the 'Find' tab, type -
>>On the 'Replace' tab, leave blank.
>>Click 'Replace all'.
>>Repeat for the () and space.
>>Practise on a copy of your file first!
>>
>>If you need more help with this, post back.
>>
>>George Gee
>>
>>>I am cutting & pasting phone numbers onto my excel spreadsheet. When
>>>they
>>> are pasted, they look like this: (216) 433-7623. I need them to look
>>> like
>>[quoted text clipped - 6 lines]
>>> Thanks,
>>> kbkst
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...l-new/200602/1