M
martinjf
How can I get the numeric value from the cell: " $4.57 dividend"
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
C
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
ability to delete all alpha characters as one of their many fine
features............
Vaya con Dios,
Chuck, CABGx3
B
BenjieLop
martinjf said: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.
B
Biff
Hi!
Are the forward slashes a regional setting?
Try this:
=LEFT(A1,FIND(" ",A1)-1)*1
Biff
=LEFT(A1,FIND(\" \",A1)-1)
Are the forward slashes a regional setting?
Try this:
=LEFT(A1,FIND(" ",A1)-1)*1
Biff
M
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?
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?
C
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
=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
R
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