Removing Non-Numeric Characters
 

I have a list of numbers which contain both numbers & text characters.
(Eg: 123/456/7).

I need to match this list to another one which contains only numbers.
(Eg: 1234567).

Is there a formula I can use which allows me to extract only numerical
characters from a cell.

Many Thanks

Glen


--
GlenS
------------------------------------------------------------------------
GlenS's Profile: http://www.excelforum.com/member.php...o&userid=28028
View this thread: http://www.excelforum.com/showthread...hreadid=475327

GlenS Wrote:
I have a list of numbers which contain both numbers & text characters.
(Eg: 123/456/7).

I need to match this list to another one which contains only numbers.
(Eg: 1234567).

Is there a formula I can use which allows me to extract only numerical
characters from a cell.

Many Thanks

Glen

Hi Glen

Try Edit and Replace


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=475327

Find & Replace works for most of the characters except for a character
which appear at the front of some of the numbers (EG: '354859). I've
tried copying & pasting this character into the Find & Replace but it
doesn't work.

I think it's something that's been entered to force Excel to start
numbers with a 0.

Glen


--
GlenS
------------------------------------------------------------------------
GlenS's Profile: http://www.excelforum.com/member.php...o&userid=28028
View this thread: http://www.excelforum.com/showthread...hreadid=475327

GlenS Wrote:
Find & Replace works for most of the characters except for a character
which appear at the front of some of the numbers (EG: '354859). I've
tried copying & pasting this character into the Find & Replace but it
doesn't work.

I think it's something that's been entered to force Excel to start
numbers with a 0.

Glen

Hi Glen

The ' allows numbers to be enterred with leading zeros

To get rid of this in the next available column use this formula
=TRIM(A1), change the reference to suit, drag this down to the bottom
of the row, the copy paste special values over the column with the
formulae


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=475327

Thanks again - worked fine.

Glen


--
GlenS
------------------------------------------------------------------------
GlenS's Profile: http://www.excelforum.com/member.php...o&userid=28028
View this thread: http://www.excelforum.com/showthread...hreadid=475327

Use
=SUBSTITUTE(A1,"/","")
instead of A1 if A1 contains 123/456/7

Stefi

"GlenS" wrote:


I have a list of numbers which contain both numbers & text characters.
(Eg: 123/456/7).

I need to match this list to another one which contains only numbers.
(Eg: 1234567).

Is there a formula I can use which allows me to extract only numerical
characters from a cell.

Many Thanks

Glen


--
GlenS
------------------------------------------------------------------------
GlenS's Profile: http://www.excelforum.com/member.php...o&userid=28028
View this thread: http://www.excelforum.com/showthread...hreadid=475327