Macro or Formula to remove Text from Cells

S

smck

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
 
F

Franz

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
Click to expand...

If your values are always made af the same number of figures, you can use
somthing lik this

=VALUE(RIGHT(A2;4))

Here I suppose your "code" is in cell A2 and that your value is 4 figures
long. I'm not sure if you should put ";" or "," inside the function, because
I'm not using English version of Excel...

Hoping to be helpful...

Regards
 
D

David McRitchie

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
 
A

Arvi Laanemets

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))
 
D

David McRitchie

Obviously the macro I suggested was untested, but it was intended
to be written as

Sub ClearConstants()
Selection.SpecialCells(xlConstants).ClearContents
end Sub
 
G

Gord Dibben

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
 
S

smck

Hi Gord, Thanks a million for your help. This macro worked like a dream--it
is exactly what I wanted. Take care,

smck
 
Top