Search only values, then replace

  • Thread starter Thread starter paul.domaskis
  • Start date Start date
P

paul.domaskis

In Excel 2003, without using VB[A], is there a way go to all cells
whose *value* contains the search string, then replace that string
inside the cell with a replacement string if the cell doesn't contain
a formula? If I search formulas, there are too many unwanted hits.
 
Press F5 (GoTo), then click on Special, select Constants and Text
(i.e. unselect Numbers, Logicals and Errors). Click OK, and this will
then highlight those cells with values that are text. Then do CTRL-H
(Find/Replace) and enter your search string and replace string and
click Replace All - it will only affect the highlighted cells.

Hope this helps.

Pete
 
That's an interesting function (Go To Special). The Text checkbox is
subordinate to the Formulas checkbox, though, so I didn't touch that.
The Constants checkbox did what I wanted. However, I note that a
numerical cell containing the value 3 (or =3) is not considered a
constant. It doesn't affect my search in this specific circumstance,
though.

Thanks!

Press F5 (GoTo), then click on Special, select Constants and Text
(i.e. unselect Numbers, Logicals and Errors). Click OK, and this will
then highlight those cells with values that are text. Then do CTRL-H
(Find/Replace) and enter your search string and replace string and
click Replace All - it will only affect the highlighted cells.

Hope this helps.

Pete

In Excel 2003, without using VB[A], is there a way go to all cells
whose *value* contains the search string, then replace that string
inside the cell with a replacement string if the cell doesn't contain
a formula?  If I search formulas, there are too many unwanted hits.
Click to expand...
Click to expand...
 
You're welcome, Paul - thanks for feeding back.

Pete

That's an interesting function (Go To Special).  The Text checkbox is
subordinate to the Formulas checkbox, though, so I didn't touch that.
The Constants checkbox did what I wanted.  However, I note that a
numerical cell containing the value 3 (or =3) is not considered a
constant.  It doesn't affect my search in this specific circumstance,
though.

Thanks!

Press F5 (GoTo), then click on Special, select Constants and Text
(i.e. unselect Numbers, Logicals and Errors). Click OK, and this will
then highlight those cells with values that are text. Then do CTRL-H
(Find/Replace) and enter your search string and replace string and
click Replace All - it will only affect the highlighted cells.
Click to expand...
Hope this helps.
Pete
Click to expand...

On May 6, 5:16 pm, (e-mail address removed) wrote:
In Excel 2003, without using VB[A], is there a way go to all cells
whose *value* contains the search string, then replace that string
inside the cell with a replacement string if the cell doesn't contain
a formula?  If I search formulas, there are too many unwanted hits.- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top