Remove Last Digit

  • Thread starter Thread starter JR
  • Start date Start date
J

JR

Hello,

I have a list of barcode numbers, and I need to be able to remove the last
digit, if possible, with a macro.

Example, current number:
23705101660

Needs to be:
2370510166

Thanks JR
 
If they're really numbers:

=int(a1/10)

if they're text masquarading as numbers:

=left(a1,len(a1)-1)

The top one will return a real number. The bottom one will still be text.
 
Hi JR,

JR said:
I have a list of barcode numbers, and I need to be able to remove the last
digit, if possible, with a macro.
Click to expand...


I would go with the solution proposed by Dave Peterson, but if you need a
macro, try:

Sub TrimEleventhDigit()
Dim rng As Range
Dim rw As Long
Dim cell As Range
' // Amend each of the 4 following Const values
Const BCodeLen As Long = 11 '<<== CHANGE
Const Col As String = "A" '<<==
CHANGE
Const FirstRow As Long = 1 '<<== CHANGE
Const ShtName As String = "MyBCodeShtName" '<<==CHANGE

Application.ScreenUpdating = False

With Sheets(ShtName)
rw = .Cells(Rows.Count, Col).End(xlUp).Row
Set rng = .Range(.Cells(FirstRow, Col), .Cells(rw, "A"))
End With

For Each cell In rng.Cells
With cell
If IsNumeric(.Value) Then
If Len(.Value) = 11 Then
.Value = Left(.Value, BCodeLen - 1)
End If
End If
End With
Next cell

Application.ScreenUpdating = True

End Sub
 
Are barcode numbers all the same length? if so then just use Data / Text To
Columns / Fixed width and set the break before the last character. Then
choose not to import the last column.
 
Back
Top