2

I am building a tracking sheet in Numbers for when I need to send my clients lists. I can't seem to figure out how to build a text string that contains information from all of the cells in one row.

I have columns that contain the subject's name, whether or not they have done an action and then the url to their personal gallery.

The last column is a formula to put together the name, , url link.

What I want to do is have a footer cell that then puts all of those text strings together.

Sum doesn't work on cells with text.

How can I add all of the text strings in all of the cells in one column together, without having to manually input each cell into a formula?

Some projects have 5 rows, which isn't too bad. However, the job I am currently attempting to track has 70 people.

It gets a little unwieldy to have to build a formula of A2&" "&B2"&etc etc etc.

Can I automate that in Numbers?

2 Answers 2

3

Another solution is to use the CONCATENATE() function.

See the knowledge base for further details.

Examples

If cell A1 contains Lorem and cell B1 contains Ipsum, =CONCATENATE(B1, “, “, A1) returns “Ipsum, Lorem”.

=CONCATENATE(60, 60) returns “6060”.

=CONCATENATE(“a”, “b”, “c”) returns “abc”.

=”a”&”b”&”c” also returns “abc”, because the ampersand is the concatenation operator.

Concatenate function Function result

To add to every row, just drag the formula handle to copy the formula down. Copy formula

2
  • That works going across the row. Is there a way to automatically concanate all of the cells in a column, without manually putting in each row? Since the number of rows will change on each job, it would be a time saver to be able to just be able to copy a footer cell at the bottom of the column and paste that in an email.
    – Matt McKee
    Mar 23, 2020 at 17:36
  • All it is are cell references. They can be any cell in rows columns or anywhere in the document. Even other sheets. This is common to all spreadsheet applications. Cheers.
    – bjbk
    Mar 23, 2020 at 17:40
1

You can combine cells with strings by using &. So in your case it probably will be something like

=C1&" "&C2&" "&C3

with C1/2/3 being the actual column references.

2
  • Cool. Is there a way to set it up so that every time I add a new row, it will automatically add that row to the formula? Or do I have to hard code every row?
    – Matt McKee
    Mar 23, 2020 at 17:06
  • 1
    @MattMcKee You can pull down the formula to other rows, as shown in the other answer.
    – nohillside
    Mar 23, 2020 at 17:54

You must log in to answer this question.

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