Help

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

IF formula with emoji

Topic Labels: Formulas
Solved
Jump to Solution
4935 17
cancel
Showing results for 
Search instead for 
Did you mean: 
Sally_Wuu
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi!
Can someone tell me why my formula is not working?
IF({Rating}=1,
” :star: ”,
IF({Rating}=2,
” :star: :star: ”,
IF({Rating}=3,
” :star: :star: :star: ”,
IF({Rating}=4,
” :star: :star: :star: :star: ”,
IF({Rating}=5,
” :star: :star: :star: :star: :star: ”,
)))))

Basically, I want to convert a lookup value of integers into an emoji.

Many thanks.

1 Solution

Accepted Solutions

@Sally_Wuu If the first value can be null, then you have to stick with your original formula.

See Solution in Thread

17 Replies 17

What field type is your {Rating} field? Is it a number that you’ve manually entered? Is it a lookup/rollup from another table? Is it Airtable’s own rating field?

Type aside (for now), you could simplify this by using the REPT() function, which repeats a given item a specified number of times.

REPT("⭐️", Rating)

If {Rating} is 5, you’ll get five stars. If it’s 50, you’ll get 50 stars. The number directly drives the output.

Your formula has an extra comma at the end. That is why it isn’t working. You can remove the last comma before the closing parenthesis. (This is a very common error.)

IF({Rating}=5,
"⭐️⭐️⭐️⭐️⭐️",    <=== here is the extra comma
)))))

Or you can use @Justin_Barrett’s formula, which is a bit more flexible.

You are right - it is a lookup. I have used your suggestion and it works. I didn’t know we didn’t even need {braces}!

I tried it without the comma, it still didn’t work. Strange…

It didn’t work because the field was a lookup field. Sometimes there are multiple issues in a formula. I’m glad you got it sorted out.

Sally_Wuu
5 - Automation Enthusiast
5 - Automation Enthusiast

Since I have you two experts, can I ask if there is a sleeker way to write this formula?
{K1 age} & IF(AND({K1 age}, OR({K2 age}, {K3 age}, {K4 age}, {K5 age})), ", ")

& {K2 age} & IF(AND({K2 age}, OR({K3 age}, {K4 age}, {K5 age})), ", ")

& {K3 age} & IF(AND({K3 age}, OR({K4 age}, {K5 age})), ", ")

& {K4 age} & IF(AND({K4 age}, {K5 age}), ", ")

& {K5 age}

Basically I have 5 fields of kids ages (K1 to K5), but not everyone has 5 kids. I want to put all the ages together e.g. 10, 5, 2 and not have a result like 10, 5, 2, , , . So that was the formula I used - it works but I thought there might be a better way.

Thanks so much for your help so far!!

Braces are only required if the field name contains spaces and/or certain special characters. For example, if you use an emoji as a field name (which is valid), it will require braces.

Yes, this can be simplified. All you need to do with each field after {K1 age} is look to see if its value exists. If it does, add the comma and the field value.

{K1 age} & IF({K2 age}, ", " & {K2 age})
& IF({K3 age}, ", " & {K3 age})
& IF({K4 age}, ", " & {K4 age})
& IF({K5 age}, ", " & {K5 age})

Screen Shot 2020-06-12 at 8.23.37 AM

Thanks Justin. It works! But what if the first value is a null but the second is not a null? how can i tweak your formula to still get a 5, 2 and not a , 5, 2, ?

@Sally_Wuu If the first value can be null, then you have to stick with your original formula.