Leave only decimal numbers in a cell

[Sun13Banjo]

I need to remove text from either the beginning or the end of a cell, leaving only a decimal number.

For example I would like the following values -
USD53.76 NAV
£14.2725XD
£1.467 NAVXD
£3.27469 DHLXD
EUR10.466 NAV

To equal -
53.76
14.2725
1.467
3.27469
10.466

I have tried to remove all text, but this also removes the decimal point and I need to keep that.

Unfortunately I can't upload a sample sheet, as restrictions at work will not allow it.

I am using Excel 2007.

Any ideas?

Thanks,

Sun

[duncandhu]

Hi Sun,

Create a new module and paste this code into it:

Please Login or Register  to view this content.

Now use the formula =GETNUMBER([cell]) to extract the number and decimal from the cell.

Thanks
Duncan

[newdoverman]

Copy your data into Word and use the Replace feature.

Enter[A-Z,a-z] in the Find field and leave the replace with field blank. Select Use Wildcards. Click Replace All. This will replace all alpha characters with nothing.

Copy the results back to Excel.

[Sun13Banjo]

Hi Sun,

Create a new module and paste this code into it:

Please Login or Register  to view this content.

Now use the formula =GETNUMBER([cell]) to extract the number and decimal from the cell.

Thanks
Duncan

Duncan, that is perfect!

I can't tell you how many hours of head scratching you've just saved me!

Many, many thanks.

Sun

[Sun13Banjo]

Newdoverman, that's what I call thinking outside of the box! Good solution, thanks.

Sun

[newdoverman]

You're welcome.

[jindon]

Other method and capable to handle negative number

=GetNumber(A1)

Please Login or Register  to view this content.

[Sun13Banjo]

Thanks Jindon. Loving the help here!