replace all function - excel 2003

[Guest]

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

 

[Lori]

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then Data>Text to Columns>Delimited>Tab>Finish to
evaluate.

 

[Guest]

Try turning automatic calculation off
Also try opening the linked file before executing the search & replace

 

[Dave Peterson]

Just a warning.

Using '= changes the .prefixcharacter setting and can cause trouble later.

I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.

And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then Data>Text to Columns>Delimited>Tab>Finish to
evaluate.

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

 

[Lori]

I suspect there are multiple sending workbooks as it is the folder
name that is being replaced, and if so opening all workbooks within
that folder could be troublesome.

Thanks for pointing out the prefix character steeing, I hadn't noticed
that this was a persistent setting before. I can't see it being a
major issue here though and you could get round it by outputting to
another column column in the third step of text to columns and
deleting the existing one. I also meant to add that you may need to do
an edit > links > update.

Just a warning.

Using '= changes the .prefixcharacter setting and can cause trouble later.

I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.

And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then Data>Text to Columns>Delimited>Tab>Finish to
evaluate.

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.
when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.
help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

 

[Guest]

thanks...worked like a charm.

Try turning automatic calculation off
Also try opening the linked file before executing the search & replace

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

 

[Guest]

Hi Rob,

Try doing a search and replace = sign with the = sign, then recalc.

Let me know how that worked.

Thanks,
Peggy

 

[Dave Peterson]

There are ways to work around the .prefixcharacter problem. But I find it
easier to just not cause it.

And maybe there are multiple sending workbooks--if that number is small, I'd
still try to open them all at once. (Small purposely left vague <vbg>.)

I suspect there are multiple sending workbooks as it is the folder
name that is being replaced, and if so opening all workbooks within
that folder could be troublesome.

Thanks for pointing out the prefix character steeing, I hadn't noticed
that this was a persistent setting before. I can't see it being a
major issue here though and you could get round it by outputting to
another column column in the third step of text to columns and
deleting the existing one. I also meant to add that you may need to do
an edit > links > update.

Just a warning.

Using '= changes the .prefixcharacter setting and can cause trouble later.

I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.

And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then Data>Text to Columns>Delimited>Tab>Finish to
evaluate.

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

 

[Dave Peterson]

And from the OP's response, it sounds like there was only one file--or a minor
number of files if more than one.

I suspect there are multiple sending workbooks as it is the folder
name that is being replaced, and if so opening all workbooks within
that folder could be troublesome.

Thanks for pointing out the prefix character steeing, I hadn't noticed
that this was a persistent setting before. I can't see it being a
major issue here though and you could get round it by outputting to
another column column in the third step of text to columns and
deleting the existing one. I also meant to add that you may need to do
an edit > links > update.

Just a warning.

Using '= changes the .prefixcharacter setting and can cause trouble later.

I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.

And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then Data>Text to Columns>Delimited>Tab>Finish to
evaluate.

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

 

[Lori]

True, it seems there were only a handful of links at most and simple
is almost always best as here <g>.

FWIW using ["=] instead of ['=] would avoid the prefix character and
still evaluate since " is the default text qualifier.

There are ways to work around the .prefixcharacter problem. But I find it
easier to just not cause it.

And maybe there are multiple sending workbooks--if that number is small, I'd
still try to open them all at once. (Small purposely left vague <vbg>.)

I suspect there are multiple sending workbooks as it is the folder
name that is being replaced, and if so opening all workbooks within
that folder could be troublesome.

Thanks for pointing out the prefix character steeing, I hadn't noticed
that this was a persistent setting before. I can't see it being a
major issue here though and you could get round it by outputting to
another column column in the third step of text to columns and
deleting the existing one. I also meant to add that you may need to do
an edit > links > update.

Just a warning.
Using '= changes the .prefixcharacter setting and can cause trouble later.
I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.
And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????
Lori wrote:
Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.
1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then Data>Text to Columns>Delimited>Tab>Finish to
evaluate.
this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.
when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.
help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

 

[Dave Peterson]

I think "= is another bad choice <bg>.

' means left justified label
" means right justified label
^ means centered label
/ means fill/repeated label

I still like $$$$$=

I don't understand what you mean by: " is the default text qualifier.

True, it seems there were only a handful of links at most and simple
is almost always best as here <g>.

FWIW using ["=] instead of ['=] would avoid the prefix character and
still evaluate since " is the default text qualifier.

There are ways to work around the .prefixcharacter problem. But I find it
easier to just not cause it.

And maybe there are multiple sending workbooks--if that number is small, I'd
still try to open them all at once. (Small purposely left vague <vbg>.)

I suspect there are multiple sending workbooks as it is the folder
name that is being replaced, and if so opening all workbooks within
that folder could be troublesome.

Thanks for pointing out the prefix character steeing, I hadn't noticed
that this was a persistent setting before. I can't see it being a
major issue here though and you could get round it by outputting to
another column column in the third step of text to columns and
deleting the existing one. I also meant to add that you may need to do
an edit > links > update.

Just a warning.

Using '= changes the .prefixcharacter setting and can cause trouble later.

I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.

And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????

Lori wrote:

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then Data>Text to Columns>Delimited>Tab>Finish to
evaluate.

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.


Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

 

[Lori]

But you'll still need to replace $$$$= with = which is very slow on
external files [unless they are open!] because of the relinking. I
wanted to avoid that step. One way is to do the replacement in say
wordpad and paste back to Excel. Another is to do text to columns on a
text version of the formula e.g. "=[Book1]Sheet1!A1

As I understand it " only means right justified when the transition
navigation keys option is selected. By " is the default text
qualifier, I meant that this is the property that you see in the in
the second step of the import text wizard/text to columns which is
used for instance when csv files are opened to distinguish commas in
text entries from separators the "s themselves are stripped. I'm sure
you know all this anyway but I just kind of wanted to organise my
thoughts <g>.

I think "= is another bad choice <bg>.

' means left justified label
" means right justified label
^ means centered label
/ means fill/repeated label

I still like $$$$$=

I don't understand what you mean by: " is the default text qualifier.

True, it seems there were only a handful of links at most and simple
is almost always best as here <g>.

FWIW using ["=] instead of ['=] would avoid the prefix character and
still evaluate since " is the default text qualifier.

There are ways to work around the .prefixcharacter problem. But I find it
easier to just not cause it.
And maybe there are multiple sending workbooks--if that number is small, I'd
still try to open them all at once. (Small purposely left vague <vbg>.)
Lori wrote:
I suspect there are multiple sending workbooks as it is the folder
name that is being replaced, and if so opening all workbooks within
that folder could be troublesome.
Thanks for pointing out the prefix character steeing, I hadn't noticed
that this was a persistent setting before. I can't see it being a
major issue here though and you could get round it by outputting to
another column column in the third step of text to columns and
deleting the existing one. I also meant to add that you may need to do
an edit > links > update.
Just a warning.
Using '= changes the .prefixcharacter setting and can cause trouble later.
I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.
And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????
Lori wrote:
Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.
1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then Data>Text to Columns>Delimited>Tab>Finish to
evaluate.
this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.
when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.
help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

 

[Dave Peterson]

The problem that I have with the .prefixcharacter is when I try to clear cells
that were once text (that used a prefixcharacter), then were converted to
values.

=if(a1=7,"ok","")
Clearing those cells that look empty can be a pain if you use that
prefixcharacter.

And if you want, you can still use data|text to columns. Just chose fixed width
and skip the $$$$$ field.

I didn't test this, but I would have guessed Data|text to columns and
edit|replace would essentially recalc at the same speed. But that's just a
guess since each formula has to go to that external workbook to get its value.

But you'll still need to replace $$$$= with = which is very slow on
external files [unless they are open!] because of the relinking. I
wanted to avoid that step. One way is to do the replacement in say
wordpad and paste back to Excel. Another is to do text to columns on a
text version of the formula e.g. "=[Book1]Sheet1!A1

As I understand it " only means right justified when the transition
navigation keys option is selected. By " is the default text
qualifier, I meant that this is the property that you see in the in
the second step of the import text wizard/text to columns which is
used for instance when csv files are opened to distinguish commas in
text entries from separators the "s themselves are stripped. I'm sure
you know all this anyway but I just kind of wanted to organise my
thoughts <g>.

I think "= is another bad choice <bg>.

' means left justified label
" means right justified label
^ means centered label
/ means fill/repeated label

I still like $$$$$=

I don't understand what you mean by: " is the default text qualifier.

True, it seems there were only a handful of links at most and simple
is almost always best as here <g>.

FWIW using ["=] instead of ['=] would avoid the prefix character and
still evaluate since " is the default text qualifier.

There are ways to work around the .prefixcharacter problem. But I find it
easier to just not cause it.

And maybe there are multiple sending workbooks--if that number is small, I'd
still try to open them all at once. (Small purposely left vague <vbg>.)

Lori wrote:

I suspect there are multiple sending workbooks as it is the folder
name that is being replaced, and if so opening all workbooks within
that folder could be troublesome.

Thanks for pointing out the prefix character steeing, I hadn't noticed
that this was a persistent setting before. I can't see it being a
major issue here though and you could get round it by outputting to
another column column in the third step of text to columns and
deleting the existing one. I also meant to add that you may need to do
an edit > links > update.

Just a warning.

Using '= changes the .prefixcharacter setting and can cause trouble later.

I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.

And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????

Lori wrote:

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then Data>Text to Columns>Delimited>Tab>Finish to
evaluate.

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.


Dave Peterson- Hide quoted text -

- Show quoted text -

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

 

[diaare]

Sorry to revive such an old tread, but I am attempting to do the same thing.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane

Hi Rob,

Try doing a search and replace = sign with the = sign, then recalc.

Let me know how that worked.

Thanks,
Peggy

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

 

[Lori]

Did you try the Text to Columns method?

Replace = with "= then select the column and choose
Data > Text To Columns with Delimited and click Finish.

You can then update links by in the Edit > Links dialog.

Sorry to revive such an old tread, but I am attempting to do the same thing.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=.  This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace.  Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.  

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane

Hi Rob,

Try doing a search and replace = sign with the = sign, then recalc.

Let me know how that worked.

Thanks,
Peggy

this is my formula  
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.
when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.
help?- Hide quoted text -

- Show quoted text -

 

[Dave Peterson]

If you open the "sending" file, does it work faster?

Sorry to revive such an old tread, but I am attempting to do the same thing.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane

Hi Rob,

Try doing a search and replace = sign with the = sign, then recalc.

Let me know how that worked.

Thanks,
Peggy

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

 

[Lori]

If there's just one filename, why not just change the link by double-
clicking the old file name in the Edit > Links dialog, this seems
simpler than the Replace method in the original post as well.

If you open the "sending" file, does it work faster?

Sorry to revive such an old tread, but I am attempting to do the same thing.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=.  This was very fast.

I changed my workbook reference from jan-june to july-dec using the findand
replace.  Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane

Hi Rob,
Try doing a search and replace = sign with the = sign, then recalc..
Let me know how that worked.
Thanks,
Peggy
:
this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.
when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.
help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

 

[diaare]

Thank you both for your help.

Unfortunately I was not able to use the edit links function because I was
only changing the tab it referenced, not the workbook. (unless you know
something I don't know)

But, after a little more trial and error with a comination of your
suggestions I was finally able to get it to work...it still took a few
minutes, but it completed without crashing excel.

BTW - the most crutical thing I forgot was to open the linked
workbook...duh! Sometimes the most obvious thing is all it takes....

Thanks Again,

Diane

If there's just one filename, why not just change the link by double-
clicking the old file name in the Edit > Links dialog, this seems
simpler than the Replace method in the original post as well.

If you open the "sending" file, does it work faster?

Sorry to revive such an old tread, but I am attempting to do the same thing.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?


"pshepard" wrote:

Hi Rob,

Try doing a search and replace = sign with the = sign, then recalc..

Let me know how that worked.

"Rob" wrote:

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -