I am in the middle of a HUGE excel spreadsheet that has titles of names
of an item.
An Example is:

SS HOOP W/ DANGLING OPEN HEART EARRING

I am trying to Delete the SS from the entire spreadsheet, but when I do
a find - replace it deletes others words that have SS in it. Like
Crosses.

I also need to rename SS to Sterling Silver in another spreadsheet.

How can I do this?

I hope it doesnt require VBA

thanks.


--
melissa
------------------------------------------------------------------------
melissa's Profile: http://www.excelforum.com/member.php...o&userid=31791
View this thread: http://www.excelforum.com/showthread...hreadid=515194

On Tue, 21 Feb 2006 22:35:34 -0600, melissa
wrote:


I am in the middle of a HUGE excel spreadsheet that has titles of names
of an item.
An Example is:

SS HOOP W/ DANGLING OPEN HEART EARRING

I am trying to Delete the SS from the entire spreadsheet, but when I do
a find - replace it deletes others words that have SS in it. Like
Crosses.

I also need to rename SS to Sterling Silver in another spreadsheet.

How can I do this?

I hope it doesnt require VBA

thanks.

For the Find string, type "SS " without the quotes. Note the <space after the
SS.

If SS can be the last word, then also use " SS" as a Find string.


--ron

Okay SS is the first letter of the sentence. I did SS(space) and it
still finds CRO*SS*ES..

ANY OHTER IDEAS?


--
melissa
------------------------------------------------------------------------
melissa's Profile: http://www.excelforum.com/member.php...o&userid=31791
View this thread: http://www.excelforum.com/showthread...hreadid=515194

"melissa" wrote:

Okay SS is the first letter of the sentence. I did SS(space) and it
still finds CRO*SS*ES..

ANY OHTER IDEAS?

Somewhere in between what you have and what you want...
In the Find/Replace dialog box, use the "Find All" and scroll through the
encountered occurrences, replacing the ones you want and ignoring the ones
you don't want. Not ideal, but better than nothing :)

HTH,
Ryan

On Tue, 21 Feb 2006 23:05:36 -0600, melissa
wrote:


Okay SS is the first letter of the sentence. I did SS(space) and it
still finds CRO*SS*ES..

ANY OHTER IDEAS?


I cannot reproduce what you have written.

If I have CRO*SS*ES in a cell, and try to replace "SS ", it does NOT replace
the SS surrounded by asterisks -- in other words, it does not give me CRO**ES.

Are you sure you meant to write what you did?

--ron

you are quite right

adding a space to the search will allow you to replace words in the way
that you describe without finding the ss inside words

i use this often to remove double or treble spaces from inbetween words
etc

maybe it is to do with the format of the cells you are searching on why
it is ignoring the spaces

are they number fields or aphanumeric change the format of your collumn
to text and try again is should work


Ron Rosenfeld Wrote:
On Tue, 21 Feb 2006 23:05:36 -0600, melissa
wrote:


Okay SS is the first letter of the sentence. I did SS(space) and it
still finds CRO*SS*ES..

ANY OHTER IDEAS?


I cannot reproduce what you have written.

If I have CRO*SS*ES in a cell, and try to replace "SS ", it does NOT
replace
the SS surrounded by asterisks -- in other words, it does not give me
CRO**ES.

Are you sure you meant to write what you did?

--ron


--
jmw
------------------------------------------------------------------------
jmw's Profile: http://www.excelforum.com/member.php...o&userid=31636
View this thread: http://www.excelforum.com/showthread...hreadid=515194

Of course if you replace "SS " with nothing it shouldn't affect
"CROSSES" but it might affect "CROSS"......

Is all your text upper case or can you utilise the "match case" option
to help you out?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=515194

Of course if you replace "SS " with nothing it shouldn't affect
"CROSSES" but it might affect "CROSS"......

Is all your text upper case or can you utilise the "match case" option
to help you out?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=515194

On Tue, 21 Feb 2006 23:05:36 -0600, melissa
wrote:


Okay SS is the first letter of the sentence. I did SS(space) and it
still finds CRO*SS*ES..

ANY OHTER IDEAS?

If your data is all in one column, you could

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then, in an adjacent column, enter the formula:

=TRIM(REGEX.SUBSTITUTE(A1,"\bSS\b",,,,FALSE))

and copy/drag down as far as required.

The FALSE means that the formula is case insensitive.

This formula will only replace ss or SS if it is a separate word.

It will NOT replace the ss in Cross, stainless, etc.

Then Edit/Copy the column with the results, and Paste Special Values over the
original.

If your data is scattered, we could write a macro to do the same thing.


--ron