Remove all Characters Remains only Numeric Data

  • Thread starter Thread starter Hardeep kanwar
  • Start date Start date
H

Hardeep kanwar

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
 
'Hardeep kanwar[_2_ said:
;953955']Hello Experts

I have Mobile No. in Column A1:A300000, But the Problem is that in Man
Cell
there are Unused and Special Character like Dash,comma,Slash, Dote
Line.
Slash Lines in Some Cell i have Mobile No. like A9810332270 o
9810332270F

How can i delete these Data and Remains only Numeric Data

Thanks in Advance

Hardeep Kanwar
Click to expand...

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 bes

+-------------------------------------------------------------------
|Filename: Keep_only_numeric function.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=144
+-------------------------------------------------------------------
 
Hi,

Try this ARRAY formula. See below on how to enter it and then drag down.
Originally posted by Lars-Ã…ke Aspelin

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($A$1:$A$300)-1)*ISNUMBER(-MID("01"&A1,ROW($A$1:$A$300),1)),ROW($A$1:$A$300))+1,1),10^(300-ROW($A$1:$A$300))),2,300)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.



--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
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
Click to expand...

Solution from Harlan Grove
A1: Your mixed string

First, create a Named Formula
Names in Workbook: Seq
Refers to: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

This ARRAY FORMULA
(committed with CTRL+SHIFT+ENTER, instead of just ENTER)
removes ALL non-numerics from a string.

In sections, for readability:

B1: =SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)*
10^MMULT(-(seq<TRANSPOSE(seq)),-ISNUMBER(1/(MID(A1,seq,1)+1)))))
--ron
 
Thanks It Works Perfectly

Thanks Again

מיכ×ל (מיקי) ×בידן said:
To remove ALL(!) Non-Numeric data from a string try the following UDF.
If cell A1 contains: A123BCD456EF789G and in B1 you'll type =GN(A1)
the UDF will return: 123456789
*** UDF should be placed in a Module ***
-----------------------------
Function GN(Rng)
For N = 1 To Len(Rng)
If IsNumeric(Mid(Rng, N, 1)) Then GN = GN & Mid(Rng, N, 1)
Next
End Function
Click to expand...
 
Back
Top