extract value

[martinjf]

How can I get the numeric value from the cell: " $4.57 dividend"

 

[CLR]

ASAP Utilities, a free add-in available from www.asap-utilities.com has the
ability to delete all alpha characters as one of their many fine
features............

Vaya con Dios,
Chuck, CABGx3

 

[BenjieLop]

How can I get the numeric value from the cell: " $4.57 dividend"

See if this works for you ...

=LEFT(A1,FIND(\" \",A1)-1)


Regards.

 

[Biff]

Hi!

=LEFT(A1,FIND(\" \",A1)-1)

Are the forward slashes a regional setting?

Try this:

=LEFT(A1,FIND(" ",A1)-1)*1

Biff

 

[martinjf]

Hi BenjieLop

The formula you posted: =LEFT(A2,FIND(" ",A2)-1) worked for "4.57
Dividend" , but I find that some of the cell values contain an
additional space between the $ and the first number, e.g. "$ 0.57
Dividend. What would be the correct formula for that?

 

[CLR]

How about..

=LEFT(A2,LEN(A2)-9)

And if you wanted numbers instead of TEXT, use this and format for
currency.........

=LEFT(A1,LEN(A1)-9)*1


Vaya con Dios,
Chuck, CABGx3

 

[Ron Rosenfeld]

How can I get the numeric value from the cell: " $4.57 dividend"

If there is going to be considerable variability in the precise contents of the
cell, you might be better off with a User Defined Function (UDF) written in
visual basic.

To enter it:

<alt-F11> opens the VB Editor.
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF, in some cell enter the formula:

=GetVal(A1)

where A1 contains your string.

========================
Function GetVal(str) As Double
Dim i As Long
Dim temp As Variant

For i = 1 To Len(str)
temp = Val(Mid(str, i, 255))
If temp <> 0 Then
GetVal = temp
Exit Function
End If
Next i

End Function
======================


--ron