Urgent help

[melissa]

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.

 

[Ron Rosenfeld]

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

 

[melissa]

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

ANY OHTER IDEAS?

 

[Guest]

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

 

[Ron Rosenfeld]

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]

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

 

[daddylonglegs]

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]

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?

 

[Ron Rosenfeld]

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