Remove all Characters Remains only Numeric Data

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,

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.


"Hardeep kanwar" wrote:

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

On Sat, 15 May 2010 20:37:01 -0700, Hardeep kanwar
wrote:

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

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(-(seqTRANSPOSE(seq)),-ISNUMBER(1/(MID(A1,seq,1)+1)))))
--ron