I have converted the below macro from function in to sub. In a Function, it returns the correct values and texts in the respected Columns,but not if it is a Sub Procedure. I am sure something is missing from the sub macro, but I am unable to figure it out.

For e.g, the cell is in Column A is "abcdef123xyz" , the value returns in Column B is 1.23123E+34, instead of returning 123. The same for text1.23123E+34

Please help.



Sub numer()
Dim c As Range, t As String, K As Long, tmpText As String, n As Long
t = ""
For Each c In Range("A1:A12").Cells
tmpText = c.Value
For n = 1 To Len(tmpText)
If IsNumeric(Mid(tmpText, n, 1)) Then
t = t & Mid(tmpText, n, 1)
End If
Next n
For K = 1 To 12
Range("b" & K) = Val(t)
If Not IsNumeric(Mid(tmpText, n, 1)) Then
t = t & Mid(tmpText, n, 1)
End If
Range("B" & K).Offset(0, 1) = t
Next K
Next c

End Sub