Quote:
Originally Posted by Hardeep kanwar[_2_]
Hello Experts
I have Mobile No. in Column A1:A300000, But the Problem is that in Many Cell
there are Unused and Special Character like Dash,comma,Slash, Doted Line.
Slash Lines in Some Cell i have Mobile No. like A9810332270 or 9810332270F
How can i delete these Data and Remains only Numeric Data
Thanks in Advance
Hardeep Kanwar
|
hi Kanwar,
Warm Greetings, try this user defined function Keep_only_numeric(A1)
to get this, create module in VBE (Alt+F11)
and insert the below code in the module
'created by bala sesharao
'created on 5/16/2010
Function keep_only_numeric(varData)
For intTemp = 1 To Len(varData)
If IsNumeric(Mid(varData, intTemp, 1)) Or _
Mid(varData, intTemp, 1) = Chr(32) Then
keep_only_numeric = keep_only_numeric & Mid(varData, intTemp, 1)
ElseIf Mid(varData, intTemp, 1) = "." Then
If Mid(varData, intTemp, 2) Like ".#" Then _
keep_only_numeric = keep_only_numeric & Mid(varData, intTemp, 1)
End If
Next
keep_only_numeric = WorksheetFunction.Trim(keep_only_numeric)
End Function
all the best