Re: Swaping and merging two columns

Haines Brown said:
Haines Brown said:

However, to do that I need to reverse and combine the data in the
first two colums so that it becomes one column with its cells holding
"FirstName Lastname". Any copy/pasting or merging I try overwrites the=20
data in the target cell rather than append to it.

What to do?
Something like this?
=B2&" "&A2
Kind regards,
Hal Ashburner

Thanks, Hal. After doing some reading, I believe I understand the
formula, and after some stumbling about was able to get it to work.

However, in my case, since I want to swap and concatenate whole
columns, I need to generate the data for each column. I tried using a
range of cells such as:

 =B2:B500&" "&A2:A500

and got nowhere. An ENTER just moves my selected cell down.

I tried to use the concatenate function:

 =CONCATENATE(A2:A4," ",B2:B4)

This concatenates the concatenation of the data in cells in column
A with the concatenation of the data of the cells of B. Interesting,
but not what I want.

It occurred to me I might have to do an array, but the manual was
sketchy on arrays. If I try on a simple little test worksheet:

 ={concatenate(B2:B4," ",A2:A4)}(3,1)[11,3]

where I try to concatenate the data in each cell of range B2:B4 with
each cell in range A2:A4, and with the result displayed in an array 3
rows tall and 1 column wide, at position at row 11, in one cell, I
get, after doing Ctl-Shft-Enter, the error: "Invalid Expression". This
probably because I have no examples of an array to work with, and the
terms used in the syntax of the array were assumed to be intuitively
obvious, which for someone like myself who has never touched a
spreadsheet, they were not. 


      Haines Brown
Hi Haines,
I might be missing the point here, but lets find out.
If you have last names in column A
Given names in column B

but what you want is a column containing Given names and last names

in cell C2 enter =B2 &" " & A2
then copy this forumla down into C3, C4 C5 etc. The formula updates for each
row as it is copied. This is called relative addressing.

If you don't want the the formula to update as you copy it around, you use
absolute addressing.

will refer to the contents of cell A1 no matter where you copy it

the concatenate function, does the same thing as the & however with less
flexibility imho.
If I understand what you want, I'm pretty sure you don't need array
functions to do it.

Ok so in column C you now have a column full of the stuff you want, but you
want to export that as a csv without the source columns (col A and col B)
You can't just delete them, because that changes the result of the formulas
in col C.
So what do you do? 
Select column C
Edit->paste special, then chose "As Values"
now col C is full of text rather than formulas and you can delete columns A
and B to get them out of the way.

All of this is 100% compatible in result and methodology with the most
popular proprietary spreadsheet. Nice work by those who did it, huh?

Hope that helps.

Kind regards,
Hal Ashburner

Attachment: signature.asc
Description: Digital signature

[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]