macro

  • Thread starter Thread starter Gayla Vogel
  • Start date Start date
G

Gayla Vogel

I need to have a macro that checks a cell for text, if there is text,
strip it out, leave the number and move down a row. If it is a number,
move down a row.

I have over 23,000 records to clean! Help ASAP!!!
 
Hi Gayla,

Can you provide a few examples of cells that have text and
numbers. Are there specific numbers/text that should stay
and/or be stripped out? This information will be helpful
in creating a solution.

Thanks.
 
You don't say what format your mixed text and numbers are in, so
this will remove any non-numeric character from the cell and
concatenate the numbers:

Public Sub StripNonNumeric()
Dim rCell As Range
Dim i As Long
Dim sIn As String
Dim sOut As String

With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = False
End With
On Error Resume Next
For Each rCell In Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)
sOut = Empty
With rCell
sIn = .Text
For i = 1 To Len(sIn)
If Mid(sIn, i, 1) Like "[0-9]" Then _
sOut = sOut & Mid(sIn, i, 1)
Next i
.NumberFormat = "General"
.Value = sOut
End With
Next rCell
On Error GoTo 0
With Application
.Calculation = True
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
 
Back
Top