Removing an alpha characture from a column of numbers

[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!

 

[Gary''s Student]

=--LEFT(A1,LEN(A1)-1) and copy down

 

[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

 

[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 Drier]

This worked Perfectly!!! Thank you so much!

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

 

[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