In my attempt to create my own gamified gradebook I needed to be able to give a student a score and comments. The problem is how do you input both values into the same cell?! My solution was to separate the score from the comments using a comma. For example: 12, You did a great job!

Find the Comma

The first problem I had to overcome was locating where to split up the number from the comment. In the case of 12, You did a great job! the comma is the 3rd character. However, in the case of 5, great use of irony! the comma is the second character.

=Find()

The formula =find will locate a text string and return the location of the text string.

=find(“,”, A1)

Remember that all of your text strings need to be in quotation marks. The formula =find(“,”, A1) says to find the comma in cell A1.

Numbers

In my examples the score is the characters to the left of the text string. I need to extract these left characters.

=left()

The formula =left() will return the characters on the left hand side of text string.

=left(A1, 2)

The formula =left(A1, 2) will return the left 2 characters. That is great if my scores in my gradebook were always 2 digit numbers. Instead I want to return the number of characters up to the position of the comma…. minus one. We do not want the comma.

=left(A1, find(“,”, A1) -1)

Text to Numbers

The dilemma is that the numbers we extracted from the text string still are considered text, not a number. This is a problem if you want to add up the scores.

=value

The formula =value() will convert text to a number.

=value(left(A1, find(“,”, A1) – 1))

Placing the formula =value() around the left function will convert the results to a number.

Comments

To extract the comments I need the characters to the right of the comma.

=right()

The formula =right() will return the characters to the right of the text string. Unfortunately, the comma counts how many characters the comma is from the left. To determine the number of characters to the right of the comma that you want to extra you need to subtract the position of the comma from the length of the text string.

=len()

=len() will count the number of characters in a text string.

=right(A1, len(A1) – find(“,”,  A1) – 1)

The formula =right(A1, len(A1) – find(“,”, A1) – 1) will return the characters to right of the text string. Notice that your score, comments has a space after the comma. Since you probably do not want your comment to start with a space you will want the length to be minus one to remove the space.

Try It

CLICK HERE for a sample gradebook.

On the 2nd tab write the formula to extract the score from the first sheet.

On the 3rd tab write the formula to extract the comments.

Check It

CLICK HERE for a sample answer.