View Single Post
 
Old 01-27-2012, 09:41 AM
Catalin.B Catalin.B is offline Windows Vista Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Quote:
Originally Posted by paulw793 View Post
Is there anyway to add onto the second formula to only copy digits and not characters?
To extract numbers from text, best is to use a macro, like this:
(can be set to extract decimal, and/or negative numbres, with the optional arguments:
Code:
Function ExtrageNumar(rCell As Range, _
     Optional CuZecimale As Boolean, Optional Negative As Boolean) As Double


Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String, strNeg As String, strDec As String
Dim lNum As String
Dim vVal, vVal2
    
    sText = rCell
    If CuZecimale = True And Negative = True Then
        strNeg = "-"
        strDec = ","
    ElseIf CuZecimale = True And Negative = False Then
        strNeg = vbNullString
        strDec = ","
    ElseIf CuZecimale = False And Negative = True Then
        strNeg = "-"
        strDec = vbNullString
    End If

    iLoop = Len(sText)
            For iCount = iLoop To 1 Step -1
                vVal = Mid(sText, iCount, 1)
                If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
                    i = i + 1
                    lNum = Mid(sText, iCount, 1) & lNum
                        If IsNumeric(lNum) Then
                            If CDbl(lNum) < 0 Then Exit For
                        Else
                          lNum = Replace(lNum, Left(lNum, 1), "", , 1)
                        End If
                End If
                If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
            Next iCount

    ExtrageNumar = CDbl(lNum)

End Function
Then, in cell, use : =ExtrageNumar(E8;1;1) (second and third arguments are optional, and are used for negative and decimals)
note:
the code may need to be modified according to your regional settings:
on lines 13 and 16, strDec = "," is strDec = "." if this is your regional decimal delimiter
Reply With Quote