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

On Thursday, August 30, 2012 7:19:25 AM UTC-5, E-on wrote:
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









--

E-on

Try this

Option Explicit
Sub getnuminstring()
Dim c As Range
Dim i As Long
Dim ms As String
Set c = ActiveCell
For i = 1 To Len(c)
If IsNumeric(Mid(c, i, 1)) Then ms = ms & Mid(c, i, 1)
Next i
MsgBox ms
End Sub