Skip to main content

Hi I am combining 2 fields (quantity and materials description) using the formula function (screenshot 1).



I am then using the lookup field to display in one cell all the materials and quantities for one job in that one cell (screenshot 2). So far so good.



I would like to be able to do a nice print out for the workshop staff but in order for it to look less cluttered, is there any way I could program it to display each (material & qty) to be displayed on a new line (new paragraph) within one cell?



e.g. rather than 2 x 22mm MDF, 4 x 18 mm Birch plywood, 6 x inset hinges



I would prefer it to look like this:



2 x 22mm MDF,


4 x 18 mm Birch plywood,


6 x inset hinges







Any ideas gratefully received!

Welcome to the community, @Camilla_Johnston!



You can do this with the following formula:



SUBSTITUTE(


ARRAYJOIN({Your Lookup Field}),


",","\n"


)


Note that Airtable won’t visually show you the line breaks in the grid view unless you:



(a) heighten your rows, or


(b) expand the cell by clicking on the tiny blue “expand cell” arrows.

You can also convert your lookup field into a rollup field.



ARRAYJOIN(values, "\n")



Using a rollup field instead of a lookup + formula field will





  • have one less field, for a less cluttered base


  • allow you to have internal commas in the individual line items


Welcome to the community, @Camilla_Johnston!



You can do this with the following formula:



SUBSTITUTE(


ARRAYJOIN({Your Lookup Field}),


",","\n"


)


Note that Airtable won’t visually show you the line breaks in the grid view unless you:



(a) heighten your rows, or


(b) expand the cell by clicking on the tiny blue “expand cell” arrows.




Thank you so much! It works!!

You can also convert your lookup field into a rollup field.



ARRAYJOIN(values, "\n")



Using a rollup field instead of a lookup + formula field will





  • have one less field, for a less cluttered base


  • allow you to have internal commas in the individual line items



Thanks so much for your reply - I did try to do a rollup field instead using ARRAYCOMPACT(SUBSTITUTE(ARRAYJOIN(values), “,”, “\n”))


but it displays some blank fields so has a big gap before showing the values. It would be preferable to have less fields but I’m getting stuck with how to delete the blank values.

Thanks so much for your reply - I did try to do a rollup field instead using ARRAYCOMPACT(SUBSTITUTE(ARRAYJOIN(values), “,”, “\n”))


but it displays some blank fields so has a big gap before showing the values. It would be preferable to have less fields but I’m getting stuck with how to delete the blank values.




Your functions are nested in the wrong order. You need to compact the list before joining.



ARRAYJOIN(ARRAYCOMPACT(values), "\n")


Your functions are nested in the wrong order. You need to compact the list before joining.



ARRAYJOIN(ARRAYCOMPACT(values), "\n")

Fantastic - it works yet again!!! Thank you both SO much!

My airtable has two fields - 1st field name is "Test" and 2nd field name is "Solution". Records from both fields are come from Lookup of other fields so multiple records appear in "Test" and "Solution". I use below formula to present Test 1, Test 2, Test 3 in separate lines under "Test" fields. Solution 1, Solution 2, Solution 3 in separate lines under "Solution" field. 

SUBSTITUTE(


ARRAYJOIN({Your Lookup Field}),


",","\n"


)

My question is how to present the information in pair but separate line?  e.g. 

Test 1

Solution 1

Test 2

Solution 2

Test 3

Solution 3

Reply


Terms & Conditions