Frank:

Thanks so much!!! This makes really sense!!!

As always, thanks so much for your help.

Tom


"Frank Kabel" wrote in message
...
Hi Tom
a simple solution: change the formula to
=IF(A1"",IF(RIGHT(A1,1)=".",LEFT(A1,LEN(A1)-1),A1),"")

Another approach would be the following macro (invoked on your target
sheet):
sub change_dots()
dim rng as range
dim lastrow as long
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
set rng = range(cells(1,"D"),cells(lastrow,"D"))
rng.formulaR1C1 =
"=IF(R[0]C1"""",IF(RIGHT(R[0]C1,1)=""."",LEFT(R[0]C1,LEN(R[0]C1)-1),R
[0]C1),"""")"
end sub

--
Regards
Frank Kabel
Frankfurt, Germany

Tom Bock wrote:
I use the function below in order to remove "trailing dots" from
tasknumbers. For instance, it changes "1.1.1." into "1.1.1"

=IF(RIGHT(A1,1)=".",LEFT(A1,LEN(A1)-1),A1)

The function above works well.

However, I need to modify it so that I can use the function in a
macro
without creating "0s" if there are no values in column A.

Here's what I'm doing....

1. Record macro
2. Select the worksheet that contains various functions
3. Select the function above and copy function
4. Go back to the worksheet that contains the task numbers
5. Move cursor into cell D1
6. Paste function
7. Highlight entire column D
8. Use the "Fill Down" feature (which will remove all trailing dots
from all task numbers -- where applicable)

Again, all of this works fine except that I may only have task
numbers in cells A1:A20 (next time I might have task numbers from
A1:150). So, by highlighting the entire column (step 7) I will
remove the trailing dots of all task numbers (no matter how many I
have each time).

The problem is though that -- in this example -- I have "zeros"
beyond cell D20 for the rest of column D and "zeros" beyond cell D150
during the 2nd time around.

Here's what I need... something that will allow either:
- Fill down values of the function only where there are values in
column A, or
- if function cannot be modified, use "some feature" to replace all
"0s" in column D with ""


I hope this make sense!?!?

Thanks for any input!!!

Tom