gardnertoo
Well-known Member
- Joined
- Jul 24, 2007
- Messages
- 938
I have a spreadsheet that was once in Excel, then printed and faxed, then scanned into an Adobe pdf image, from which the text was restored with optical character recognition, then copied (using Adobe's "copy as table", not just "copy") and dumped back into Excel. "Copy as table" did not result in 100% success, and I ended up with many spots where a group of cells in column D got combined into a single cell. For example, this string appears in one cell, followed by six empty cells below it:
CLCW PUMP No.1 RUN FDBK CONDENSER HOT WELL LEVEL HI CONDENSER HOT WELL LEVEL HI HI CONDENSER HOT WELL LEVEL LO CONDENSER HOT WELL LEVEL LO LO CONDENSER HOTWELL BYPASS FLOW WATER-COOLED CONDENSER LEVEL GAUGE (105M100)
On the original, that text occopied seven rows in the same column, like this:
CLCW PUMP No.1 RUN FDBK
CONDENSER HOT WELL LEVEL HI
CONDENSER HOT WELL LEVEL HI HI
CONDENSER HOT WELL LEVEL LO
CONDENSER HOT WELL LEVEL LO LO
CONDENSER HOTWELL BYPASS FLOW
WATER-COOLED CONDENSER LEVEL GAUGE (105M100)
In many of the places where this happened, most in fact, the first word of each cell will be the same. It could serve as a break point indicator in a macro built to split up the text. But this is an example where that will not hold true. There is one place in the sheet where 93 rows got combined into one, and there were twelve different “first word” candidates in that block! I have been copying these blocks of text into Word, hitting Enter Enter Enter Enter to split them up, then copying back to Excel. That’s not bad for the big ones, especially since I found the tip online that you within Word you can insert a carriage return in the “Find and Replace” by using the ^p string, but it is tedious when there are only two or three combined cells.
Requested solution: I would like to be able to select the cell, then click within the string at a spot that I manually choose to be the break point between this cell and the next cell (next = the one below). Then with a keyboard shortcut (I’m thinking Ctrl+Shift+X, the capitalized analogue of the Ctrl+x = “cut” function) the macro will remove everything after the cursor from this cell, and put it all in the next cell.
Windows XP, Excel 2007
CLCW PUMP No.1 RUN FDBK CONDENSER HOT WELL LEVEL HI CONDENSER HOT WELL LEVEL HI HI CONDENSER HOT WELL LEVEL LO CONDENSER HOT WELL LEVEL LO LO CONDENSER HOTWELL BYPASS FLOW WATER-COOLED CONDENSER LEVEL GAUGE (105M100)
On the original, that text occopied seven rows in the same column, like this:
CLCW PUMP No.1 RUN FDBK
CONDENSER HOT WELL LEVEL HI
CONDENSER HOT WELL LEVEL HI HI
CONDENSER HOT WELL LEVEL LO
CONDENSER HOT WELL LEVEL LO LO
CONDENSER HOTWELL BYPASS FLOW
WATER-COOLED CONDENSER LEVEL GAUGE (105M100)
In many of the places where this happened, most in fact, the first word of each cell will be the same. It could serve as a break point indicator in a macro built to split up the text. But this is an example where that will not hold true. There is one place in the sheet where 93 rows got combined into one, and there were twelve different “first word” candidates in that block! I have been copying these blocks of text into Word, hitting Enter Enter Enter Enter to split them up, then copying back to Excel. That’s not bad for the big ones, especially since I found the tip online that you within Word you can insert a carriage return in the “Find and Replace” by using the ^p string, but it is tedious when there are only two or three combined cells.
Requested solution: I would like to be able to select the cell, then click within the string at a spot that I manually choose to be the break point between this cell and the next cell (next = the one below). Then with a keyboard shortcut (I’m thinking Ctrl+Shift+X, the capitalized analogue of the Ctrl+x = “cut” function) the macro will remove everything after the cursor from this cell, and put it all in the next cell.
Windows XP, Excel 2007