Strip non numeric characters from a cell

  • Thread starter Thread starter TraciAnn
  • Start date Start date
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!
 
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
 
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]
 
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
 
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)%))
 
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
 
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
 
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.
 
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
 
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...
 
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...
 
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!!
 
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
 
Back
Top