Concatenation of a Column

[Melanie_Tobin]

Is there any way to concat all the values in a column? Bascially I have 60 email addresses and I want to concat so that I can reference all the emails at once. Need all in one field. Similar to the CONCATENATE(TRANSPOSE()) functions in excel? Any chance?

[Elias_Gomez_Sai]

Yes. Check the Formula field reference: https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference#text

[W_Vann_Hall]

If you’re talking about 60 rows, each with an email address field, that you want to bring together into a single field, you can do that, as well. Essentially, you’ll need to link each row in your table to a single row in another table (see the Help section on linked records).

The easiest way to do that is as follows:

1. Define a new single-line text field in the table.
2. Enter any value in the new text field for row 1. I typically use '.' (the period character).
3. Click on the fill handle for the cell — the small white box that appears in the cell’s lower right-hand corner — and while holding down the mouse button, drag the handle to the bottom row of the table. Now, in each row the field should have the value '.'.
4. Right-click on the header for the new text field and select ‘customize field type.’
5. Change the field’s type from text to ‘link to another record.’ Select ‘create new table’ and name it.

Now, every row in your original table is linked to the sole record in the newly created table, a record with a primary field named '.'.

In your newly created table, define a rollup field that follows the link back to your original table and rolls up the email field. Your aggregation function will depend on how you want to present the emails. If you want them in a single field but displayed one per line, use this function:

ARRAYJOIN(values,'\n')

If you want them as a comma-separated list, use

ARRAYJOIN(values,', ')

Other formats are possible, as well.

[Melanie_Tobin]

GET OUT! Days I tell you! I danced all around it but never got it. THANK YOU SO MUCH!!! I really needed this!

[Michelle_Sawwan]

In case anyone is trying to concatenate columns with cells that link to other tables, I found another way to do this:

1. Create a new column, to the right or left of the columns you want to combine.
2. Input a regular concatenation formula, using the desired column values as inputs. Make sure to include a comma to keep desired values as discrete, separate units. The concatenated column will have a line of text that contains all the previous columns’ values, but they won’t be linked to the desired table yet.
3. Duplicate the concatenated column, which will still have the single-line text format
4. In the duplicated column, go into “customize field type,” select “Link to: …,” and select the table to which you want the column to link.

This should accomplish both keeping original values separate, and linking them to the desired table once you have the final column ready.

[Play_Pomarejos]

Hello. I tried this but using the formula does not list the result on a per line. it list them in a single cell.

Hope you can help me. Thank you

[Kamille_Parks]

Play_Pomarejos:

the formula does not list the result on a per line

The data will be one per line using the formula but your row height is too short to show it as such. You have to change your row height from “short” to “extra tall”, or expand the record, or expand the cell.

[Play_Pomarejos]

Thanks for your reply. You mean it will be on a per line but will stay in a single cell?

Is there a way to make it appear in every row instead of single cell?

[Kamille_Parks]

I’m not sure what you want to accomplish. @W_Vann_Hall 's process does what OP wanted: get the values of multiple records to appear in one cell.

If you values to appear one per row, can you not just make a text box column and type values like normal?

Unless you want all rows to show the same list of values. Then follow the given instructions and back in your original table, add a Lookup field that points to the table with ‘.’ unifying record amd the field in which the emails are listed. That should make every record (row) in your first table have a cell that includes all the emails in the table.

[Play_Pomarejos]

Here’s what I am trying to accomplish if possible:

Based on the image above:
In Table A, I have Data1 and Data2, and then Data3. I want Data3’s Unique Values to reflect in Table B - Data3.

In Table A, Data 3 is a result of concatenate formula. Data1 and Data2 may change anytime, thus when changed, will subsequently change the result of Data3 in Table A.

Table A’s Data3 Field should stay as formula field. If I try to make it a linked field and link to TableB, it will change the formula to link field and will not update when a change is made in TableA’s Data1 and Data2. we dont want that.

Thus, what I did is add Data4 field, make all the field values as “.”, or period, then link it to Table B. In Table B’s results, I’ll roll up and want the results to appear in every row of the table.

I used roll up ARRAYUNIQUE(Values) and ARRAYJOIN(VALUES,’\n’) but the results appear in one field.

Please help.

Thank you