replace colum with last six characters in each row.

  • Thread starter Thread starter S Himmelrich
  • Start date Start date
S

S Himmelrich

Here is what I have and I'm getting an error in line 1:

Dim rngCells As Range
With ActiveSheet
1 For Each rngCells In Range("B3", Value.SpecialCells(xlLastCell))
rngCells.Value = Right(rngCells.Value, 6)
Next
End With
 
Dim rngCells As Range
With ActiveSheet
For Each rngCells In Range(Range("B3"),
Range("B3").SpecialCells(xlLastCell))
rngCells.Value = Right(rngCells.Value, 6)
Next
End With

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Sub extract_right_6()
Dim rng As Range
Set rng = Range(Range("B3"), Cells(Rows.Count, 2).End(xlUp))
For Each cell In rng
cell.Value = Right(cell.Value, 6)
Next
End Sub



Gord Dibben MS Excel MVP
 
This solution is triming some of the records that result then less
then 6 characters, every cell has this type of format - > 3180015155
002075. So perhaps removing the first 11 would be a better solution.
 
UPDATE: if I trim 8 instead of 6 it works....this must be something
with the original data.....
 
UPDATE: if I trim 8 instead of 6 it works....this must be something
with the original data.....
 
Maybe you have a couple of trailing spaces?

Trim them...............

Sub extract_right_6()
Dim rng As Range
Set rng = Range(Range("B3"), Cells(Rows.Count, 2).End(xlUp))
For Each Cell In rng
Cell.Value = Application.Trim(Right(Cell.Value, 6))
Next
End Sub


Gord
 
Back
Top