Format function...I think

  • Thread starter Thread starter roma92
  • Start date Start date
R

roma92

I have a simple function request, (I think) I have a column with ove
10,000 cell entries of account numbers. Although its "Supposed" to b
strictly numbers, over time people have entered "-" or letters. I nee
to write a function/formula that will take column J data and move i
over to column k carrying over only the numbers and deleting all else


Is this possible?
Thank you ahead of time for any assistance
 
If the only options are real number and text you can select the range, press
F5, click special,
select constants and uncheck everything except number, click OK. Copy and
paste..
Otherwise post example of number that you want to keep and how the crap
looks that
you don't want
 
Hi
one way: use the following UDF:
Public Function clear_chars(rng As Range)
Dim retvalue
Dim char_count
Dim source_value

source_value = rng.Value
For char_count = 1 To Len(source_value)
If IsNumeric(Mid(source_value, char_count, 1)) Then
retvalue = retvalue & Mid(source_value, char_count, 1)
End If
Next
clear_chars = --retvalue
End Function

e.g.
=CLEAR_CHARS(A1)
in cell J1 and copy down. after this copy column c and insert it again
as Values (edit - Paste Special)
 
roma

Macro to remove(in place) all but numerics.

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


Gord Dibben Excel MVP
 
Thank you all very much you are life (and time) savers. I esp liked tha
macro, great stuff!
 
Thank you all very much you are life (and time) savers. I esp liked tha
macro, great stuff!
 
Back
Top