Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Spreadsheet Formula for Collecting Twitter Handles

Spreadsheet Formula for Collecting Twitter Handles

Twitter is a powerful tool for Professional Development. Asking educators for their Twitter handle is a great way to connect with educators you are working with. Several teachers also use Twitter with their students, it would be helpful to have a list of student names and Twitter handles in order to connect and communicate with them.

The problem with asking for Twitter handles in a Google Form is that participants are not sure if they should include the @ symbol or not. Depending on what I need to do with it, I may want it  either way.

From the screenshot above you can see that some participants included the @ and some did not. In this case I DID need the @ symbol. So I wrote a formula in the spreadsheet to check for the @ and add it if it was not already there.

Adding the @ Symbol

In a blank column you will want to use the =if function
=if(condition,true,false)

The =left function selects characters from the left hand side of the text string. (Note, in other circumstances you may want to use =right.)

=left(B2,1) will return the left most character. I want to test if that is the @ symbol.

Type =if(
Then add the check for the left character left(cell,1)=”@”
Notice the @ symbol is in quotation marks, as all text strings need to be.

Type a comma. If the left character is the @ symbol we want it to return the entire Twitter handle the user typed in. Type the location of the cell with the Twitter handle.

Type a comma. If the left character is NOT the @ symbol, we want it to add it. Use concatenation to append the @ symbol to what is in the cell containing the Twitter handle. In quotations put the @ symbol. Concatenate that with the Twitter handle by using ampersand (&).

Assuming the Twitter handle is in cell B2 your formula would look like this

=if(left(B2,1)=”@”,B2,”@”&B2)

 Flip it Around

If you have the opposite issue where the cells contain the @ symbol but you do not want them to write this formula instead.

=split(B2,”@”)

The =split function will break up the text anywhere it sees the @ symbol. It also will “eat” the @ symbol, so this is a nice tool for stripping off the character.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

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

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

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