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

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

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

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