How to copy multiple cell values into one cell

Hi Sam,

The concatenate operator & will join strings together.

In B1 this formula:

=A1&", "&A2&", "

and so on to account for each cell in A.

Another way is to copy Column A into TextEdit. Then Edit > Find > Find and Replace.

With the cursor in the Find box, hold the option key and press enter. That will put a return character in.

In the Replace box, type a comma then a space.

Click on All.

Copy and paste into cell B1 in Numbers.

Regards,

Ian

Sam,

One simple way to use a spreadsheet to convert a column of values into a string separated by a comma all in one cell goes like this:

In cell B1 you bring in the value in A1 with the formula: =A1

In cell B2 you put the formula: B1&","&A2

Then fill the formula in B2 down the rest of the column by clicking once on B2, hovering your cursor over the bottom edge, and dragging the yellow dot that appears downward:

The last cell in column B will have all the values in one cell, separated by a column.

Then you can command-c, select another cell, and Edit > Paste Formula Results if you need them somewhere else in the spreadsheet. Or just Edit > Paste in any text editor or email program if that's what you need.

SG

To offer something different, here is an AppleScript that will do this if you're using Numbers 3.1.

1. Copy script from below and paste into AppleScript Editor, then "hit the hammer" to compile.
2. Select the cells with the numbers in column A.
3. Click the green run button in AppleScript Editor.
4. Paste the result in the cell at the top of the second column (or wherever you want in any application on the Mac that can accept text).

--select cells with values, run script, select destination in Numbers or elsewhere, paste

set delimiter to "," -- change to space or tab as needed

try

tell application "Numbers" to tell front document to tell active sheet

tell (first table whose class of selection range is range)

set my_selection to the selection range

tell my_selection

set paste_str to ""

repeat with this_cell from 1 to (count of cells)

tell cell this_cell

set not_blank to value is not missing value

if not_blank then set paste_str to paste_str & value & delimiter

end tell

end repeat

end tell

if length of paste_str < 1 then error -- no non-blank cells selected

set paste_str to (characters 1 thru -2 of paste_str) as text --remove trailing ,

end tell

end tell

on error

display dialog "Select Cells" buttons {"Cancel"}

end try

set the clipboard topaste_str

display notification "Ready to paste" with title "Numbers"

return paste_str--this line optional - for testing in AppleScript Editor

-- end of script

SG

Hi Sam,

My answer might differe in a few words, but would be essentially the same as Ian's.

I'm curious, though, why do you want to put all of these seemingly similar pieces of data into the same cell?

Regards,

Barry

what I would have suggested (had Ian not already done so). 😉

Have to read the threads more carefully. Don't know how I missed Ian's post. Apologies to Ian. Well, at least I added some illustrations.🙂

SG

Copy and past the table into Pages.

Under Tables click Convert Table to Text

Open Find/Replace

Replace the paragraph return with ", "

Replace all

Then, copy the paragraph of numbers and paste them back into a cell in Numbers or wherever you need them.

Sam,

Once you have all that in one cell, what do you plan to do with it?

You can make it look like it's all in one cell if you set the horizontal cell borders to None.

Jerry

@SG

Brilliant! Certainly cuts down on the point and clicks and fiddly bits in what I would have suggested (had Ian not already done so). 😉

Regards,

Barry

Dustin,

Your solution assumes that the user has access to Numbers 2.3.

The Numbers 3.2 solution would be to Paste into a Text Box, then move or edit as required.

Jerry