Help with Merging Cells

[kimmyh3]

Hello,

I have a document that I need help with.

The document has 10 different columns, and I need all the data in just 1 column, not 10 different columns. Does anyone know an easy way to do this? I tried to highlight the row and merge, but it did not work.

Does anyone know an easy way to move all the items into one column?

Example: Row 8 would have: * Corporate, Estate Planning/Wills, Business Law,Probate & Trust, Tax-Federal*in all 1 cell, not different cells.


Thanks for your help!
Kimmy

[alex_c]

You can try combining the values in one cell with the following formula.

Say cell B1 says Mary and cell C1 says Smith. In another cell you can type

=B1&" "&C1

to get "Mary Smith" in that cell.

[Palmetto]

Example: Row 8 would have: * Corporate, Estate Planning/Wills, Business Law,Probate & Trust, Tax-Federal*in all 1 cell, not different cells.

Did you mean to include the * character? (omitted below)

The formula would be:
=A8& " " &B8&" "&C8&" "&D8&" "&E8&" "&F8&" "&G8&" "&H8&" "&I8&" "&J8

Do you really need the comma? Reason I ask is because you will have trailing commas for cells that are empty.

Also, the cells that appear blank are, in fact, not blank. Try a simple COUNTA or COUNTBLANK on a row and you will see it returns a value greater than cells which contain "visible" text.

[kimmyh3]

Thank you all so much for the feedback, this is wonderful.

It seems to be working, however, I do need the comma in between the words. If I use the formula: =A8& " " &B8&" "&C8&" "&D8&" "&E8&" "&F8&" "&G8&" "&H8&" "&I8&" "&J8, how do I get the comma?

Thanks again! This is going to save me SO much time!
Kimmy

[kimmyh3]

I also forgot to ask:

I have to do this for about 2300 cells. Do I just keep changing the line name?

Thanks!

[ChemistB]

It seems to be working, however, I do need the comma in between the words. If I use the formula: =A8& " " &B8&" "&C8&" "&D8&" "&E8&" "&F8&" "&G8&" "&H8&" "&I8&" "&J8, how do I get the comma?

Instead of " "between each reference, use ", ". IF you drag the formula down the column, the cells will automatically advance for each new row.

Now if you want to remove the formula and have it as text, you need to copy the column (with the formula) and Paste Special >Value. That will convert the formulas to text. Hope this helped.

[Palmetto]

how do I get the comma?

Comma added to formula.
=B8& "," &C8&","&D8&","&E8&","&F8&","&G8&","&H8&","&I8&","&J8&","&K8

I have to do this for about 2300 cells. Do I just keep changing the line name?

Because you may have empty cells in column-J, I would do the following to make it fast and easy.

Click cell A1, then right-click and choose Insert Column.

Put the formula into cell A8.
With A8 the active cell, double-click the fill-handle of the cell pointer. The fill-handle is the tiny square at the lower right of the cell pointer.

Excel will automatically fill down column-A as many rows as are filled in the adjacent column (B).

For the rows above A8, just drag the fill-hand upward to fill those cells with the formula).

[kimmyh3]

I seem to be receiving a Circular Reference Warning error message after I plug in the formula.



Any suggestions? I am running Excel 2007.

[kimmyh3]

I also do not need any spaces in between the words. Example:
Corporate,Real Estate,Tax-Federal

We are in the long process of updating databases and are currently working with a company to import our data.

Thanks!!!

[Palmetto]

I seem to be receiving a Circular Reference Warning error message after I plug in the formula.

I forgot to adjust the column references to account for inserting a new column. The formula in my previous post is corrected.

I also do not need any spaces in between the words.

Formula amended and spaces removed.

[ChemistB]

I also do not need any spaces in between the words. Example:
Corporate,Real Estate,Tax-Federal

Then Palmetto's formula (with ",") should work.

If you are getting a circular reference, that means that your formula is referring to the cell where you are putting the formula. For example, with your example, the formula cannot go into B8, C8,D8,E8,F8,G8,H8,I8,J8 or K8. You need to put it into either A8, L8, M8 etc.

[kimmyh3]

This is absolutely wonderful! It is working, however, I still have spaces between my comma's:

Corporate ,  Estate Planning/Wills ,  Business Law ,  Probate & Trust ,  Tax-Federal 

Is there anyway to remove the spaces without having to go through the document one by one?

[alex_c]

The first thing I would check is to see if there are spaces between the quotes in the formula. If there were no spaces there, I would then check to see if there were spaces inside the referenced cells (where the data is). So I would check the cell that said "Corporate" by clicking inside it to see if it actually said " corporate " with spaces before and/or after the visible letters.

[DonkeyOte]

The cells in your grid contain a few instances of CHAR 160, might be an idea to highlight the entire matrix and run an Edit -> Replace, in Replace With box type 0160 (numeric keypad) whilst holding ALT (if using a laptop hold ALT + FN and type 0160), in Replace With leave blank.

You could then in theory use a slight variant of the earlier code to generate the string

Please Login or Register  to view this content.