13

I have a Numbers spreadsheet. Column B contains first names, column C contains surnames.

I need an easy way to combine and reformat this data so that it’s in the format Surname, Firstname. I then need to get this data into a TextEdit document in text format.

My first step was to try this formula =C2,", ",B2 to combine the data, but this produced an error and I'm stuck.

  • Does anyone know how I can combine the cells in the order I want?
  • Once combined, how do I get the result as plain text into a TextEdit document?
5

This answer assumes:

  • you have a header row and, because of that, the first row containing your data is Row 2.
  • that Column D is empty

Please see my notes at the end if the above assumptions are incorrect.

Solution

Following these steps will solve your problem:

  1. In cell D2 enter the following formula: =(C2&", "&B2)
  2. Press enter
  3. Cell D2 should now produce the result you’re after (i.e. if cell B2 contained the name John and cell C2 contained the name Doe, cell D2 should now show Doe, John)
  4. Now copy the formula from cell D2 down all the rows in Column D
  5. The other cells in Column D should populate with the data you want
  6. Now select and copy all cells containing data in Column D
  7. Select cell E2 and press shift command V (or go to Edit > Paste Formula Results)
  8. Now select and copy all cells containing data in Column E
  9. Open your TextEdit document
  10. Go to Edit > Paste and Match Style

Now you should have all the data you wanted as text and in the format you need.

NOTE 1: - If your next blank column isn’t Column D, then at step 1 enter the formula in the next blank column (i.e. if your next blank column is Column F, then enter the formula in cell F2. This also means you’d adjust the other steps accordingly.

NOTE 2: - If you don’t have a header row, then at Step 1 you would enter the following formula instead: =(C1&", "&B1) into cell D1. This also means you’d adjust the other steps accordingly.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .