| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help modifying a complex column of formulas
I have a column of about 1000 entries. It is a very long Concatenate
formula. Within the formula there is a reference to another column which I no longer want to include. Is there a way to modify all these entries to remove that reference? If it was a static value I would just do a search and replace but the part I want to remove is different on each row (for example, A1 on the first row, A2 on the second and so on, up to A1000. I want to remove them all) any ideas? Thanks in advance! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help modifying a complex column of formulas
Post your formula.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- wrote in message ups.com... I have a column of about 1000 entries. It is a very long Concatenate formula. Within the formula there is a reference to another column which I no longer want to include. Is there a way to modify all these entries to remove that reference? If it was a static value I would just do a search and replace but the part I want to remove is different on each row (for example, A1 on the first row, A2 on the second and so on, up to A1000. I want to remove them all) any ideas? Thanks in advance! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help modifying a complex column of formulas
Thiis is the formula:
=CONCATENATE(Master!$R$2,Master!$R$3,A2,Master!$R$ 4,Master! $C2,D3,Master!$C3,Master!$C5,Master!$C8) I want to remove the A2 part from all 1000 entries On Oct 27, 2:45 pm, "RagDyeR" wrote: Post your formula. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- wrote in message ups.com... I have a column of about 1000 entries. It is a very long Concatenate formula. Within the formula there is a reference to another column which I no longer want to include. Is there a way to modify all these entries to remove that reference? If it was a static value I would just do a search and replace but the part I want to remove is different on each row (for example, A1 on the first row, A2 on the second and so on, up to A1000. I want to remove them all) any ideas? Thanks in advance! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help modifying a complex column of formulas
Just change the formula in the first cell to this:
=CONCATENATE(Master!$R$2,Master!$R$3,Master!$R$4,M aster!$C2,D3,Master! $C3,Master!$C5,Master!$C8) by deleting the ,A2. Select the cell again and double-click on the fill handle (the small black square in the bottom right corner of the cursor) and this will copy it down the column. Hope this helps. Pete On Oct 27, 8:37 pm, wrote: Thiis is the formula: =CONCATENATE(Master!$R$2,Master!$R$3,A2,Master!$R$ 4,Master! $C2,D3,Master!$C3,Master!$C5,Master!$C8) I want to remove the A2 part from all 1000 entries On Oct 27, 2:45 pm, "RagDyeR" wrote: Post your formula. -- Regards, RD ---------------------------------------------------------------------------*-------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------*-------------------- wrote in message oups.com... I have a column of about 1000 entries. It is a very long Concatenate formula. Within the formula there is a reference to another column which I no longer want to include. Is there a way to modify all these entries to remove that reference? If it was a static value I would just do a search and replace but the part I want to remove is different on each row (for example, A1 on the first row, A2 on the second and so on, up to A1000. I want to remove them all) any ideas? Thanks in advance!- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help modifying a complex column of formulas
Thanks for the suggestion Pete, but it wont work in my case.
Sorry, I should have mentioned this before. The problem is that every so often I have customised the formula down the column. In about 200 to 300 cells I have replaced the Master!$C8 at the end of the formula with Master!$C9. I want to keep those like that. Ideally I would do some kind of search and replace with a wildcard, for example: replace "Master!$R$3,*,Master!$R$4" with "Master!$R$3,Master!$R$4" but I don't think such a thing is possible. On Oct 27, 3:47 pm, Pete_UK wrote: Just change the formula in the first cell to this: =CONCATENATE(Master!$R$2,Master!$R$3,Master!$R$4,M aster!$C2,D3,Master! $C3,Master!$C5,Master!$C8) by deleting the ,A2. Select the cell again and double-click on the fill handle (the small black square in the bottom right corner of the cursor) and this will copy it down the column. Hope this helps. Pete On Oct 27, 8:37 pm, wrote: Thiis is the formula: =CONCATENATE(Master!$R$2,Master!$R$3,A2,Master!$R$ 4,Master! $C2,D3,Master!$C3,Master!$C5,Master!$C8) I want to remove the A2 part from all 1000 entries On Oct 27, 2:45 pm, "RagDyeR" wrote: Post your formula. -- Regards, RD ---------------------------------------------------------------------------*-------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------*-------------------- wrote in message oups.com... I have a column of about 1000 entries. It is a very long Concatenate formula. Within the formula there is a reference to another column which I no longer want to include. Is there a way to modify all these entries to remove that reference? If it was a static value I would just do a search and replace but the part I want to remove is different on each row (for example, A1 on the first row, A2 on the second and so on, up to A1000. I want to remove them all) any ideas? Thanks in advance!- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help modifying a complex column of formulas
Well, you should be able to replace this....
Master!$R$3,A2 with this... Master!$R$3 to do what you want. Rick wrote in message ups.com... Thanks for the suggestion Pete, but it wont work in my case. Sorry, I should have mentioned this before. The problem is that every so often I have customised the formula down the column. In about 200 to 300 cells I have replaced the Master!$C8 at the end of the formula with Master!$C9. I want to keep those like that. Ideally I would do some kind of search and replace with a wildcard, for example: replace "Master!$R$3,*,Master!$R$4" with "Master!$R$3,Master!$R$4" but I don't think such a thing is possible. On Oct 27, 3:47 pm, Pete_UK wrote: Just change the formula in the first cell to this: =CONCATENATE(Master!$R$2,Master!$R$3,Master!$R$4,M aster!$C2,D3,Master! $C3,Master!$C5,Master!$C8) by deleting the ,A2. Select the cell again and double-click on the fill handle (the small black square in the bottom right corner of the cursor) and this will copy it down the column. Hope this helps. Pete On Oct 27, 8:37 pm, wrote: Thiis is the formula: =CONCATENATE(Master!$R$2,Master!$R$3,A2,Master!$R$ 4,Master! $C2,D3,Master!$C3,Master!$C5,Master!$C8) I want to remove the A2 part from all 1000 entries On Oct 27, 2:45 pm, "RagDyeR" wrote: Post your formula. -- Regards, RD ---------------------------------------------------------------------------*-------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------*-------------------- wrote in message oups.com... I have a column of about 1000 entries. It is a very long Concatenate formula. Within the formula there is a reference to another column which I no longer want to include. Is there a way to modify all these entries to remove that reference? If it was a static value I would just do a search and replace but the part I want to remove is different on each row (for example, A1 on the first row, A2 on the second and so on, up to A1000. I want to remove them all) any ideas? Thanks in advance!- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help modifying a complex column of formulas
On Oct 27, 4:26 pm, "Rick Rothstein \(MVP - VB\)"
wrote: Well, you should be able to replace this.... Master!$R$3,A2 with this... Master!$R$3 to do what you want. Rick wrote in message ups.com... Thanks for the suggestion Pete, but it wont work in my case. Sorry, I should have mentioned this before. The problem is that every so often I have customised the formula down the column. In about 200 to 300 cells I have replaced the Master!$C8 at the end of the formula with Master!$C9. I want to keep those like that. Ideally I would do some kind of search and replace with a wildcard, for example: replace "Master!$R$3,*,Master!$R$4" with "Master!$R$3,Master!$R$4" but I don't think such a thing is possible. On Oct 27, 3:47 pm, Pete_UK wrote: Just change the formula in the first cell to this: =CONCATENATE(Master!$R$2,Master!$R$3,Master!$R$4,M aster!$C2,D3,Master! $C3,Master!$C5,Master!$C8) by deleting the ,A2. Select the cell again and double-click on the fill handle (the small black square in the bottom right corner of the cursor) and this will copy it down the column. Hope this helps. Pete On Oct 27, 8:37 pm, wrote: Thiis is the formula: =CONCATENATE(Master!$R$2,Master!$R$3,A2,Master!$R$ 4,Master! $C2,D3,Master!$C3,Master!$C5,Master!$C8) I want to remove the A2 part from all 1000 entries On Oct 27, 2:45 pm, "RagDyeR" wrote: Post your formula. -- Regards, RD ---------------------------------------------------------------------------*-------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------*-------------------- wrote in message That won't work though, because in the next row it is A3, then A4 and so on until A1000 oups.com... I have a column of about 1000 entries. It is a very long Concatenate formula. Within the formula there is a reference to another column which I no longer want to include. Is there a way to modify all these entries to remove that reference? If it was a static value I would just do a search and replace but the part I want to remove is different on each row (for example, A1 on the first row, A2 on the second and so on, up to A1000. I want to remove them all) any ideas? Thanks in advance!- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help modifying a complex column of formulas
This works in "Edit - Replace":
Replace ,A*, With , -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === wrote in message ups.com... Thanks for the suggestion Pete, but it wont work in my case. Sorry, I should have mentioned this before. The problem is that every so often I have customised the formula down the column. In about 200 to 300 cells I have replaced the Master!$C8 at the end of the formula with Master!$C9. I want to keep those like that. Ideally I would do some kind of search and replace with a wildcard, for example: replace "Master!$R$3,*,Master!$R$4" with "Master!$R$3,Master!$R$4" but I don't think such a thing is possible. On Oct 27, 3:47 pm, Pete_UK wrote: Just change the formula in the first cell to this: =CONCATENATE(Master!$R$2,Master!$R$3,Master!$R$4,M aster!$C2,D3,Master! $C3,Master!$C5,Master!$C8) by deleting the ,A2. Select the cell again and double-click on the fill handle (the small black square in the bottom right corner of the cursor) and this will copy it down the column. Hope this helps. Pete On Oct 27, 8:37 pm, wrote: Thiis is the formula: =CONCATENATE(Master!$R$2,Master!$R$3,A2,Master!$R$ 4,Master! $C2,D3,Master!$C3,Master!$C5,Master!$C8) I want to remove the A2 part from all 1000 entries On Oct 27, 2:45 pm, "RagDyeR" wrote: Post your formula. -- Regards, RD ---------------------------------------------------------------------------*-------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------*-------------------- wrote in message oups.com... I have a column of about 1000 entries. It is a very long Concatenate formula. Within the formula there is a reference to another column which I no longer want to include. Is there a way to modify all these entries to remove that reference? If it was a static value I would just do a search and replace but the part I want to remove is different on each row (for example, A1 on the first row, A2 on the second and so on, up to A1000. I want to remove them all) any ideas? Thanks in advance!- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help modifying a complex column of formulas
Well, you should be able to replace this....
Master!$R$3,A2 with this... Master!$R$3 to do what you want. That won't work though, because in the next row it is A3, then A4 and so on until A1000 Doh! (he says as he slaps his forehead) Rick |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help modifying a complex column of formulas
So it does! Fantastic.
I had no idea you culd search and replace using * Thanks for help. Saved me from endless hours of tedious work. On Oct 27, 4:59 pm, "RagDyeR" wrote: This works in "Edit - Replace": Replace ,A*, With , -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === wrote in message ups.com... Thanks for the suggestion Pete, but it wont work in my case. Sorry, I should have mentioned this before. The problem is that every so often I have customised the formula down the column. In about 200 to 300 cells I have replaced the Master!$C8 at the end of the formula with Master!$C9. I want to keep those like that. Ideally I would do some kind of search and replace with a wildcard, for example: replace "Master!$R$3,*,Master!$R$4" with "Master!$R$3,Master!$R$4" but I don't think such a thing is possible. On Oct 27, 3:47 pm, Pete_UK wrote: Just change the formula in the first cell to this: =CONCATENATE(Master!$R$2,Master!$R$3,Master!$R$4,M aster!$C2,D3,Master! $C3,Master!$C5,Master!$C8) by deleting the ,A2. Select the cell again and double-click on the fill handle (the small black square in the bottom right corner of the cursor) and this will copy it down the column. Hope this helps. Pete On Oct 27, 8:37 pm, wrote: Thiis is the formula: =CONCATENATE(Master!$R$2,Master!$R$3,A2,Master!$R$ 4,Master! $C2,D3,Master!$C3,Master!$C5,Master!$C8) I want to remove the A2 part from all 1000 entries On Oct 27, 2:45 pm, "RagDyeR" wrote: Post your formula. -- Regards, RD ---------------------------------------------------------------------------*-------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------*-------------------- wrote in message oups.com... I have a column of about 1000 entries. It is a very long Concatenate formula. Within the formula there is a reference to another column which I no longer want to include. Is there a way to modify all these entries to remove that reference? If it was a static value I would just do a search and replace but the part I want to remove is different on each row (for example, A1 on the first row, A2 on the second and so on, up to A1000. I want to remove them all) any ideas? Thanks in advance!- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help modifying a complex column of formulas
Well, you should be able to replace this....
Master!$R$3,A2 with this... Master!$R$3 to do what you want. That won't work though, because in the next row it is A3, then A4 and so on until A1000 There is always the macro world... Sub RemoveAx() Dim R As Range Dim SecondComma As Long For Each R In Range("A1:A1000") SecondComma = InStr(1 + InStr(R.Formula, ","), R.Formula, ",") R.Formula = Replace(R.Formula, Left$(R.Formula, _ SecondComma), "=CONCATENATE(Master!$R$2,") Next End Sub Rick |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help modifying a complex column of formulas
You're welcome, and thanks for the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- wrote in message ps.com... So it does! Fantastic. I had no idea you culd search and replace using * Thanks for help. Saved me from endless hours of tedious work. On Oct 27, 4:59 pm, "RagDyeR" wrote: This works in "Edit - Replace": Replace ,A*, With , -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === wrote in message ups.com... Thanks for the suggestion Pete, but it wont work in my case. Sorry, I should have mentioned this before. The problem is that every so often I have customised the formula down the column. In about 200 to 300 cells I have replaced the Master!$C8 at the end of the formula with Master!$C9. I want to keep those like that. Ideally I would do some kind of search and replace with a wildcard, for example: replace "Master!$R$3,*,Master!$R$4" with "Master!$R$3,Master!$R$4" but I don't think such a thing is possible. On Oct 27, 3:47 pm, Pete_UK wrote: Just change the formula in the first cell to this: =CONCATENATE(Master!$R$2,Master!$R$3,Master!$R$4,M aster!$C2,D3,Master! $C3,Master!$C5,Master!$C8) by deleting the ,A2. Select the cell again and double-click on the fill handle (the small black square in the bottom right corner of the cursor) and this will copy it down the column. Hope this helps. Pete On Oct 27, 8:37 pm, wrote: Thiis is the formula: =CONCATENATE(Master!$R$2,Master!$R$3,A2,Master!$R$ 4,Master! $C2,D3,Master!$C3,Master!$C5,Master!$C8) I want to remove the A2 part from all 1000 entries On Oct 27, 2:45 pm, "RagDyeR" wrote: Post your formula. -- Regards, RD ---------------------------------------------------------------------------*-------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------*-------------------- wrote in message oups.com... I have a column of about 1000 entries. It is a very long Concatenate formula. Within the formula there is a reference to another column which I no longer want to include. Is there a way to modify all these entries to remove that reference? If it was a static value I would just do a search and replace but the part I want to remove is different on each row (for example, A1 on the first row, A2 on the second and so on, up to A1000. I want to remove them all) any ideas? Thanks in advance!- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Modifying data in excel containing 4000+ formulas | Excel Worksheet Functions | |||
| Need help with some rather complex formulas... | Excel Worksheet Functions | |||
| Need help with some rather complex formulas...... | Excel Worksheet Functions | |||
| Modifying sumif formulas. | Excel Worksheet Functions | |||
| Complex If Then formulas | Excel Worksheet Functions | |||
Linear Mode
