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

Create a VBA module in your worksheet with the following code. To execute, use the formula "=getvalue(A1)" (substitute A1 for whatever).

Function getvalue(selection As Variant) As Variant
Dim i As Integer
Dim charnum As Integer
charnum = Len(selection)
Dim newstr As Variant
newstr = selection

i = 0
Do
i = i + 1
If IsNumeric(Mid(selection, i, 1)) = False Then newstr = Replace(newstr, Mid(selection, i, 1), "")

Loop While i < charnum

getvalue = newstr
End Function

"smck" <[email protected]>ha scritto nel messaggio
[email protected]

> 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

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
--
Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

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" <[email protected]> wrote in message news:[email protected]...
> 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

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<at>tarkon.ee
Arvi Laanemets


"smck" <[email protected]> wrote in message
news:[email protected]...
> 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

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

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

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" <[email protected]>
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

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" <[email protected]>
> 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
>
>