Strings and numbers in cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to make Excel only recognise numbers in a cell when the cell
contains both numbers and a string?

lespal
 
The following UDF might do what you want it to. It loops through a string
value and concatenates numeric strings and returns the result as a long
integer.

Function ReturnNums(StringVal) As Long


Dim intLen As Integer
Dim i As Integer
Dim strReturnVal As String
Dim strChar As String

intLen = Len(StringVal)


For i = 1 To intLen
strChar = Mid(StringVal, i, 1)
If IsNumeric(strChar) Then strReturnVal = strReturnVal & strChar
Next i

If Len(strReturnVal) = 0 Then
ReturnNums = 0
Else
ReturnNums = CLng(strReturnVal)
End If


End Function
 
Back
Top