Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Google Sheets: Splitting Numbers from Text

Google Sheets: Splitting Numbers from Text

Sample Gradebook

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.

1 thought on “Google Sheets: Splitting Numbers from Text

  1. Thanks Alice. You always provide clear, detailed, logical explanations. They always teach me a great deal more than just the answer to the specific question you were solving.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 21st or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session as we learn how to revolutionize your assessment strategies. We will dive into the essentials of crafting high-quality rubrics that go beyond traditional scoring methods to offer rich, meaningful feedback. We’ll learn about the components of an effective rubric and how to mathematically ensure the accuracy of your assessments.

Exit this pop up by pressing escape or clicking anywhere off the pop up.