Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro or Formula to remove Text from Cells
I sent this before but not sure if it was accepted.
I have a worksheet with some cells containing values and some containing values and text. e.g. 1234 or T 1234 or Test 1234. I need a macro or Formula to remove only the text from these cells. I tried creating a formula using ISTEXT function but I am missing something and its not working. Help and Thanks. smck |
#2
|
|||
|
|||
|
#3
|
|||
|
|||
If your intent is to retain formulas and remove text constants from within a
selection you would use a macro. This is built into the InsertRowsAndFillFormulas maro in Insert a Row using a Macro to maintain formulas http://www.mvps.org/dmcritchie/excel/insrtrow.htm which inserts the number of rows you ask for. But for just thr pafrt the clears constants within a selection, you can use:. Sub C;earConstants() Selection.SpecialCells(xlConstants).ClearContents end Sub If not familiar with installing using macros see http://www.mvps.org/dmcritchie/excel/getstarted.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "smck" wrote in message ... I sent this before but not sure if it was accepted. I have a worksheet with some cells containing values and some containing values and text. e.g. 1234 or T 1234 or Test 1234. I need a macro or Formula to remove only the text from these cells. I tried creating a formula using ISTEXT function but I am missing something and its not working. Help and Thanks. smck |
#4
|
|||
|
|||
Hi
If text part is always delimited from numeric part, there is only single word in text part or no text at all, and the text part is always the leftmost, then the formula above will do: =VALUE(IF(ISNUMBER(FIND(" ",TRIM(A1))),MID(TRIM(A1),FIND(" ",TRIM(A1))+1,LEN(A1)),A1)) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "smck" wrote in message ... I sent this before but not sure if it was accepted. I have a worksheet with some cells containing values and some containing values and text. e.g. 1234 or T 1234 or Test 1234. I need a macro or Formula to remove only the text from these cells. I tried creating a formula using ISTEXT function but I am missing something and its not working. Help and Thanks. smck |
#5
|
|||
|
|||
Obviously the macro I suggested was untested, but it was intended
to be written as Sub ClearConstants() Selection.SpecialCells(xlConstants).ClearContents end Sub |
#6
|
|||
|
|||
smck
Macro.......... Public Sub Stripper() ''strip numbers or letters, user choice via inputbox Dim myRange As Range Dim Cell As Range Dim myStr As String Dim i As Integer With Application .ScreenUpdating = False .Calculation = xlManual End With On Error Resume Next Set myRange = Range(ActiveCell.Address _ & "," & Selection.Address) _ .SpecialCells(xlCellTypeConstants) If myRange Is Nothing Then Exit Sub If Not myRange Is Nothing Then Which = InputBox("Strip Numbers - Enter 1" & vbCrLf & _ "Strip Letters - Enter 2") If Which = 2 Then For Each Cell In myRange myStr = Cell.text For i = 1 To Len(myStr) If (Asc(UCase(Mid(myStr, i, 1))) < 48) Or _ (Asc(UCase(Mid(myStr, i, 1))) 57) Then myStr = Left(myStr, i - 1) _ & " " & Mid(myStr, i + 1) End If Next i Cell.Value = Application.Trim(myStr) Next Cell Selection.Replace What:=" ", _ Replacement:="", Lookat:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False ElseIf Which = 1 Then For Each Cell In myRange myStr = Cell.text For i = 1 To Len(myStr) If (Asc(UCase(Mid(myStr, i, 1))) < 65) Or _ (Asc(UCase(Mid(myStr, i, 1))) 90) Then myStr = Left(myStr, i - 1) _ & " " & Mid(myStr, i + 1) End If Next i Cell.Value = Application.Trim(myStr) Next Cell End If End If With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Gord Dibben Excel MVP On Sun, 24 Apr 2005 19:49:01 -0700, "smck" wrote: I sent this before but not sure if it was accepted. I have a worksheet with some cells containing values and some containing values and text. e.g. 1234 or T 1234 or Test 1234. I need a macro or Formula to remove only the text from these cells. I tried creating a formula using ISTEXT function but I am missing something and its not working. Help and Thanks. smck |
#7
|
|||
|
|||
Hi Gord, Thanks a million for your help. This macro worked like a dream--it
is exactly what I wanted. Take care, smck "Gord Dibben" wrote: smck Macro.......... Public Sub Stripper() ''strip numbers or letters, user choice via inputbox Dim myRange As Range Dim Cell As Range Dim myStr As String Dim i As Integer With Application .ScreenUpdating = False .Calculation = xlManual End With On Error Resume Next Set myRange = Range(ActiveCell.Address _ & "," & Selection.Address) _ .SpecialCells(xlCellTypeConstants) If myRange Is Nothing Then Exit Sub If Not myRange Is Nothing Then Which = InputBox("Strip Numbers - Enter 1" & vbCrLf & _ "Strip Letters - Enter 2") If Which = 2 Then For Each Cell In myRange myStr = Cell.text For i = 1 To Len(myStr) If (Asc(UCase(Mid(myStr, i, 1))) < 48) Or _ (Asc(UCase(Mid(myStr, i, 1))) 57) Then myStr = Left(myStr, i - 1) _ & " " & Mid(myStr, i + 1) End If Next i Cell.Value = Application.Trim(myStr) Next Cell Selection.Replace What:=" ", _ Replacement:="", Lookat:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False ElseIf Which = 1 Then For Each Cell In myRange myStr = Cell.text For i = 1 To Len(myStr) If (Asc(UCase(Mid(myStr, i, 1))) < 65) Or _ (Asc(UCase(Mid(myStr, i, 1))) 90) Then myStr = Left(myStr, i - 1) _ & " " & Mid(myStr, i + 1) End If Next i Cell.Value = Application.Trim(myStr) Next Cell End If End If With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Gord Dibben Excel MVP On Sun, 24 Apr 2005 19:49:01 -0700, "smck" wrote: I sent this before but not sure if it was accepted. I have a worksheet with some cells containing values and some containing values and text. e.g. 1234 or T 1234 or Test 1234. I need a macro or Formula to remove only the text from these cells. I tried creating a formula using ISTEXT function but I am missing something and its not working. Help and Thanks. smck |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
Combining & formatting cells with text - Excel 2002 | Excel Discussion (Misc queries) | |||
Help adding text values | Excel Worksheet Functions | |||
Concatenation formula loses text wrap formatting | Excel Discussion (Misc queries) | |||
Macro Formula revision? | Excel Worksheet Functions |