Search and replace query

  • Thread starter Thread starter KiwiBrian
  • Start date Start date
K

KiwiBrian

I need to be able to specify my search criteria including leading and/or
trailing spaces, and similarly with the replace text.
I could not get Excel to accept/handle spaces as part of the search mask.
How would I do this?
For example if I want to replace all uses of the word he in a database with
she how would I specify space he space for the search, and space she space
for the replace?

TIA
Brian Tozer
 
Brian

Edit>Replace

What: <space>he<space>
With: <space>she<space>

Works for me.

Gord Dibben Excel MVP
 
Thankyou very much Gord.
Your reply sent me off to do more testing and I now see how I was fooling
myself.
But it hilights my problem.
An example:-
In a column I have some entries ending in Pl which is an abbreviation for
Place.
However I now realise that the last visible character is the last character
in that column and I can't specify a trailing space as a search delimiter.
However if I specify <space>Pl I will get false hits. For example some
entried do have Place in full.
So in this example (one of many similar ones) how would I specify my search
criteria so as to only get hits when the <space>PL has no following
character?
I hope my explanation is clear.
Thanks
Brian
 
Thanks for your reply but it fails for me in the following representative
examples, and I cannot see any way of accomplishing it using Search and
Replace.
I hope you or anyone else can show me how it can be done.

Present Wanted
Burt Pl Burt Place
Burt Place Burt Place
Fred Pl Fred Place
Fred Place Fred Place

TIA
Brian Tozer
 
KiwiBrian

One way: (need two steps)

Replace <ace> with <>
replace <PL> with <Place>

George Gee


*KiwiBrian* has posted this message:
 
What's the difference to you if "Pl" is replaced with "Place",
*AND* "Place" *is* replaced with "Place"?

In your example, replace Pl *AND* Place with Place.
Same result ... yes?

Find what:
<Space>Pl*
Replace with:
<Space>Place
Worked for me!
 
Wonderful!!
Nearly right but gave me the lateral thinking that I should have thought of
for myself.
Solution:-
Replace <space>Place with <space>Pl
Replace <space>Pl with <space>Place

Otherwise other instances of <ace> get changed, e.g. face becomes f

Thanks again.
Brian Tozer
 
KiwiBrian

Glad to set you on the right road.
After all, you don't want to go changing your face!

*Ragdyer* has a more elegant solution.

George Gee


*KiwiBrian* has posted this message:
 
Thanks Ragdyer.
Unfortunately this fails on Plaza. Either as the last word in the record or
within the record.
Maybe my earlier posts clarifying the requirement got lost.
Appreciate the suggestion.

Brian
 
Back
Top