Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting the data according to the number of cell | New Users to Excel | |||
Extracting number from Text and () | Excel Discussion (Misc queries) | |||
Help!!! Extracting a number to line up with a different number | Excel Discussion (Misc queries) | |||
Extracting a 'number' from text | Excel Discussion (Misc queries) | |||
Extracting the number from a text | Excel Discussion (Misc queries) |