Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
Replace over two cells
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 |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
Replace over two cells
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 |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
Replace over two cells
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I replace the #DIV/0! with blanks in the cells? | Excel Discussion (Misc queries) | |||
replace ' in cells | Excel Discussion (Misc queries) | |||
Find and Replace 2 cells | Excel Discussion (Misc queries) | |||
Checking the Cells in Sheet1 with Cells in Sheet2 and replace | Excel Worksheet Functions | |||
How to find multiple cells/replace whole cells w/data | Excel Discussion (Misc queries) |