Delete text from cell containing text and numbers?

  • Thread starter Thread starter index
  • Start date Start date
I

index

Hi,


A list of product codes that i use includes some cells containing jus
numbers and other cells containing numbers and text.

Does anyone know how to create a macro that when run will search th
selection and when it finds a cell containing both text and numbers i
will remove the text from the cell leaving the numbers behind? Is thi
possible?

Any help would be much appreciate
 
Try something like

Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues).Delete shift:=xlUp



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Public Sub StripAllAZs()
''strips out everything except numbers
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
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
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Gord Dibben XL2002
 
Thanks for the help, however a small part of it is not working...

When I run it the lines
"Selection.Replace What:=" ", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I'm being told there is either a Expected named parameter or a Synta
error. Can anyone help me fix this please??
 
You may be the victim of line-wrapping.

Try....

"Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Or

"Selection.Replace What:=" ", Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

The <sp>_ is a line-continuation character. The code is part of one
continuous line.

Gord
 
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

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
 
Thanks very much for your help. However, although i am no longer gettin
any error messages, the macro is not doing anything to my data!

I am trying to remove any text from a cell, ie

abc123
123abc
ab123c

becomes...

123
123
123

Any further help would be much appreciated (apologies if this is
simple task being made difficult by a simple user!!!
 
Both Gord and Paul's macros work for me.

Here a formula solution that would work for your examples (only one
occurrence of numbers)

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter, copy down and later copy and paste
special as values in place

However this is much easier to do using VBA and both the solutions you
received from Gord and Paul works
for me

--

Regards,

Peo Sjoblom

index said:
Thanks very much for your help. However, although i am no longer getting
any error messages, the macro is not doing anything to my data!

I am trying to remove any text from a cell, ie

abc123
123abc
ab123c

becomes...

123
123
123

Any further help would be much appreciated (apologies if this is a
simple task being made difficult by a simple user!!!)


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Index

Don't know who you are responding to. You have a couple of suggestions from
myself and at least one other.

If myself....code works fine for me.

Select the cell(s) with abc123 and 123abc then run the macro.

Is it possible your text is generated via formula? Like =A1 & A2 or similar?
If so, the code will not work.

Copy>Paste Special>Values>OK would get rid of any formulas.

Try the code that Paul provided. It also works for me.

Gord
 
Back
Top