Removing Non-Numeric Characters

[GlenS]

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

 

[Paul Sheppard]

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

 

[GlenS]

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

 

[Paul Sheppard]

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

 

[GlenS]

Thanks again - worked fine.

Glen

 

[Guest]

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

Stefi