Invalid characters in cells

[Francis Ang]

I am trying to extract only the valid values from a cell and remove all the
invalid values, like â­ / # etc. For example, in cell A1, the value is
â­8930â­â­/â­. The length of the value is variable and the placement of the
invalid values are not fixed.

How do I extract only 8930?

I have tried, TRIM, LTRIM, RTRIM functions but it does not work!

Any help would be greatly appreciated.

 

[Mike H]

Hi,

As long as the numbers are in a single block then this should work

=LOOKUP(10^23,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Mike

 

[Bob Phillips]

Try

=SUBSTITUTE(A1,CHAR(160),"")

 

[Francis Ang]

Thanks for the quick response Mike. I'll have a go with your suggestion.

Thanks again.

 

[Francis Ang]

Thanks you for the quick response, Bob.

Thank you very much.

 

[Rick Rothstein]

If your "invalid characters" **always** follow the number (that is, you have
a number and then non-digits following it), then you can just use the Val
function...

TextValue = "8930/#dfs"
NumberOnly = Val(TextValue)

If your numbers can have decimal points in them, then the above Val
suggestion will only work if your regional setting for the decimal point is
a "dot" character.

 

[Francis Ang]

Thanks for the tip, Rick.