Replace over two cells

[Steve Wylie]

I have a large spreadsheet with addresses on that I am
using as a source for a Word merge and I need to replace
all cells that say "Petham Canterbury" with a cell
saying "Petham" and the next cell saying "Canterbury".
In other words, to split up the two words over two lines.

I have been using the Text to Columns function, and have
knocked up a quick macro to speed things up, but have to
press a shortcut key combination each time I want this to
happen:

Selection.TextToColumns Destination:=Range
(ActiveCell.Address), DataType:=xlDelimited _
, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True,
Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

What I really need is something that does a Find &
Replace across selected cells and do the whole lot in one
go.

Would anyone have any ideas, either for a Find & Replace
solution or a macro solution?

Steve

 

[keepitcool]

Steve,

almost there... except the Destination argument needs changing to:

Destination:= Selection

now you can select the whole column (provided the adjacent column is
empty) and give it a whirl


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Steve Wylie]

Thanks, but unfortunately not all the cells I need to
work on are in the same column. I'd need to select two
or three columns where the "Petham Canterbury" could be
in, then run the search on that selection.

I'm thinking that what I need is a macro that does a
Find, does the splitting up bit, then does another find
and split etc, and keeps on going till the Find returns
false.

Steve