[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 677
  • Last Modified:

Excel XP remove the last digit from a column of numbers

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?
  • 2
1 Solution
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

Curt LindstromCommented:
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
jghentleyAuthor Commented:
Nice and simple. Works great. Thank you.
Curt LindstromCommented:
Thanks for the points!

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now