Combine multiple lines of content into one.

Question

Hello,

I need to fix an Excel table created by someone who knew even less than I do. Since the text in one of the columns was too long to be visible on one line, this person continued it on the line below. So, each record spans multiple lines, making it impossible for me to modify the sort of the data according to my needs.

My table covers more than 4000 lines, I tried to create a macro by hitting the "Record Macro" button. But I only managed to delete information. Can someone suggest a macro?

The column A contains a sequential number for each record, and column E contains the text to be merged; here’s what the macro should do:

1. Find the first empty cell in column A
2. Move the cursor to column E
3. Cut the text in that cell
4. Move the cursor to the cell above
5. Paste the clipboard content after the existing text without deleting anything
6. Bring the cursor back to the cell below (which is now empty)
7. Delete the line
8. Repeat the process until the end of the table.

Configuration: Windows 7 / Firefox 21.0

Littlegreg · Accepted Answer

Hard to say without the table, but it seems to me that there is something simpler than a macro.

There is a formula to combine multiple cell contents; it's the concatenate function...
Try something like this in your column F:
=concatenate(B1;C1.D1)

See what it gives you and apply this function to what you want.

Babar64420 · Answer

Hello, We should be able to do without macros by concatenating and then sorting. I’ll respond to someone first and get back to you See you later.

Babar64420 · Answer

Concatenation means assembling several parts into one.
For you, that's exactly what you want to do with your lines.
Example
A1 ="Toto goes to"
A2="big game fishing"
If in B1 you type =A1&A2, it gives "Toto goes to big game fishing"
So you need to add a space between the end of A1 and the beginning of A2.
In the end, you should type in B1 = A1&" "&A2.
Now, for the 4000 lines.
In a free column, you will type the formula next to your first line. Nothing below. Then you select these 2 lines and drag down.
In this column, you will then have a line with the concatenation and a blank line. You simply sort to keep only the non-empty lines at the top of the table.
You can then clean your file of the initial columns.
Is that clear?
See you later.

Superpat · Answer

I'm sorry, I think I was misunderstood.

Here’s what my table looks like

A1=First sequential number
B1=Code
C1=Date
D1=Date
E1=The text starts, continues in cell E2, and ends in E3.
A4=Second sequential number
etc.

Cells A2, B2, C2, D2, A3, B3, C3, and D3 have been left blank.
My goal is to gather the text from cells E1, E2, and E3. Once done, I delete rows 2 and 3. And my second sequential number now appears in row 2.

The texts in column E can span one to five lines. There is no consistency.

JvDo · Answer

Good evening everyone,

If your data is sometimes on one line and sometimes on two, you can use a conditional formula that checks if the cell in column A, of the row below, is empty.

If column F is free and your data starts in row 1, enter the formula in F1: =IF(A2=";",E1&CAR(10)&E2,IF(A1=";"",E1)) and drag it down over your 4000 rows.
Note: I used CAR(10) instead of a space to enforce a line break in the cell. This will give you the same appearance as currently. If it doesn’t suit you, replace it with a space between quotes

Then select the cell range F1:F4000 for a special copy/paste "value" (this is to eliminate the formulas).
Next, select your data range A1:F4000 (here, I assume you don't have any data outside of this range) and sort by column F as the first key and your current index column as the second key.
This assumes you have a sort key in your data (a reference, an identifier, an invoice number, or something that suits you). Generally, it’s what’s in column A.

Once the sort is done, all the rows with an empty cell in column A will be at the end of your data (or at the beginning depending on the order of your sort).
Then select this data area after checking that you don’t have any data lurking in columns B to D (you never know) and delete the corresponding rows.

Finally, your column E is no longer needed (make sure there are no data below your 4000 rows!!!). You can delete it and you'll end up with a column E that has only one cell per record.

Naturally, you should back up your data before working on it.

In case you currently don’t have a sort key, I suggest creating a fictitious one that goes from 1 to 4000 in a column that you insert before column A (it will become your new column A and everything I wrote will need to be shifted by one column).
This will allow you, when finished, to sort your data range A1:G4000 with column A as the first key.
Finally, delete your column A that is no longer needed.

If you haven’t given up reading this post and implement it up to this point, you will certainly need to reformat your area regarding alignment and row height.
We will address that later.

Best regards.

PS: if you don’t feel comfortable with what I just suggested, don’t touch anything!!!
If your Excel sheet interacts with other sheets or feeds into other systems, don’t change anything.

Babar64420 · Answer

So, macro!

Combine multiple lines of content into one.

6 réponses

Texture issues in enshrouded

Driver his

Peritel to hdmi conversion

How to access the bios on a compaq notebook

Dsi won't turn on anymore

Search for a song with the lyrics

Left mouse click, touchpad click inactive

Codes stronghold crusader

Stuck computer system repair

Disable write protection mp3 player