A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Modify "Remove Dots" function



 
 
Thread Tools Display Modes
  #1  
Old March 15th, 2004, 09:55 PM
Tom Bock
external usenet poster
 
Posts: n/a
Default 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  
Old March 15th, 2004, 10:22 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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  
Old March 15th, 2004, 10:25 PM
Tom Bock
external usenet poster
 
Posts: n/a
Default 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  
Old March 15th, 2004, 10:28 PM
mudraker
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:48 PM.


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