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.

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&"0 123456789")),ROW($1:$10000))))

Mike

"Francis Ang" wrote:

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.

Try

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

--
__________________________________
HTH

Bob

"Francis Ang" wrote in message
...
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.

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

Thanks again.

"Mike H" wrote:

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&"0 123456789")),ROW($1:$10000))))

Mike

"Francis Ang" wrote:

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.

Thanks you for the quick response, Bob.

Thank you very much.

"Bob Phillips" wrote:

Try

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

--
__________________________________
HTH

Bob

"Francis Ang" wrote in message
...
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.

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.

--
Rick (MVP - Excel)


"Francis Ang" wrote in message
...
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.

Thanks for the tip, Rick.

"Francis Ang" wrote:

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.