Vba assistance - extracting numbers out of a string

Question

QUESTION: Hello. I am currently working on a project that would allow me to extract numbers out of a Cell that contains Text. I would need that cell, once modified, to be an Integer. Unfortunately, I have no idea what I am doing wrong... Any help you could provide would really help!

In my code, I have attempted to go through a column, one cell at a time, retrieve the value of the given cell (unless it is empty), and modify the cell value - I only want the Right 5 characters of the cell to remain.

Code: Sub RemoveText() Dim Row As Long Dim Y As String Dim Z As String

For Row = 1 To Range("A2").End(xlUp).Row

   If Cells(Row, "A") <> "" Then
       Y = Cells(Row, "A")
       Z = Right(Y, 5)
       Cells(Row, "A") = Z
   End If

Next Row

End Sub

Before Macro:

 A

1 Hello11111 2 Be22222 3 0 <-- =Sum(A1:A2)

After Macro:

 A

1 11111 2 22222 3 33333

ANSWER: Robert

If you select A2, then hit the end key followed by the Up Arrow, where to you end up: In cell A1. So your code says

for Row = 1 to 1

I think you want to do

Sub ProcessData() Dim row As Long, z As String For row = 1 To Cells(Rows.Count, "A").End(xlUp).row

If Cells(row, "A") <> "" Then
   If Not Cells(row, "A").HasFormula Then
       z = Right("00000" & Cells(row, "A").Text, 5)
       If IsNumeric(z) Then
          With Cells(row, "A")
             .NumberFormat = "0"
             .Value = CDbl(z)
          End With
       Else
          Cells(row, "A").Value = z
       End If
   End If
 End If

Next End Sub

I added some checking because I don't know what your data looks like and I want to try to avoid errors.

---------- FOLLOW-UP ----------

QUESTION: Works perfectly! Thank you very much!

Ran into one more hiccup though. I am running the macro while Sheet1 is selected; however, the data is on Sheet2. Is there an easy way to point this macro at the correct Sheet?

Also if you can email me at - I have one more small question.

Answer

Robert,

Sub ProcessData() Dim row As Long, z As String With worksheets("Sheet2") For row = 1 To .Cells(Rows.Count, "A").End(xlUp).row If .Cells(row, "A") <> "" Then

  If Not .Cells(row, "A").HasFormula Then
      z = Right("00000" & .Cells(row, "A").Text, 5)
      If IsNumeric(z) Then
         With .Cells(row, "A")
            .NumberFormat = "0"
            .Value = CDbl(z)
         End With
      Else
         .Cells(row, "A").Value = z
      End If
  End If
End If

Next End With End Sub

I don't have any other knowledge about you other than that you identify yourself as Robert. (i.e. I don't have your email address)

If you want to contact me via email, then send it to

twogilvy AT msn dot com