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.
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
Advertisement