I want to remove numbers but leave text in cells.

[D]

Hi, using Excel 2003,I'm pasting information into numerous cells of a
worksheet. Each cell is a mixture of text and numbers eg. (123 test example
3210 ), can I remove the numbers and just leave text.

regards,

 

[Jim Cone]

I believe that will require VBA.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"D"
<[email protected]>
wrote in message
Hi, using Excel 2003,I'm pasting information into numerous cells of a
worksheet. Each cell is a mixture of text and numbers eg. (123 test example
3210 ), can I remove the numbers and just leave text.

regards,

 

[Gary''s Student]

Sub cleanum()
s = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
For Each r In Selection
v = r.Value
For i = 0 To 9
v = Replace(v, s(i), "")
Next
r.Value = v
Next
End Sub

 

[Don Guillett]

try

Sub RemoveNumbers() 'Don
For Each c In Range("g2:g4")
ms = ""
For i = 1 To Len(c)
If Not Mid(c, i, 1) Like "[0-9]" Then ms = ms & Mid(c, i, 1)
Next i
'MsgBox ms
c.value=ms
Next c
End Sub

 

[Dave Peterson]

Another way to do it is to select the range to fix, then do 10 edit|Replaces.
Change 0 to (nothing), change 1 to (nothing), ... change 9 to nothing.

In code, it would look like:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim iCtr As Long

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No text constants in the Selection!"
Exit Sub
End If

For iCtr = 0 To 9
myRng.Replace What:=iCtr, _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub

 

[D]

Thanks to all for the vba solutions.

derek