software

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: www.excelforum.com/member.php...oamp;userid=31791
View this thread: www.excelforum.com/showthread...hreadid=515194On Tue, 21 Feb 2006 22:35:34 -0600, melissa
gt; wrote:

gt;
gt;I am in the middle of a HUGE excel spreadsheet that has titles of names
gt;of an item.
gt;An Example is:
gt;
gt;SS HOOP W/ DANGLING OPEN HEART EARRING
gt;
gt;I am trying to Delete the SS from the entire spreadsheet, but when I do
gt;a find - replace it deletes others words that have SS in it. Like
gt;Crosses.
gt;
gt;I also need to rename SS to Sterling Silver in another spreadsheet.
gt;
gt;How can I do this?
gt;
gt;I hope it doesnt require VBA
gt;
gt;thanks.

For the Find string, type quot;SS quot; without the quotes. Note the lt;spacegt; after the
SS.

If SS can be the last word, then also use quot; SSquot; 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: www.excelforum.com/member.php...oamp;userid=31791
View this thread: www.excelforum.com/showthread...hreadid=515194quot;melissaquot; wrote:

gt; Okay SS is the first letter of the sentence. I did SS(space) and it
gt; still finds CRO*SS*ES..
gt;
gt; ANY OHTER IDEAS?
gt;
Somewhere in between what you have and what you want...
In the Find/Replace dialog box, use the quot;Find Allquot; 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,
RyanOn Tue, 21 Feb 2006 23:05:36 -0600, melissa
gt; wrote:

gt;
gt;Okay SS is the first letter of the sentence. I did SS(space) and it
gt;still finds CRO*SS*ES..
gt;
gt;ANY OHTER IDEAS?I cannot reproduce what you have written.

If I have CRO*SS*ES in a cell, and try to replace quot;SS quot;, 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 workRon Rosenfeld Wrote:
gt; On Tue, 21 Feb 2006 23:05:36 -0600, melissa
gt; gt; wrote:
gt;
gt; gt;
gt; gt;Okay SS is the first letter of the sentence. I did SS(space) and it
gt; gt;still finds CRO*SS*ES..
gt; gt;
gt; gt;ANY OHTER IDEAS?
gt;
gt;
gt; I cannot reproduce what you have written.
gt;
gt; If I have CRO*SS*ES in a cell, and try to replace quot;SS quot;, it does NOT
gt; replace
gt; the SS surrounded by asterisks -- in other words, it does not give me
gt; CRO**ES.
gt;
gt; Are you sure you meant to write what you did?
gt;
gt; --ron--
jmw
------------------------------------------------------------------------
jmw's Profile: www.excelforum.com/member.php...oamp;userid=31636
View this thread: www.excelforum.com/showthread...hreadid=515194
Of course if you replace quot;SS quot; with nothing it shouldn't affect
quot;CROSSESquot; but it might affect quot;CROSSquot;......

Is all your text upper case or can you utilise the quot;match casequot; option
to help you out?--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=515194
Of course if you replace quot;SS quot; with nothing it shouldn't affect
quot;CROSSESquot; but it might affect quot;CROSSquot;......

Is all your text upper case or can you utilise the quot;match casequot; option
to help you out?--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=515194On Tue, 21 Feb 2006 23:05:36 -0600, melissa
gt; wrote:

gt;
gt;Okay SS is the first letter of the sentence. I did SS(space) and it
gt;still finds CRO*SS*ES..
gt;
gt;ANY OHTER IDEAS?

If your data is all in one column, you could

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

Then, in an adjacent column, enter the formula:

=TRIM(REGEX.SUBSTITUTE(A1,quot;\bSS\bquot;,,,,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