Removing Excess Digits From Cells

  • Thread starter Thread starter Raquel666
  • Start date Start date
R

Raquel666

I have two lists of numbers that I'd like to use to join two tables in
Access.

One list has an extra "Check Digit" at the end.

I'd like to know how to remove the "Check Digit".

(It may not matter but one of the characters in the list of "numbers"
is a letter.)

I tried to "Format Cells" by typing in one less ###-##-#### but it did
not do anything.
 
Hi Raquel,

try using the formula =LEFT(E6;1) for a column next to the
list and use E6 for every cell... so you get rid of the
last digit.

Best

Markus
 
Raquel,

if your list of numbers is actually alpha based, =left will work, if
it is number based, try using =trunc(e6/10) with e6 being the reference
to the column where your original numbers are

HTH
Chris
 
Chris said:
Raquel,

if your list of numbers is actually alpha based, =left will work, if
it is number based, try using =trunc(e6/10) with e6 being the reference
to the column where your original numbers are

HTH
Chris
Click to expand...

Where do I type that formula in?
What does the number 6 have to do with it does that mean "remove the
sixth number from the left"?
Here's what the numbers look like and I have to get rid of the last
digit (the numbers are in column B):

100-02-018B0
100-02-020A8
100-04-021A3
100-04-034D2
100-04-03707
100-07-01100
100-07-019C6
100-08-002F6
100-08-004C1
100-08-004D9
100-08-015G9
 
Assume they start in B2 going down, put this formula in a help column

=LEFT(TRIM(B2),LEN(TRIM(B2))-1)

copy down the formula as long as needed by dragging the lower right corner
of the cell
with the mouse, then select the help formula range (high light),
copy, then do edit>paste special as values. Now you can copy and paste the
stripped values over the old if needed

--


Regards,

Peo Sjoblom
 
You could use "Text To Columns".
That way, the result is "actual " data, and not the return of a formula,
where the formula would have to be subsequently removed.

Select the data, then:
<Data> <Text To Columns>
Check "Fixed Width", then <Next>
Click in the "Data Preview" window to place the "break line" to the left of
the last character, then <Next>
Click in the second column of the "Preview" window to select it (black)
Then click on "Do Not Import Column"
You'll see the column header change to "Skip Column"
Then <Finish>

This *changes* your original datalist to the new configuration.

With your original data in Column A, if you would like to preserve the
original datalist, and perhaps move the new configuration to another column,
*before* you click the final <Finish>, change the default address in the
"Destination" box to say D1, or K1, or whatever.
 
IT WORKED! Waaaaaaaaaaaay Coooooooooooool! You guys are wizards.

THANKS!

Peo said:
Assume they start in B2 going down, put this formula in a help column
Click to expand...

I created a "help column" in column "C" and pasted the formula in the
formula bar then brought it down. (There are about 30,000 numbers in
the list.)
 
Back
Top