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

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