[Solved] Combining columns of text into one column

[mendobruce]

I am trying to combine two columns of text into one. The merge cells combine just gives me a big blank box. When I try to paste the contents of one into another it just over writes the original. Special paste gives me crazy results, I am lost! Another app I am using requires a cvs file with one field for full name and my sheet has separate columns for first and last name. Can someone help this idiot beginner?

[thomasjk]

Try Insert Function--->Concantenate function in a column. Select the other 2 columns as parameters. Then use Copy and Paste special to replace the formulas with the text. Leave the Text box checked. Make a backup before you begin.

[robleyd]

There are several ways you might do this. Assuming first name is in A1 and last name in B1, in C1 use the CONCATENATE function:

Code: Select all

=CONCATENATE(A1;" ";B1)

or the concatenation operator &

Code: Select all

=A1&" "&B1

In both cases if A1 contains John and B1 contains Doe, C1 will display John Doe

If you are new to spreadsheets, you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know

OpenOffice Spreadsheet Tutorial for Beginners with Examples

[mendobruce]

I tried that and it worked great, but when I tried to delete the original first name - last name columns, my new column lost it's data and just displayed #REF

[robleyd]

Then use Copy and Paste special to replace the formulas with the text. Leave the Text box checked. Make a backup before you begin.

[Bald Eagle]

I tried that and it worked great, but when I tried to delete the original first name - last name columns, my new column lost it's data and just displayed #REF

Yes.
That's expected behaviour - it's a spreadsheet. It's "live"
If your formula in C1 is based on A1 and B1, when you change the content of those cells --- the calculated result in C1 CHANGES.
Which also means that is you DELETE A1 and B1 - there's nothing for C1 to calculate FROM.

So you do the calculation, then highlight Column C, and copy.

Now go somewhere else - column E, another tab, another spreadsheet file entirely....
and [CTRL][SHIFT][V] or Edit - paste special - and select the special pasting options to only paste the contents - NOT THE FORMULA.
That will paste the calculated results as "dead text" rather than a live calculation linked to the contents (and existence) of A1 and B1.

[mendobruce]

tHANKS