Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Combining different data fields into one column

Solved
Jump to Solution
3142 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!!