CS 100 (Learn) — CS 100 (Web) — Module 05
NOTE: If your internet access is restricted and you do not have access to YouTube, we have provided alternate video links.
Note: This video transcript has been slightly modified. Corrections are marked with strikethrough, and alternative wording has been placed in [square brackets] to correct some of the awkward or confusing phrasing in the videos.
In this video we are going to explore how to use text and labels in Excel.
We have cooked up a little example that you might [encounter] in a "real world" situation, where you want to illustrate [display in cells]:
Sub-total
Tax
Total
Your boss looks at the spreadsheet and says: "that was great, but I wanted [it] to look like this":
Sub-total
+ Tax
= Total
You say "no problem". You go back to your Excel spreadsheet and you want to make it look exactly like your boss [indicated].
You enter "+ tax" and now we have entered [encountered] a problem, because when I started typing "+ tax" Excel automatically assumed: "oh, you are using plus... you must be entering a formula... I am going to interpret this as a formula". Now it is going to look for [try and reference] a cell named "tax", but we have not named any cells "tax" so it [displays] an error message: "#name?"... means: "you used a name [that could not be found]".
How do we fix this? A little trick is to use the apostrophe ('). If you start [the contents of a cell] with a single apostrophe ('), it [indicates to] Excel that [the contents of the cell] should be interpreted as text (it should be interpreted as a label [, which is another term for text]).
Similarly we are going to have a problem [with =Total]. If I try to type [into a cell] =Total it does not know what total is (it looks like a formula), so [again,] what I want to do is use a single quote (') and then =Total.
If you are trying to enter something and Excels misinterprets it, forcing it to be text (by using the apostrophe) [may] be very helpful.
Another way that we can use text in Excel is by using quotations ("") directly. You may be familiar with air quotes or quotations in different contexts.
For example, if I showed you this message Billy is really "smart" you would interpret that word smart to mean not smart or not literally smart.
In Excel, [quotes almost have the] opposite [effect].
In Excel we can use quotations to indicate: "this is text, do not try to interpret [the contents of the text as] a formula". I will show you where that is going to come in handy.
If I use the equal sign to [indicate the content of the cell] is going to be a formula, and then I put a [double] quote around [text, it indicates:] "this is still a formula, but everything I put in the quotes [should be] interpreted as text".
For example ="= Total", appears the same [as '=Total].
The difference is [that] this [version] is a formula whereas, the [prior version] was just a text [value].
Where does this really matter? Let's [consider] a new example where that might be useful.
You are familiar with the mathematical operators like + and subtraction and multiplication. There is an operator that you can use working with text: it is called the concatenation operator and it is the ampersand (&).
For example, instead of "= Total", I could say: "your total amount is...", [and then] I use the ampersand, and then I want to reference the cell that [contains] the total amount.
="Your total amount is "&D5
Now we can see that it [nicely] displays at the bottom: "Your total amount is 114".
This might be something useful if you are going to be printing out your Excel spreadsheet to give us an invoice to a customer.
we can even be a bit fancier and [add] something like "please pay in 30 days".
With the concatenation operator, if you look at it [in the formula bar]
="Your total amount is "&D5&" please pay in 30 days"
The ampersand "glues together" two things. It is officially called a concatenation operator.
There is [also] a CONCATENATE function which does a very similar thing but most people just use the concatenation operator.
there are functions that can be used with text and not just numbers. for example, in this [example], the contents of D5 are a number (not text). I can force it to be text [if] I use the TEXT function: it [displays in the pop-up] that it converts a value to text in a certain number format. I am going to [use the TEXT function] on D5 to use the format "$0.00".
TEXT(D5,"$0.00")
this looks a little unusual but now we can see how it formatted the [contents of D5]: Your total amount is now $113.00 please pay in 30 days".
The TEXT function is an example of a function that takes a number and converts it to a text in a specific format. [It] is useful if you want to use it inside of a sentence like this.
There are other interesting functions. For example, the UPPER [function]. I say "what is the uppercase of this?". So maybe if they do not pay in 25 days we yell at them and say "Hey! you are supposed to pay in 30 days" or something like that.
You can yell at someone [with the UPPER funciton and] you can also convert [text] to lowercase.
You can also do things like: "let's take the left [of some text] and I only want the first 20 characters" [with the LEFT function].
I could [also] use the [RIGHT function], or you can [obtain text from the middle with the MID function]. I could say: "give me the middle of this text, starting with character number 10 and give me 10 characters".
=MID(C9,10,10)
If you count in 10 characters: "l amount I".
The MID, LEFT and RIGHT [funcitons] can be used sometimes to tease out parts of a text. These are all examples of formulas that use text. [There are additional functions] that we can use.
An interesting one is [the] FIND [funciton]. It finds the position of a character or another text [sequence] within [another] text [sequence].
Where this might be useful (for example) [is if] you have some email addresses "bob@uwaterloo.ca" and we also have "sally@uwaterloo.ca". What we might [want to] do is extract just the name from that particular piece of text.
What I can do first is find ampersand [the @ symbol] in that text. You will notice that it [produced the result that] the ampersand [@ symbol] is at position 4. [With sally,] the ampersand [@ symbol] is at position 6.
That is working great... I can [now] use [the] LEFT [function]: find the left of cell C13 and then the number of characters I want to keep is the number at [position of] the ampersand [@ symbol] minus 1.
=LEFT(C13,FIND(C13,"@")-1)
This is a little [tricky, so I will] review this before I hit enter: I am saying: "first find the ampersand [@ symbol] in cell C13... subtract 1 from it, and then I want to keep [that many] leftmost characters.
[In this example], we can see that we [extracted the text] "bob" and "sally". We were able to extract part of a piece of text.
Those are some examples of working with strings [text and labels] in Excel