VB Right

[Suzanne]

I need a fix to select only the 4 right digits in cells that are not empty in
a column (Range J2:??)

Thanks -- Suz

 

[Rick Rothstein \(MVP - VB\)]

I need a fix to select only the 4 right digits in cells that are not

empty in a column (Range J2:??)

I'm not entirely clear on the meaning of "fix" and "select" as you used them
in your question. This formula...

=RIGHT(J2,4)

will return up to the right-most 4 characters (that is, from zero to four
characters depending on the cell's content) in a cell (J2 for the above
example usage) and can be copied down to handle the other cells in the
column. Is that what you are looking for?

Rick

 

[Joel]

Sub fix_ColumnJ()

LastRow = Cells(Rows.Count, "J").End(xlUp).Row
For RowCount = 2 To LastRow
data = Trim(Range("J" & RowCount))
'get last four didgits of text
If Len(data) >= 4 Then
data = Trim(Right(data, 4))
End If
'convert to number if necessary
'comment out if not necessary
If IsNumeric(data) Then
Number = Val(data)
End If
Range("J" & RowCount) = Number
Next RowCount
End Sub