Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
cut & paste
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
cut & paste
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
cut & paste
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
cut & paste
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
cut & paste
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
cut & paste
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy and paste Excel chart in Word -- font compresses | Charts and Charting in Excel | |||
need custom cut and paste functions | New Users to Excel | |||
I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) | |||
excel - numbers as text | New Users to Excel |