Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 82
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to apply a function to an entire column Barb @ Work[_2_] Excel Worksheet Functions 1 May 12th 08 07:29 PM
How do I apply my function to the entire column? jsthngn Excel Discussion (Misc queries) 1 January 23rd 06 04:21 PM
How do I apply a function to an entire column? didja New Users to Excel 1 November 23rd 05 06:04 AM
use same cell w/in a function in a entire column. collegeboy28 Excel Discussion (Misc queries) 2 November 22nd 05 03:46 AM
Fill the entire column with a function [email protected] Excel Discussion (Misc queries) 1 September 29th 05 11:26 AM


All times are GMT +1. The time now is 08:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"