Quote:
Originally Posted by paulw793
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