How do I return a cell's value instead of it's formula?-CodePudding

I've made a spreadsheet that is essentially a world cup prediction league.

5 points are awarded for a correct score 2 points are awarded for a correct outcome

The calculation sheet works as follows:

In the score column, a football emoji is displayed if the results match. It uses this IF statement: =IF(AND(I4=I$2,J4=J$2),"⚽","")

In the outcome column, a tick emoji is displayed if the outcome is correct and a x if it's incorrect. It uses this IF statement: =IF(OR(AND(I$2>J$2,I4>J4),AND(I$2=J$2,I4=J4),AND(I$2<J$2,I4<J4)),"✅","✖️")

In the end column, either 0, 2, or 5 points are awarded.

I wanted an easy way of inputting every player's predictions submitted via a google form, rather than having to type each prediction in manually.

I duplicated the form responses sheet so that I could re-order the player data, so their results matched up to the calculations sheet.

Each column is a separate fixture (eg - E2 England v Iran, F2 Senegal v Holland etc) The results have been submitted in each cell in the following format: 2-1

So I used =LEFT(E2,1) to capture the first teams score and =RIGHT(E2,1) to capture the second teams score. This was so I could split the score into two values in order to apply the formulas.

This worked, but the problem is that the football emoji doesn't appear if the score is correct, because the cell is referencing a formula rather than a value. This subsequently means that it doesn't award the player the correct number of points in the end column. (5)

Is there a way to tweak it so that correct score column reads the value rather than the forumula thus displaying the football emoji?

Help appreciated. Many thanks in advance!

I tried copying and special pasting the cells so it just displayed the values, but this didn't work unfortunately.

CodePudding user response：

LEFT and RIGHT are returning strings instead of numbers. Wrap those two formulas with VALUE. For example:

=VALUE(LEFT(E2,1))

That way your conditional would be able to compare the numbers and return the desired emoji

CodePudding user response：

output of

=LEFT(E2, 1)

is plain text. to convert it into value you can for example multiply it by 1

=LEFT(E2, 1)*1

or add zero:

=LEFT(E2, 1) 0

or use VALUE:

=VALUE(LEFT(E2, 1))

or try:

=--LEFT(E2, 1)