Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Removing only certain data in a cell
If I have a cell that contains the following, (Phone 123-456-7890), is there
a formula I can use that will strip the number off and put it in another cell? I have a big list I need to clean up, and doing it one at a time will take to long. Thanks |
#2
|
|||
|
|||
Hi
see: http://www.dicks-blog.com/archives/2...t-1/trackback/ -- Regards Frank Kabel Frankfurt, Germany "Juan" schrieb im Newsbeitrag ... If I have a cell that contains the following, (Phone 123-456-7890), is there a formula I can use that will strip the number off and put it in another cell? I have a big list I need to clean up, and doing it one at a time will take to long. Thanks |
#3
|
|||
|
|||
A help column and
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Phone", ""),"(",""),")","")) and if you don't want the hyphens =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,"Phone",""),"(",""),")",""),"-","")) and if you want a real number =--TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A 1,"Phone",""),"(",""),")",""),"-","")) Of course you might be best off just using editreplace Regards, Peo Sjoblom "Juan" wrote: If I have a cell that contains the following, (Phone 123-456-7890), is there a formula I can use that will strip the number off and put it in another cell? I have a big list I need to clean up, and doing it one at a time will take to long. Thanks |
#4
|
|||
|
|||
Juan
Macro to strip all but 123-456-7890. Copy the original column to an adjacent column then run this macro on the copied column. If you don't care about anything but the 123-456-7890 then don't bother copying the column. Sub RemoveAlphas() '' Remove alpha characters from a string. Dim intI As Integer Dim rngR As Range, rngRR As Range Dim strNotNum As String, strTemp As String Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _ xlTextValues) For Each rngR In rngRR strTemp = "" For intI = 1 To Len(rngR.Value) If Mid(rngR.Value, intI, 1) Like "[0-9,-]" Then strNotNum = Mid(rngR.Value, intI, 1) Else: strNotNum = "" End If strTemp = strTemp & strNotNum Next intI rngR.Value = strTemp Next rngR End Sub Gord Dibben Excel MVP On Thu, 16 Dec 2004 15:35:02 -0800, "Juan" wrote: If I have a cell that contains the following, (Phone 123-456-7890), is there a formula I can use that will strip the number off and put it in another cell? I have a big list I need to clean up, and doing it one at a time will take to long. Thanks |
#5
|
|||
|
|||
=RIGHT(A1,12) and copy down............
Vaya con Dios, Chuck, CABGx3 "Juan" wrote in message ... If I have a cell that contains the following, (Phone 123-456-7890), is there a formula I can use that will strip the number off and put it in another cell? I have a big list I need to clean up, and doing it one at a time will take to long. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering data in certain cell | Links and Linking in Excel | |||
Returning Data from a third cell in same row that meets two other | Excel Discussion (Misc queries) | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) | |||
I need a macro to find cut and paste data to new cell | Excel Discussion (Misc queries) | |||
Dragging cell data | Excel Discussion (Misc queries) |