Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Combining different data fields into one column

Solved
Jump to Solution
2346 9
cancel
Showing results for 
Search instead for 
Did you mean: 
bartdubs
4 - Data Explorer
4 - Data Explorer

Hi there,

I want to have a column which displays two bits of data. Those are a cumulative $ amount and below that a cumulative % amount. I have these two lookup fields separately however need to combine them into one field column. Any help would be greatly appreciated,

Bart

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

for example like this

IF(
  {Rollup profit ($)},
  "$" & {Rollup profit ($)}
)
&
IF(
  AND({Rollup profit ($)},{Rollup profit (%)}),
  ", "
)
&
IF(
  {Rollup profit (%)},
  INT({Rollup profit (%)}*100) & "%"
)

See Solution in Thread

9 Replies 9
Sho
11 - Venus
11 - Venus

Hi @bartdubs,

Two fields can be displayed as one by using the "&" operator symbol in the Formula field.

Name & " - " & Age
=> Jane - 28

"\""&{Product Name}&"\""
=> "GreatProduct"

Name & " \n" & Age
=> Jane 
28

Formula Field Reference - Overview | Airtable Support

Hi @Sho 

Thanks that makes sense. However, when I do that it inserts both values as basic numeric values. How would I be able to do it where it keeps the percentage as a percentage and the $ value as a $ value?

Thanks,

Bart

Sho
11 - Venus
11 - Venus

If you want to display percentages or amounts as strings in the Formula field, you must also write such formatting yourself.

"$" & {Value}

INT({Value}*100) & "%"

 

bartdubs
4 - Data Explorer
4 - Data Explorer

@Sho When using the formula

 

"$" & {Rollup profit ($)}, INT({Rollup profit (%)}*100) & "%"
 
It is just saying invalid formula. Is there any specific reason this might be?
(The fields labeled as rollup are both lookup fields)
Sho
11 - Venus
11 - Venus

A little more!
"," is also a string.

"$" & {Rollup profit ($)} & ", " & INT({Rollup profit (%)}*100) & "%"

 

That's a lot better thank you very much

bartdubs_0-1694685594957.png

Last bit.....Is there a way to remove the excess symbols?

Sho
11 - Venus
11 - Venus

for example like this

IF(
  {Rollup profit ($)},
  "$" & {Rollup profit ($)}
)
&
IF(
  AND({Rollup profit ($)},{Rollup profit (%)}),
  ", "
)
&
IF(
  {Rollup profit (%)},
  INT({Rollup profit (%)}*100) & "%"
)

If you mean floating point issue when displaying currency value, use ROUND({Rollup profit ($)}, 2) , for example, to preserve 2 digits after comma.

thank you very much!!