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 --=20 Kind regards, Hal AshburnerThanks, 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 KB1GRM
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. =$A$1 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->copy 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