Excel XP remove the last digit from a column of numbers

Posted on 2003-03-31
Medium Priority
Last Modified: 2010-08-05
I have a column containing several hundred customer numbers in Excel XP. I want to get rid of the last digit in each number (there are currently 7 digits long and I want to make them 6). How do I do this?
Question by:jghentley
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 44

Expert Comment

ID: 8238937
Hello jghentley,

you could write your own VBA function which does what you did with the excel functions like

-first make a copy of your workbook and then proceed
-open the vb editor with alt+f11
-insert a new module
-then paste this code

Option Explicit

Function StripLastChar(Byval s as string) as string
end function

and call it in a loop like

Public sub RunMyfunction()
Dim c as range
For each C in selection
  'test for blank
  if c.value <> "" then
    'run function
    c.value = striplastchar(c.value)
  end if
next c
End sub

-now save this
-return to your sheet
-select all cells you want to run this funtion on
-then choose tools|macros|macro|RunMyfunction|run
-this will strip all last chars for the selected cells

LVL 18

Accepted Solution

Curt Lindstrom earned 1000 total points
ID: 8238987
Say your column is A1 to A500. In, let's say column B,  enter =left(A1,6) in B1. Copy B1 to B2 until B500.
To alter the numbers in the A column copy B column to A and use Paste Special and copy values. The B column can now be deleted.

BR/ Curt

Author Comment

ID: 8240556
Nice and simple. Works great. Thank you.
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 8242732
Thanks for the points!

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question