Modify "Remove Dots" function

  • Thread starter Thread starter Tom Bock
  • Start date Start date
T

Tom Bock

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
 
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
 
Frank:

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

As always, thanks so much for your help.

Tom


Frank Kabel said:
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 said:
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
Click to expand...
Click to expand...
 
Use a nested if statement

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


If you are doing this event often then look at using a macr
 
Back
Top