Search only values, then replace

[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.

 

[Pete_UK]

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

 

[paul.domaskis]

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.

 

[Pete_UK]

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.

Hope this helps.

Pete

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 -

- Show quoted text -