Remove Characters From String

[saltkev]

I have a column of code numbers lets say A1 - A5000, each of these code numbers will vary in length, so I am looking to remove the last 12 carracters of each code number. I need to use VBA not Spread sheet formula.


Many Thanks in Advance



Saltkev

 

[JackDanIce]

What happens if a code number is 12 or fewer characters long? Assuming you want it left alone, try:

Sub RemoveLast12()

Dim i as Long

Application.ScreenUpdating = False

For i = 1 to Range("A" & Rows.Count).End(xlUp).Row
  If Len(Range("A" & i)) > 12 Then Range("A" & i) = Left(Range("A" & i), Len(Range("A" & i)) - 12)
Next i

Application.ScreenUpdating = True
End Sub

 

[saltkev]

Cracking Thanks Very Much

 

[JackDanIce]

Glad it helps

Slight improvement may be:

Sub RemoveLast_j()
Dim i As Long
Const j As Long = 12
Application.ScreenUpdating = False
For i = 1 To range("A" & Rows.Count).End(xlUp).Row
  If Len(range("A" & i)) > j Then range("A" & i) = Left(range("A" & i), Len(range("A" & i)) - j)
Next i
Application.ScreenUpdating = True
End Sub

Where you can change the red 12 to whatever number you want to be the number of characters you remove from the right of each cell.

I've yet to get to grips with coding with arrays, but you could probably get some even faster code if arrays were used