Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert a function in an entire column
I have a column with many values, and I need to add the int function to the
entire column. I cannot create another column, obtain the int value and delete the first column because there are several other cells in the workbook referring to the original column. Is there a way to insert the function in the column? Thanks in advance Regards Emece |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert a function in an entire column
Select the cells whose formulas you want to modify and run this simple macro:
Sub INT_Only() ' gsnuxx s1 = "=INT(" s2 = ")" For Each r In Selection If IsEmpty(r) Then Else v = r.Formula If Left(v, 1) = "=" Then v = Right(v, Len(v) - 1) End If r.Formula = s1 & v & s2 End If Next End Sub -- Gary''s Student - gsnu200829 "Emece" wrote: I have a column with many values, and I need to add the int function to the entire column. I cannot create another column, obtain the int value and delete the first column because there are several other cells in the workbook referring to the original column. Is there a way to insert the function in the column? Thanks in advance Regards Emece |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert a function in an entire column
You have some numbers on Sheet 1 in column, say, J like 12.34, 456.67 and
you want =INT(12.34) etc? Would you be happy with a process that converts 12.34 to 12 and 456.67 to 456 etc? On another sheet (say Sheet4) in J1 enter =INT(Sheet1!J1) and copy down the column as far as needed. Select all these formulas and Copy Move to J1 in Sheet1 and use Edit | Paste Special with Values specified. Now you have integers best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Emece" wrote in message ... I have a column with many values, and I need to add the int function to the entire column. I cannot create another column, obtain the int value and delete the first column because there are several other cells in the workbook referring to the original column. Is there a way to insert the function in the column? Thanks in advance Regards Emece |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert a function in an entire column
Instead of using:
v = Right(v, Len(v) - 1) You could use: v = mid(v, 2) (VBA's mid doesn't need that third parm. It's different than excel's =Mid().) Gary''s Student wrote: Select the cells whose formulas you want to modify and run this simple macro: Sub INT_Only() ' gsnuxx s1 = "=INT(" s2 = ")" For Each r In Selection If IsEmpty(r) Then Else v = r.Formula If Left(v, 1) = "=" Then v = Right(v, Len(v) - 1) End If r.Formula = s1 & v & s2 End If Next End Sub -- Gary''s Student - gsnu200829 "Emece" wrote: I have a column with many values, and I need to add the int function to the entire column. I cannot create another column, obtain the int value and delete the first column because there are several other cells in the workbook referring to the original column. Is there a way to insert the function in the column? Thanks in advance Regards Emece -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to apply a function to an entire column | Excel Worksheet Functions | |||
How do I apply my function to the entire column? | Excel Discussion (Misc queries) | |||
How do I apply a function to an entire column? | New Users to Excel | |||
use same cell w/in a function in a entire column. | Excel Discussion (Misc queries) | |||
Fill the entire column with a function | Excel Discussion (Misc queries) |