extracting just numeric value out from alphaneumeric cell

[Fam via OfficeKB.com]

Is there any formula to extract just neumeric value out from a cell which has
both text and a numerial values?
for example 10MM, and I just like to extract out 10 in different cell.
I have tried left function, but it is specific due to range. So if I put
=left(a2, 2), it will spit out 10 but what if the value is 2, or 750.
Any help will be appreciated.

 

[Guest]

Hi

Is there anything 'static' in all of the values? Do they all end in MM? Do
they all have two letters after the number? Are they always 4 characters
long?

Andy.

 

[CLR]

ASAP Utilities, an Excel Add-in available free from www.asap-utilities.com
has a feature that will "delete all text-characters in the selection". Just
copy your data to a helper column and apply the feature.

hth
Vaya con Dios,
Chuck, CABGx3

 

[Fam via OfficeKB.com]

Thanks for the reponse. No not all of them are four character long. They
varies from 4 to 7. But they all have MM.

wrote:

Hi

Is there anything 'static' in all of the values? Do they all end in MM? Do
they all have two letters after the number? Are they always 4 characters
long?

Andy.

Is there any formula to extract just neumeric value out from a cell which
has

[quoted text clipped - 3 lines]

=left(a2, 2), it will spit out 10 but what if the value is 2, or 750.
Any help will be appreciated.

 

[Guest]

So you could use something like this:
=VALUE(LEFT(A2,LEN(A2)-2))

Andy.

Thanks for the reponse. No not all of them are four character long. They
varies from 4 to 7. But they all have MM.

wrote:

Hi

Is there anything 'static' in all of the values? Do they all end in MM? Do
they all have two letters after the number? Are they always 4 characters
long?

Andy.

Is there any formula to extract just neumeric value out from a cell
which
has

[quoted text clipped - 3 lines]

=left(a2, 2), it will spit out 10 but what if the value is 2, or 750.
Any help will be appreciated.

 

[Fam via OfficeKB.com]

worked like a charm...thx

wrote:

So you could use something like this:
=VALUE(LEFT(A2,LEN(A2)-2))

Andy.

Thanks for the reponse. No not all of them are four character long. They
varies from 4 to 7. But they all have MM.

[quoted text clipped - 14 lines]