Removing an alpha characture from a column of numbers

K

K Drier

I have a column of numbers that end with an alpha character. I need to
remove the alpha character and I'd like to write a macro or formula to do it
automatically since the column is about 1000 numbers long.

I got the macro to work once, but it won't work again on the balance of the
column. Would someone please help me figure out what I need to do to make
this an automatic process so I can reuse it at a later date?

Thanks so much!
 
M

Mike H

Hi,

You can have a macri if you want but put this in an adjacent column and
double clivk the fill handle and it will fill down as far as there are data
in an adjacent column and remove the last character.

=LEFT(A1,LEN(A1)-1)+0

Mike
 
B

Bob Phillips

Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

.Cells(i, "A").Value = Val(Left$(.Cells(i, "A").Value,
Len(.Cells(i, "A").Value) - 1))
Next i
End With
 
K

K Drier

This worked Perfectly!!! Thank you so much!

Mike H said:
Hi,

You can have a macri if you want but put this in an adjacent column and
double clivk the fill handle and it will fill down as far as there are data
in an adjacent column and remove the last character.

=LEFT(A1,LEN(A1)-1)+0

Mike
 
R

Rick Rothstein

Given that the values being modified are numbers with any alpha characters
only at the end, you can shorten your code to this...

Dim LastRow As Long
Dim i As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
.Cells(i, "A").Value = Val(.Cells(i, "A").Value)
Next
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top