Macro request: split text from one cell to many, manually choosing break points

[gardnertoo]

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

 

[tlowry]

So, here's a suggestion. It requires a Textbox (Textbox1) to work

• Make a backup of your file
• Copy this code into a new module (AltF11,Insert|Module,)
• Back to the worksheet with data (AltF11)
• Create the TextBox (Not from the form tool box)(Multiline = true) on the sheet with the "Packed" cells
• Copy a "packed" cell (more cells if you want to) into the TextBox
• Select a cell (just click it) where you wish to start the "unpacked" cells
• Position the cursor in the TextBox where you want the first break
• Run the macro "GiveMeABreak"
• Repeat as needed

You can figure out how you want to run this macro (keys or button)
In testing, I found the button to be preferable.

Sub GiveMeABreak()
    Dim k
    k = Me.TextBox1.SelStart
    Me.TextBox1.SelStart = 0
    Me.TextBox1.SelLength = k
    ActiveCell = Me.TextBox1.SelText
    Me.TextBox1.SelText = ""
    ActiveCell.Offset(1, 0).Activate
End Sub

 

[gardnertoo]

Thank you, tlowry, but for me this code didn't work. As written, it throws an "Invalid use of the Me keyword" error.
I replaced "Me." with "ActiveSheet." and got "Run-time error '438': Object doesn't support this property or method"

 

[tlowry]

My fault; this goes into the module for the sheet with the TextBox. Mine is: "Sheet1 (Sheet1)".

Please try it again.

tlowry

 

[gardnertoo]

I removed the old one, copied it again to the sheet code, now it gives me a new error: "Compile error: Method or data member not found" referring to TextBox1

Tell me exactly where to get the kind of textbox you're using? I used the Insert tab, Text grouping (far right), Text Box object. When I select it, the name of it does come up as TextBox1

 

[gardnertoo]

Never mind, I figured it out: it needs to be an ActiveX Control text box, as oppposed to the Forms Control kind of box. Got it working, thank you!