Remove Non-Numeric Characters from Cell

[gurs]

I am trying to come up with a formula (prefer to use stock Exce
functions rather than VBA script) to remove non-numeric characters fro
a cell, leaving only the numbers. For example, if cell A1 contain
"$6,800 + ", I would like a formula to enter into cell B2 such that th
result for cell B2 reads "6800". I have searched past posts bu
nothing seems on point. Thanks

 

[Bob Phillips]

I think this is too complex. There are too many characters that might need
removing, and too many instances in the string, and as a formula has no
looping capability, I thin k that this is not possible.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[gurs]

*I think this is too complex. *

If it's too complex to do using stock Excel functions, is there a wa
to do it using a custom function? What I'd really like to avoid is
macro. Thanks

 

[Debra Dalgleish]

If there are a limited number of non-numeric characters to remove, you
could use the substitute function. For example, to remove plus signs and
Less Than signs:

=SUBSTITUTE(SUBSTITUTE(B3,"+",""),"<","")+0

 

[CLR]

For the example given...........

=LEFT(A1,(FIND(("+"),A1)-1))*1

Vaya con Dios,
Chuck, CABGx3

 

[gurs]

*=SUBSTITUTE(SUBSTITUTE(B3,"+",""),"<","")+0*

When I try that formula, I get the "#VALUE!" error for every cell tha
contains non-numeric characters. In other words, for every cell tha
has a character other than a number, your formula returns an error. I
I eliminate the "+0" from the end of the formula, it eliminates th
error but Excel thinks that the result is text, not a number. I trie
using a 2nd column to multiply the results of the modified formula b
1, but still got the "#VALUE!" error

 

[Debra Dalgleish]

Did you revise the formula so it removes all text characters in your
sample? The example I gave only removes the plus sign and less than sign.

 

[Dave Peterson]

I'd use a couple of helper cells.

The first with Debra's suggestion in it (say in C3):
=SUBSTITUTE(SUBSTITUTE(B3,"+",""),"<","")

The second that checks to see if it's numeric:
=if(isnumber(-c3),--c3,c3)
or even
=if(isnumber(-c3),--c3,b3)
(to return the original value)