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.

5 Easy Steps for Teaching with Digital Tools

Introducing digital tools into your teaching can seem challenging at first. There are many tools out there, and it might feel like there’s a lot to learn. But with easy steps for teaching with digital tools, you can start simply and gradually. There’s no need to dive in all at once – just take it one step at a time. Taking easy steps for teaching with digital tools starts with trying something!

Read More »
Students Prepare to Present

Help Your Students Prepare to Present

Maximize student success in presentations with ‘Speaker Notes by AliceKeeler,’ the ideal Google Slides add-on. Enhance how students prepare to present with easy transfer of speaker notes to Google Docs, promoting effective communication skills. Dive into the world of engaging, technology-aided presentations and empower students to shine in their academic endeavors. Discover the key to transforming student presentations into interactive, skill-building experiences.

Read More »

Infographic 7 Basic Steps for a Google Form

New to using Google Forms? This tool is essential for teachers to not only save time but to be adaptive to student needs. Use Forms to survey students, play games, personalize instruction, and assessment. This infographic on the 7 basic steps for a Google Form will help you get started.

Read More »

Online Workshop: Get Your Add-on Published

How can you make Google Workspace (Docs/Sheets/Slides) even better? You can create custom Add-ons with Google Apps Script. Wondering How to Get Your Google Workspace Add-on Published? Join Alice Keeler, Google Developer Expert, to learn the steps to get your Add-on officially published.

Read More »

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.

%d