Combining text from several cells (Excel 2000)

Topic

New Reply

I’m a real beginner at Excel. Someone has given me a chart containing text entries, not numbers, and my job is to clean everything up. (The text in the chart was imported from several different sources, which ended up splitting up many of the entries that should all be in one cell.)

I’ve attached a sample document showing you what I mean. The first row shows you how some of the entries in the document are now. The second row is the “cleaned-up” version of the first row.

The only way I know to get all the text into the first cell is to double-click in the second cell, select the text, press Ctrl-C, double-click back in the first cell, paste onto the end of what’s there, and then repeat the process for each cell in the row. Pretty cumbersome. Is there some way to select all the cells I want to combine and have everything put into the first cell in one fell swoop?

I tried using the “Merge” option under Format Cells, but that deletes some of the text and also doubles the size of the cell.

Reply | Quote

Replies

If all the columns are equally organised with data in say A, B & C
Then something like

=A1 & ” ” & B1 & ” ” & C1

in Cell D1 would combine them.

After that you can copy the D1 formula down the rows for as many as you need
[editted to add this next bit]
After that if you select the whole column, copy it, and “paste special > values” back onto itself you can then remove the original columns

Reply | Quote

Wow, Andrew, that works great! It took me a while to try what you said and get it just right, but it really works. This will help a lot. Thanks so much.

Russ

Reply | Quote

Wow, Andrew, that works great! It took me a while to try what you said and get it just right, but it really works. This will help a lot. Thanks so much.

Russ

Reply | Quote

If all the columns are equally organised with data in say A, B & C
Then something like

=A1 & ” ” & B1 & ” ” & C1

in Cell D1 would combine them.

After that you can copy the D1 formula down the rows for as many as you need
[editted to add this next bit]
After that if you select the whole column, copy it, and “paste special > values” back onto itself you can then remove the original columns

Reply | Quote

There are several possibilities here. First would be to use a formula. If the first cell is A1, and the last cell in the row with the most words is in column P, then you could enter a formula like the one below into a cell in an empty column and copy it down.

=TRIM(A1&" "&B1&" "&C1&" "&D1&" "&D1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1&" "&K1&" "&L1&" "&M1&" "&N1&" "&O1&" "&P1)

To eliminate the formulas and replace them with the actual text, do the following:

1- Select the column with the formulas.

2- Select Copy from the Edit menu.

3- Select Paste Special from the Edit Menu

4- In the resulting dialog box, select Values in the Paste section.

5- Click OK.

You should now have a column with the text you want, and you can select the columns containing the individual words and delete them.

Another possibility would be to use a User Defined Function, like the one in This Post to concatenate the words.

If you are going to have to do this often a fairly simple VBA macro would do everything automatically.

Reply | Quote

Thank you, Legare. That’s just what I need.

Russ

Reply | Quote

Thank you, Legare. That’s just what I need.

Russ

Reply | Quote

There are several possibilities here. First would be to use a formula. If the first cell is A1, and the last cell in the row with the most words is in column P, then you could enter a formula like the one below into a cell in an empty column and copy it down.

=TRIM(A1&" "&B1&" "&C1&" "&D1&" "&D1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1&" "&K1&" "&L1&" "&M1&" "&N1&" "&O1&" "&P1)

To eliminate the formulas and replace them with the actual text, do the following:

1- Select the column with the formulas.

2- Select Copy from the Edit menu.

3- Select Paste Special from the Edit Menu

4- In the resulting dialog box, select Values in the Paste section.

5- Click OK.

You should now have a column with the text you want, and you can select the columns containing the individual words and delete them.

Another possibility would be to use a User Defined Function, like the one in This Post to concatenate the words.

If you are going to have to do this often a fairly simple VBA macro would do everything automatically.

Reply | Quote