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" wrote:

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