extracting a number
 

I have a column (A) of 300+ rows, with 10 digits in each cell. I need to
remove the last digit of each cell.

I tried =LEFT(A2,9) on column B and then copied the fomula to the last row,
but when I deleted column A, coumn B gave me a #REF! error. Is there a way to
convert those number in column B?

Or another way to do this without the use of =LEFT function?

TIA

Hernan.

extracting a number
 

PasteSpecial - Values... when you past back to column A. Otherwise you are
pasting a formula that is referencing the column to the left of A which does
not exist...
--
HTH...

Jim Thomlinson


"Hernan" wrote:

I have a column (A) of 300+ rows, with 10 digits in each cell. I need to
remove the last digit of each cell.

I tried =LEFT(A2,9) on column B and then copied the fomula to the last row,
but when I deleted column A, coumn B gave me a #REF! error. Is there a way to
convert those number in column B?

Or another way to do this without the use of =LEFT function?

TIA

Hernan.

extracting a number
 

Hernan,

Maybe this will accomplish your task without using the "LEFT" function

Counter = 1 ' Set the value to the row number of the cell containing the
first value
Do Until ActiveSheet.Cells(Counter,1) = "" ' This only works if there
is a blank cell after your last value and the values are in column A
ActiveSheet.Cells(Counter,1).Value =
INT(ActiveSheet.Cells(Counter,1)/10)
Counter = Counter+1
Loop

If you want column A to retain the original value and column B to contain
the cell value without the last digit, then the 1st line in the DO loop
should read

ActiveSheet.Cells(Counter,2).Value = INT(ActiveSheet.Cells(Counter,1)/10)

Good luck.

Boog

"Hernan" wrote in message
...
I have a column (A) of 300+ rows, with 10 digits in each cell. I need to
remove the last digit of each cell.

I tried =LEFT(A2,9) on column B and then copied the fomula to the last
row,
but when I deleted column A, coumn B gave me a #REF! error. Is there a way
to
convert those number in column B?

Or another way to do this without the use of =LEFT function?

TIA

Hernan.

extracting a number
 

Hernan,

Maybe this will accomplish your task without using the "LEFT" function

Counter = 1 ' Set the value to the row number of the cell containing the
first value
Do Until ActiveSheet.Cells(Counter,1) = "" ' This only works if there
is a blank cell after your last value and the values are in column A
ActiveSheet.Cells(Counter,1).Value =
INT(ActiveSheet.Cells(Counter,1)/10)
Counter = Counter+1
Loop

If you want column A to retain the original value and column B to contain
the cell value without the last digit, then the 1st line in the DO loop
should read

ActiveSheet.Cells(Counter,2).Value = INT(ActiveSheet.Cells(Counter,1)/10)

Good luck.

Boog

"Hernan" wrote in message
...
I have a column (A) of 300+ rows, with 10 digits in each cell. I need to
remove the last digit of each cell.

I tried =LEFT(A2,9) on column B and then copied the fomula to the last
row,
but when I deleted column A, coumn B gave me a #REF! error. Is there a way
to
convert those number in column B?

Or another way to do this without the use of =LEFT function?

TIA

Hernan.

extracting a number
 

Hernan,

Maybe this will accomplish your task without using the "LEFT" function

Counter = 1 ' Set the value to the row number of the cell containing the
first value
Do Until ActiveSheet.Cells(Counter,1) = "" ' This only works if there
is a blank cell after your last value and the values are in column A
ActiveSheet.Cells(Counter,1).Value =
INT(ActiveSheet.Cells(Counter,1)/10)
Counter = Counter+1
Loop

If you want column A to retain the original value and column B to contain
the cell value without the last digit, then the 1st line in the DO loop
should read

ActiveSheet.Cells(Counter,2).Value = INT(ActiveSheet.Cells(Counter,1)/10)

Good luck.

Boog

"Hernan" wrote in message
...
I have a column (A) of 300+ rows, with 10 digits in each cell. I need to
remove the last digit of each cell.

I tried =LEFT(A2,9) on column B and then copied the fomula to the last
row,
but when I deleted column A, coumn B gave me a #REF! error. Is there a way
to
convert those number in column B?

Or another way to do this without the use of =LEFT function?

TIA

Hernan.

extracting a number
 

If the idea is to extract the last digit of your 10-digit number
(assuming they are all uniformly so), simply use the the RIGH
function: no need for code.

In Column B, *=Right(A2,1) and fill down*


Before you delete Colum A, make sure the formulas in Column B ar
converted to numbers. You do this by highlighting Column B,
PasteSpecial-----Values. You were probably getting #REF! error by no
performing this conversion

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=38697

extracting a number
 

Thanks to all that replied.

Jim got it first though.

Thanks Jim!


"Jim Thomlinson" wrote:

PasteSpecial - Values... when you past back to column A. Otherwise you are
pasting a formula that is referencing the column to the left of A which does
not exist...
--
HTH...

Jim Thomlinson


"Hernan" wrote:

I have a column (A) of 300+ rows, with 10 digits in each cell. I need to
remove the last digit of each cell.

I tried =LEFT(A2,9) on column B and then copied the fomula to the last row,
but when I deleted column A, coumn B gave me a #REF! error. Is there a way to
convert those number in column B?

Or another way to do this without the use of =LEFT function?

TIA

Hernan.