Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# IF formula with emoji

Topic Labels: Formulas
Solved
1108 17
cancel
Showing results for
Did you mean:
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
18 - Pluto

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

17 Replies 17
18 - Pluto

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.

18 - Pluto

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.

5 - Automation Enthusiast

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}!

5 - Automation Enthusiast

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

18 - Pluto

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.

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!!

18 - Pluto

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})
``````

5 - Automation Enthusiast

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, ?

18 - Pluto

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

18 - Pluto

I’m having a hard time understanding why the first value would ever be null. If K1 is the first child, why would someone bypass the `{K1 age}` field and enter the first child’s age into `{K2 age}` instead?

5 - Automation Enthusiast

Hi Justin, because I am not making the k1 age compulsory and if they happen to leave it blank, I don’t want it to appear as a null comma field. Of course, that begets the question why not make it compulsory? I’m still thinking it through…

18 - Pluto

In that case, here’s an alternate way to approach it:

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

This will work no matter where the numbers are placed.

5 - Automation Enthusiast

Thanks again Justin, could I ask, between this new formula and the original one, which will be a greater drain on processing power should we need to host it ourselves?

18 - Pluto

There’s no way to track how much processing power a formula uses. My hunch is that the difference between them is negligible.

What do you mean by “host it ourselves?” Airtable runs completely online. There’s no local hosting option.

5 - Automation Enthusiast

I suppose there’s the possibility of downloading the data onto a spreadsheet or MS access and have those chug them out if I decide to move away from Airtable.

18 - Pluto

True. However, back to your processing power question, Airtable formulas don’t place any kind of noticeable drain on modern computers. The power required is so small that it’s not even worth measuring, even for formulas far more complex than this.

5 - Automation Enthusiast

I just had a bad experience with excel on a Mac a while ago. Can’t believe MS excel doesn’t allow multi-core Macs to maximise processing power - only one core will be used for processing. I wrote a very clunky formula. And it took me hours/days to get a response for a whole table.

Thanks SO much for your time on this. So much appreciated.