vba extract only numbers in next row

[dushyantgupta111]

Hi,

I have been trying to extract only numbers in the next row.

actual result as follow:

The result I want is
[TABLE="width: 134"]
<tbody>[TR]
[TD]
abc125
[/TD]
[TD="align: right"]125
[/TD]
[/TR]
[TR]
[TD]bcd121
[/TD]
[TD="align: right"]121
[/TD]
[/TR]
[TR]
[TD]111aaa
[/TD]
[TD="align: right"]111
[/TD]
[/TR]
</tbody>[/TABLE]

While the code works as:

[TABLE="width: 134"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]abc125
[/TD]
[TD="align: right"]125
[/TD]
[/TR]
[TR]
[TD]bcd121
[/TD]
[TD="align: right"]125121[/TD]
[/TR]
[TR]
[TD]111aaa
[/TD]
[TD="align: right"]125121111[/TD]
[/TR]
</tbody>[/TABLE]

Sub onlynumm()
Dim onlynum As String
Dim valuee As String

For Each cell In Selection
    Dim res As String
    Dim i As Integer
    valuee = cell.Value
    For i = 1 To Len(valuee)
        If IsNumeric(Mid(valuee, i, 1)) = True Then
            res = res + Mid(valuee, i, 1)
        End If
    Next
onlynum = res
cell.Offset(0, 1).Value = onlynum
Next
End Sub

 

[friel300]

Sub onlynumm()
Dim onlynum As String
Dim valuee As String


For Each cell In Selection
    Dim res As String
    Dim i As Integer
    valuee = cell.Value
    For i = 1 To Len(valuee)
        If IsNumeric(Mid(valuee, i, 1)) = True Then
            res = res + Mid(valuee, i, 1)
        End If
    Next
onlynum = res
cell.Offset(0, 1).Value = onlynum
[B]res = Empty[/B]
Next
End Sub

you just need to clear the res variable. Then it should work just fine.

 

[Rick Rothstein]

Assuming your numbers are either at the beginning or end of the text (as you examples show), then this macro should also work...

[table="width: 500"]
[tr]
	[td]Sub GetNumbers()
  Dim cell As Range, Txt As String
  For Each cell In Selection
    Txt = cell.Value
    If Len(Txt) Then
      If Txt Like "#*" Then
        cell.Offset(, 1).Value = Val(Txt)
      Else
        cell.Offset(, 1).Value = Mid(Txt, InStr(Txt, StrReverse(Val(StrReverse(Txt)))))
      End If
    End If
  Next
End Sub[/td]
[/tr]
[/table]

 

[dushyantgupta111]

Sub onlynumm()
Dim onlynum As String
Dim valuee As String


For Each cell In Selection
    Dim res As String
    Dim i As Integer
    valuee = cell.Value
    For i = 1 To Len(valuee)
        If IsNumeric(Mid(valuee, i, 1)) = True Then
            res = res + Mid(valuee, i, 1)
        End If
    Next
onlynum = res
cell.Offset(0, 1).Value = onlynum
[B]res = Empty[/B]
Next
End Sub

you just need to clear the res variable. Then it should work just fine.

Thanks friel300, it worked.