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.
