Merging columns, not cells

users
#1

I can’t find help for merging columns or ranges. Is it possible? Merging cells is so easy, but I hate to have to do it a million times.

Mark W. Howe
San Juan Capistrano, CA
949-496-3453 home/ office
949-525-3914 cell [not for messages]

"Phao Binh" http://swcenter.fortlewis.edu/finding_aids/inventory/2008008.pdf

#2

Mark,

With merging I assume you mean concatenating the strings into one column...

I generally put a concatenate formula in a separate column, then select cut that complete column and special past values to the desired column.

Rob.

#3

I don't think so. You can merge two cells into one. Or a range of cells into one - so a column or columns into a single cell. But I don't see any way of merging, say, two adjacent columns into a single column of cells - other than the piecemeal way you've described. If you find a way, please let me know!

As a workaround (and as has already been suggested), you can
o Construct a formula to concatenate the contents of each pair of cells into a new column.
o Cut or copy the result and use Paste Special (no Formulae) to paste it back over one of the columns.
o Empty the other column?

But you still end up with a single column of populated cells, not a range of merged cells (if that matters).

I trust this helps.

Brian Barker

#4

Hi :slight_smile:
It might be possible by saving as a .csv file (possibly losing all the
formulas and certainly all the formatting) and then use a text-editor
to remove some of the commas.

It's not much more elegant and would probably take even longer and
maybe even make quite a mess - but it might be an option!
Regards from
Tom :slight_smile:

#5

Brian and Rob;
Can you give me an example of a concatenation formula for a column of text merged with a column of numbers?
The resulting column would have the text and number in one cell.

#6

You can concatenate values using the CONCATENATE() function or - more easily - using the & operator. I'm guessing that you might want an intervening blank between your text and the number, so you need something like
=CONCATENATE(A1;" ";B1)
or just
=A1&" "&B1

This works equally with numbers, which are naturally converted to text on the fly. But remember that the number stored in a cell is not necessarily what you can see: this is also influenced by the cell formatting. The cell formatting of the number is not respected by the automatic conversion, which instead uses the full expression of the stored (or calculated) value. So you may instead wish to control this conversion using the TEXT() function, e.g.
=A1&" "&TEXT(B1;"0.000")
The format codes used in the TEXT() function are the same as you use (and can see) in the "Format code" box of the Format Cells dialogue.

I trust this helps.

Brian Barker

#7

Re: Merging the contents of two columns into a single column.
For what I am doing this method worked perfectly. I would have loved to have these instructions in the help pages.
Thanks Brian and Rob.

I am now starting to think of all kinds of ways to use this.