If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Modify "Remove Dots" function
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 |
#2
|
|||
|
|||
Modify "Remove Dots" function
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 |
#3
|
|||
|
|||
Modify "Remove Dots" function
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 |
#4
|
|||
|
|||
Modify "Remove Dots" function
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 macro --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|