Removing last 2 digits on a number in Excel

[lermuseaux]

I have the following VBA macro so that on a 12 digit number in column A
on my spreadsheets, it keeps only the first 10 digits in the same cell.


What I want to do is change it so that it doesn't assume my data will
always be in column A, but whatever column I have highlighted. So if I
were to highlight all of Column C, it would perform the function all
the way down the column.

Dim rng As Range
For Each rng In Columns(1).SpecialCells(xlConstants, xlNumbers).Areas
rng.TextToColumns _
Destination:=rng, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(2, 9))
Next

Also, once in a while, the data I have will only have 11 digits in
which case I would want to remove the last 2 digits and add a zero to
the front of the remaining number keeping it at 10 digits total again.
Is there a way to accomplish this task in the same script?

Example of numbers I will have in the spreadsheet:

127647794701
97124764905

Number one I want to be: 1276477947
Number two I want to be: 0971247649

Thanks for any suggestions.....

 

[Ken Hudson]

Hi,
Here is one approach. When run, the macro will prompt the user for the
column number to be adjusted. As far as putting a zero in front of the
number, the macro will do that, but if the column is formatted as a number,
you won't see the zero....

Sub Shorten()
Dim ColID As Integer
Dim Iloop As Double
Dim NumRows As Double
ColID = InputBox("Enter column number you wish to convert.")
NumRows = Cells(65536, ColID).End(xlUp).Row
For Iloop = 1 To NumRows
If Len(Cells(Iloop, ColID)) = 12 Then
Cells(Iloop, ColID) = Left(Cells(Iloop, ColID), 10)
Else
If Len(Cells(Iloop, ColID)) = 11 Then
Cells(Iloop, ColID) = "0" & Left(Cells(Iloop, ColID), 9)
End If
End If
Next Iloop
End Sub

 

[Dave L]

Thanks so much Ken! That does exactly what I want it to do. My numbers are
all formatted as Text so even the one with only 11 digits formats properly.

Thanks again for your help! You have been invaluable )

 

[Ken Hudson]

You're welcome, Dave.
Glad it worked the way you wanted.