need help modifying a complex column of formulas

  • Thread starter Thread starter nicolai
  • Start date Start date
N

nicolai

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!
 
Post your formula.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

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!
 
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
 
Just change the formula in the first cell to this:

=CONCATENATE(Master!$R$2,Master!$R$3,Master!$R$4,Master!$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
 
How about just editing out the Ax, in top cell and copying that down to A1000?


Gord Dibben MS Excel MVP
 
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.
 
Well, you should be able to replace this....

Master!$R$3,A2

with this...

Master!$R$3

to do what you want.

Rick


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.
 
Well, you should be able to replace this....

Master!$R$3,A2

with this...

Master!$R$3

to do what you want.

Rick


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.
Click to expand...
That won't work though, because in the next row it is A3, then A4 and
so on until A1000
 
This works in "Edit - Replace":
Replace
,A*,
With
,

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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.
 
Well, you should be able to replace this....
That won't work though, because in the next row it is A3,
then A4 and so on until A1000
Click to expand...

Doh! (he says as he slaps his forehead)

Rick
 
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.
 
Well, you should be able to replace this....
That won't work though, because in the next row it is A3,
then A4 and so on until A1000
Click to expand...

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
 
Back
Top