cut & paste

  • Thread starter Thread starter kbkst
  • Start date Start date
K

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
 
Try this formula in B1
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")","")," ","")

And copy down
 
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
 
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
 
George Gee:
Thank you so much for your help. This worked wonderfully, and you are a hero
in the office.

kbkst

George said:
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
Click to expand...
[quoted text clipped - 6 lines]
Thanks,
kbkst
Click to expand...
Click to expand...
 
Glad to have helped.
Hero? ... hardly!

George Gee


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

kbkst

George said:
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
Click to expand...
[quoted text clipped - 6 lines]
Thanks,
kbkst
Click to expand...
Click to expand...
Click to expand...
 
Back
Top