Strip non numeric characters from a cell

T

TraciAnn

Hello.

I have a list of contacts that I am trying to "clean-up" the data.

A column of phone numbers have been entered in a variety of different ways
(e.g. 555.555.5555; (555) 555-5555; Home: 555-5555; etc.)

How can I remove the non-numeric characters?

Thanks in advance!
 
R

Ron Rosenfeld

Hello.

I have a list of contacts that I am trying to "clean-up" the data.

A column of phone numbers have been entered in a variety of different ways
(e.g. 555.555.5555; (555) 555-5555; Home: 555-5555; etc.)

How can I remove the non-numeric characters?

Thanks in advance!
Click to expand...

Several ways:

Assuming your data is in A1.

==================================
From Harlan Grove:

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)))))
================================

Or you can use a UDF:

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=DigitsOnly(A1)

in some cell.

=================================
Option Explicit
Function DigitsOnly(str As String) As Double
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D+"
DigitsOnly = re.Replace(str, "")
End Function
=============================
--ron
 
L

Lori Miller

Maybe try one of these with Ctrl+Shift+Enter:

=NPV(-0.9,,IF(ISERR(MID(A1,COLUMN(A:IV),1)%),"",MID(A1,COLUMN(A:IV),1)%))

=NPV(-0.9,,IFERROR(MID(A1,COLUMN($A:$IV),1)%),"")) [in xl2007]
 
R

Ron Rosenfeld

Maybe try one of these with Ctrl+Shift+Enter:

=NPV(-0.9,,IF(ISERR(MID(A1,COLUMN(A:IV),1)%),"",MID(A1,COLUMN(A:IV),1)%))
Click to expand...

I tried some combos other than 55...

e.g.

123.456.7890 --> 987654321

(478) 123-4567 --> 7654321874
--ron
 
L

Lori Miller

You're right, i think it should be:

=NPV(-0.9,,IF(ISERR(MID(A1,256-COLUMN(A:IV),1)%),"",MID(A1,256-COLUMN(A:IV),1)%))
 
R

Ron Rosenfeld

Several ways:

Assuming your data is in A1.

==================================
From Harlan Grove:

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)))))
================================

Or you can use a UDF:

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=DigitsOnly(A1)

in some cell.

=================================
Option Explicit
Function DigitsOnly(str As String) As Double
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D+"
DigitsOnly = re.Replace(str, "")
End Function
=============================
--ron
Click to expand...

By the way, the above UDF returns the digits as a numeric value. If you want
to return the digits as a text string (which would retain leading zero's) then
change line two to read:

Function DigitsOnly(str As String) As String
--ron
 
R

Ron Rosenfeld

You're right, i think it should be:

=NPV(-0.9,,IF(ISERR(MID(A1,256-COLUMN(A:IV),1)%),"",MID(A1,256-COLUMN(A:IV),1)%))
Click to expand...

That seems to work. Neat technique!
--ron
 
T

TraciAnn

Awesome, Ron!!!

Thank you. Since the data was being scrubbed for an import I needed to
retain the numeric format, but thanks for the hint on the text version too.

Due to the tens of thousands of records it sure beat Find and Replace on the
large variety of characters.
 
R

Ron Rosenfeld

Awesome, Ron!!!

Thank you. Since the data was being scrubbed for an import I needed to
retain the numeric format, but thanks for the hint on the text version too.

Due to the tens of thousands of records it sure beat Find and Replace on the
large variety of characters.
Click to expand...

Glad to help. Thanks for the feedback.
--ron
 
T

TraciAnn

Lori,

Thanks! This worked beautifully too. After running Ron's solution, I tried
yours on a backup copy. They both worked as expected. I'll be keeping them
both stored in my library of helpful tips!

Thanks again!
--
TraciAnn


Lori Miller said:
Maybe try one of these with Ctrl+Shift+Enter:

=NPV(-0.9,,IF(ISERR(MID(A1,COLUMN(A:IV),1)%),"",MID(A1,COLUMN(A:IV),1)%))

=NPV(-0.9,,IFERROR(MID(A1,COLUMN($A:$IV),1)%),"")) [in xl2007]

TraciAnn said:
Hello.

I have a list of contacts that I am trying to "clean-up" the data.

A column of phone numbers have been entered in a variety of different ways
(e.g. 555.555.5555; (555) 555-5555; Home: 555-5555; etc.)

How can I remove the non-numeric characters?

Thanks in advance!
Click to expand...
Click to expand...
 
L

Lori Miller

Glad this was useful too.
Note Ron's comments - the 15 digit limitation applies to all
formulas returning numbers, otherwise use the text UDF.

TraciAnn said:
Lori,

Thanks! This worked beautifully too. After running Ron's solution, I tried
yours on a backup copy. They both worked as expected. I'll be keeping them
both stored in my library of helpful tips!

Thanks again!
--
TraciAnn


Lori Miller said:
Maybe try one of these with Ctrl+Shift+Enter:

=NPV(-0.9,,IF(ISERR(MID(A1,COLUMN(A:IV),1)%),"",MID(A1,COLUMN(A:IV),1)%))

=NPV(-0.9,,IFERROR(MID(A1,COLUMN($A:$IV),1)%),"")) [in xl2007]

TraciAnn said:
Hello.

I have a list of contacts that I am trying to "clean-up" the data.

A column of phone numbers have been entered in a variety of different
ways
(e.g. 555.555.5555; (555) 555-5555; Home: 555-5555; etc.)

How can I remove the non-numeric characters?

Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
J

jdazzo2

Hi All,

Need your help! I'm working in a worksheet that is 6 columns by 3,000 rows.. All of the rows have numeric values although some of the numbers have an* after that (ie. 391*). How can I remove this asterix so I just have thenumeric value in the cell? Otherwise, these cells don't roll-up in to my pivot. I've tried find/replace & format cell and can't seem to figure it out but there's gotta be a a way.

Thank you!!
 
C

Claus Busch

hi,

Am Wed, 20 Mar 2013 21:24:39 -0700 (PDT) schrieb (e-mail address removed):
Need your help! I'm working in a worksheet that is 6 columns by 3,000 rows. All of the rows have numeric values although some of the numbers have an * after that (ie. 391*). How can I remove this asterix so I just have the numeric value in the cell? Otherwise, these cells don't roll-up in to my pivot. I've tried find/replace & format cell and can't seem to figure it out but there's gotta be a a way.
Click to expand...

try it with Find/Replace
Find what: ~*
Replace with:


Regards
Claus Busch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula to remove non-numeric characters from a cell 2
Finding a Name on a list and returning all matching rows 2
Removing parentheses in a phone number 7
Removing duplicates from a list of addresses 1
how to change format of data already entered 5
Use of Wildcard characters with replace 2
formatting a column 4
making a phone number to be just 10 numbers 5

Top