Excel Text Functions: Learn them in 30 Minutes! | Excel Classes | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Excel Text Functions: Learn them in 30 Minutes!

teacher avatar Excel Classes, Excel teacher

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

11 Lessons (33m)
    • 1. Introduction: Why Learn Text Functions?

      1:11
    • 2. Changing Text Formatting: Useful Tips

      5:21
    • 3. How to Extract Text with LEFT

      3:40
    • 4. Combining Text with CONCAT

      3:30
    • 5. Combining Text: CONCAT or CONCATENATE?

      2:04
    • 6. Combining Text with TEXTJOIN

      2:38
    • 7. Extracting Text with LEFT, RIGHT, and MID

      2:26
    • 8. Searching and Extracting Text with MID and SEARCH

      7:10
    • 9. Changing Text with SUBSTITUTE

      2:18
    • 10. How to Convert data to Text: A Useful Tip

      2:50
    • 11. Thanks for watching!

      0:16
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

372

Students

1

Project

About This Class

What are Text Functions in Microsoft Excel?

Although Excel is mostly associated with numerical data and analysis, it's also really useful for managing text based data. Text functions allow you to change the format of text, combine different text strings, extract parts of a text string, substitute one text string for another, and more!

Who is this class for?

This classed is designed for any Excel user (beginner to advanced) who wants to learn about text functions. Text functions are useful for a wide range of tasks and professions, although are usually not the most common functions for Excel users to learn, so if you don't think they could be useful for you to learn, you might be surprised!

If you're completely new to Excel, you'll benefit from taking my 'Excel for Beginners: Learn the Essentials in 50 Minutes' class before taking this class.

What will I learn?

In just 30 minutes, this class covers the following topics:

  1. Changing Text Formatting: Useful Tips
  2. How to Extract Text with LEFT
  3. Combining Text with CONCAT
  4. Combining Text: CONCAT or CONCATENATE?
  5. Combining Text with TEXTJOIN
  6. Extracting Text with LEFT, RIGHT, and MID
  7. Searching and Extracting Text with MID and SEARCH
  8. Changing Text with SUBSTITUTE
  9. How to Convert data to Text: A Useful Tip

Meet Your Teacher

Teacher Profile Image

Excel Classes

Excel teacher

Teacher

Hello, I'm Julian. 

I'm a digital marketing consultant with 10+ years experience in using and teaching Excel, having worked for a number of agencies and international brands.

I'm passionate about teaching Microsoft Excel in a simple and straightforward way. Check out my classes today to learn more about Excel :)

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: Why Learn Text Functions?: in this class, we're going to cover the topic of text functions in Microsoft Excel When people think of excel, they typically think of numerical data. For example, you might think of using Microsoft Excel to analyze financial data or to look at sales reports containing lots of numbers and charts. But data can also be tax based on. There's lots of useful things you can do in Excel to manipulate and extract meaning from information that's in the form of text. In this class, we're going to be running through the most important and useful functions that will give you the skills and best practice tips you need to work with. Text in Excel will be covering how you can use Excel Teoh, change the format of text. Combine different text strings, extract parts of a text string, substitute one texting for another on more. If you're wondering what the benefits of being able to use text functions are just a few examples include being able to clean up and tidy, poorly formatted spreadsheets, being able to join up different text rings to form content for a website, for example, or extracting information to create new data and you might find existing things you do in Excel will be a lot quicker and easier once you've learned some text functions. So factor enrolling in this class and let's get started. 2. Changing Text Formatting: Useful Tips: in this video, we're gonna look at the lower upper trim and proper functions. Now, these really useful functions as they're a great way. Teoh, get your text data looking consistent so that you don't have sort of discrepancies in the way that they're written out. So, for example, in our product list here, we've got all of the products listed, but they look different in their appearance. So, for example, table is with an upper case T at the beginning frame is all lower case. Footstool is all uppercase. Chair has some spaces at the beginning on, then although you can't see immediately bast all, there are some spaces after some of them. So there, we've got a space. Where is here? We don't On overall, There's just a lack of consistency in the formatting. Um, for the products listed in column A. So what we're gonna do is just create a new column here in this table of data. We're gonna pull this new product from name. We're just gonna use this column. Teoh, illustrate how these different functions work. We're going to start off with lower. So all you have to leave the low is typing equals lower and tab. Then select the cell that you want to adapt. So a to now, obviously this is already lower case. So there's not been any sort of visual change there if we just drag that down, So double click to fill down. Um, we can see that thesis Els where there was an upper case. So the capital t there and then this was all uppercase has been converted into lower case former. So we've now got a consistent case formatting across there. Now we could also do equaled upper. So we're gonna remove these on just equals upper, and this just does the complete opposite. So it converts the text to upper case rather than lower case. So hit, Enter on. If we just fail these down, you'll notice again quite clear to see that a law or the text is now consistent and column B So everything is upper case. There's no let lower case characters here at all. Now let's have a look at trim, so just delete those Now what trim does is it removes all spaces from a text oring except single spaces between words, so we can do an example of that equals trim, Select, Sell a two and toe. Then we'll just fill those down on what you'll notice is, Is that where we've got chair here, for example, Whether is those spaces before the see those have been removed? There wasn't actually any spaces in these cells here, but at the top here we had spaces after bastar, although no in these ones on, if we were to pace, this is a value. You'll see that the spacing has gone there, so it made everything consistent In terms of spacing, the only spaces that are left are the ones that should be there. So where we've got them between words. So that's trim, and then we'll just delete those. And then lastly, we'll have a look at proper now proper. What it does is it just makes the first letter of each word a capital letter. So in bar stool, for example, will just click that both the B and the S will become capital letters like so And then if we just Phil down alot of the other words are only one word anyway, so it's just made the first letter a capital so lower Casey to uppercase see etcetera on that is the proper function. Now, all of these functions are really useful, but they're even more useful when you combine them. So we'll do one example of that, Andre. What we're gonna do is we're just going to write out, equals trim. And then, rather than selecting, sell a two. Now, we're gonna also combine that with lower. So we're gonna type in lower, then press tab. Then we've got equals. Trim, open brackets, a lower open brackets. Now we're going to select El Sur Les, too, and then gonna closed off, hit, enter and then fill that down. And now a love ourselves are in column B have been converted. Teoh. They've been trimmed. So we've got rid of those blank spaces and they're also all in lower case. So all of them are in a completely consistent format. There's no kind of discrepancy with the ways in which they look, so that's just something to be aware off. It's really useful when you combine these different functions to get your data. Looking much Martin Tidier on what we can do just to finish off is copy all of our new values that are in our tidy format. Andi, just paste them over here and we're gonna paste values. That right click, just like that one. And then we've got all of our product names formatted how we want them, and then we can just remove this column so right, click on delete. 3. How to Extract Text with LEFT: in this video, we're going to use the left function to you create a product code, a color code on a material code. So at the moment, all of the product names they're written out was words. So bar stool chair, footstool, etcetera. And then the colors. It's the foreword. Green, orange. Exactly. And then we've got plastic, metal or wood for the material. What we're gonna do is we've gone Teoh, find it kind of short away to write these out so that we don't have the full word or full phrase to describe them on. To do that, we're gonna use the left function. So I've created three new column headers here Product code, color, code and material code on. All we're gonna do is use one of the first letters or some of the first letters Teoh create this code. So for product code, what we'll do is that we just took out equals left. Now what this does is it basically returns? Uh, however many characters you specify from the left most part of a cell, so we've done equals left. Now we need to select the tax that we're interested in, so houses product code we're going to select, Sell a to on and when you want to get even, like one or two or three characters that is short away off referring to bar, stool or chair or whatever else. Now if we were to just take the first character, so b c f etcetera. The problem with that is that we've got to products here, footstool and frame, which both start with an F so one can act. It isn't gonna be enough to distinguish between those two pollock. So we're actually going to go with two characters here. So we're gonna do a comma on, then select two and then here enter, and then we get those two characters. So be a for bar stool. And then if we fill that down, you'll see that we've got the 1st 2 characters of each of these cells in column A on we can distinguish between the frame and the footstool because there's the two captures fo and fr . It wouldn't have worked if we just went with one character for color code. All of the colors have distinct names where the first letter is actually completely different. So in this particular case, we could do equals left on. Then we can select the cell. So be to and then if a number of characters, we're just going to specify one, because that's enough for us to tell. So we know that once we feel this down, it's G for green over orange after reds wife, yellow on beef blue on. That's it. That's all of the colors that we've got on then material code. So which can you seem again on Material code is nice and simple again. There's only actually three materials plastic metal on wood. So we just need to do select cell C two on, then one character hit Enter on, Which gonna fill this down on. Then what you can see is that we've now got this kind of shorthand version or all of this information on. That's an example of how you use the left function on in some of the next videos. We're going to start to make this code a little bit mawr detail and complex, but this is sort of, you know, the starting point for that 4. Combining Text with CONCAT: in this video, we're gonna look at the contact function on how you can use it. Teoh, combine text strings. So we're just gonna focus in on the product code part of our data. So over to the left, we've still got all of our other information, but we're just gonna be focusing in on these cells here. So in the last video, we took the 1st 2 letters off the product to get the product code the first letter of the color to get the color code on the first letter of the material to get the material code. Now, what would be useful is is if we combine this information into one cell to give us a product I d. That tells us everything about the product in one cell. So to do that, we're gonna use the contact function. So we do is type out equals Kong cat on. What you can do here is there's two options. First of all, you can select a range of cells, and you can do that by highlighting them like this. And then you'll get the coal on in between and then hit. Enter on. Did you see that? We get all of our letters A, B, A, G and P joined up his one texting. However, this doesn't look too easy to read on, and we can kind of do better than that. So what we're gonna do is not do it like that, But we're actually going to do it by selecting each individual cell and adding hyphens in between each of Kate's. So if we type r equals, conquer and then we're going to select sell e to And then now we're gonna do a comma and you use commerce. Teoh separate the different cells of the different text rings and then to get the hyphen, which we don't have in any of ourselves. At the moment, you open up a quotation mark on do hyphen. You could also put on other words here. Oh, you could even put spaces. But in this particular case, we're gonna do a hyphen, and then we're gonna do another quotation mark on then another comma to move on to text three and then we select, say, left to then quotation mark FIFA in quotation marks, comma. Then we select cell G two and then we hit enter and then we have our product. I d written out as the product code the color code on the material code with a hyphen in between. So we can easily see which parts of the code are which. Now the last thing we're gonna do just to make this look slightly Tidier is we're actually gonna do put on upper function into this formula as well. So if we just come up to this formula bar, all we do there is type out upper. It's happened. And then we had enough racket at the end and then hit. Enter on. Then we get our products. I d written out like that so we can see that this is the bar stool in green and it's made of plastic on. Then which gonna fill them. And then we have all of our information written out was a product code with all of the information in one cell for each product. And that's an example of how you can use the contact function to combine text rings in Excel 5. Combining Text: CONCAT or CONCATENATE?: so we've gone over the contact function. However, I just want to run over the con Katyn eight function and just quickly explain the differences between these two functions, which really similar now what the difference really years is that we've come Katyn eight. You can't select a range of cells on it doesn't work in exactly the same way as Con Cap. For that reason, on compassionate is a legacy function in Excel on it. It's not as useful as common cat, so you should really be focusing on using conquer. However, it's still worth being aware of Come Katyn, in case you ever come across it in a workbook that you may have to work with. So let's just quickly show that difference between Khan Cats and can Katyn. Eight. So we've contract. We could select, say, this range of cells here so you can see it's a range because we've got the colon between these two, and I just highlighted all three of these cells hit, Enter on. We get along off three of these cells joined up in one cell. If I was trying do that, we've Can Katyn eight, and you'll notice there's this little warning triangle there, which indicates it's a legacy function in Excel. It wouldn't work in the same way. So I'll do come Katyn eight, and I'm gonna highlight these three cells hit, enter. And rather than having them all joined up in one cell, all it's done is effectively copied them across into these three cells. And it's not had that desired effect of joining them all up. And that's because, come, captain, it doesn't support combining up ranges of cells you can still use Can Katyn eight by sort of clicking on individual cells or writing out your own text during. So let's just do one example that we can click on here. Then add a high fern, then click. I'm here. I'm not a hyphen and then click into here, and it will still work just fine. But as I say, you can't select a range of cells in the same way you can with contact function. So just work being aware off the differences there and why conquer is a better function to use and having your toolkit 6. Combining Text with TEXTJOIN: in this video, we're gonna look at the text join function, which is similar to Con Cat On Can Katyn eight. But there's a few differences, so if we just dive straight in on open up the textual function now, first of all, you have to specify a d limiter on what with the limiter is it's something that goes in between each cell. Now, you could leave this blank or you could just do quotation marks of nothing in between them , but for the purpose here which gonna put in a hyphen, as we've been doing on. Then move on to this next option here, which is ignore empty. Now we've ignore empty cells include empty cells. True or false. What this means is that if you have an empty cell in your text range in your cell range sorry, You will get a new extra de limiter in the place there if you select false, whereas you won't. If you select true and will run through both those examples and you can see the difference , we're going to select true for now and then we're going to select our text range. We're going to select these three here now, you could also click on individual cells, but we're going to just select all three of these for now. And then we're going to hear Enter Now they go. So we have got all of these codes joined up with the hyphen on the deep, which is the D limiter in between is different from Kong cap because you don't have to keep specifying, um the de Limited the heart, which will You have to have a hyphen here and that's same Ofcom. Katyn is where you don't have Teoh do that in the text joint function. So it's pretty useful on Let's just have a look at what happens when we delete a cell. So we're gonna delete this one here on, and because we've got it is true, it's ignoring the empty cell there, so we're not getting an extra de limiter. However, if we change this to false, it will include the empty cell and you end up with actually the limiter because it's giving us a D limit of four this empty cells. We've got two hyphens there. So that's the text joint function bit different to conquer. And can Katyn eight, um, can beam or simple to use can also be, you know, slightly more complex to use. They're both, you know, conquered and text joined both really useful functions. To be aware of the ons, you may find that you prefer using one over the other. It really just does depend on the situation. 7. Extracting Text with LEFT, RIGHT, and MID: So far, we've taken a look at the left function, but there's also two similar functions right and mid that were also really useful on. We're just going to have a quick look at those functions as well as left again in this video to. So let's say your product I D. Is the information that you had Justus an example. But you didn't have your product code color code on material code on Do you wanted those in the cells? So that's where the left white and mid functions are really useful. So we've got our product I d all listed in column A. So to get our product code, we're going to use a left function on. We're going to extract those 1st 2 characters so we're selecting Sell a two, and then we're going to specify to from num characters on then for color code. We know that that is in the middle. It's after the product code, but it's before the material code, and it's also between the two hyphens. So if we type out mid, then it's happy we select our text. And now for mid, you have to specify the start number on the numbers of characters. So the start number will be four. Because we've got the 1st 2 for the product code and then the third is the hyphen on, then the color code is that character number four on. We know that the color code is only one character, so we do a comma and then one for NUM characters. Close it off, Enter on, we get G. So that's the color there and then last leaf material code. We're going to use the right function on this works exactly the same as the left function, except it's the other way around. So which can select Sell a two on. We just want one character, so it's going to take the one character at the right of that text ring in cell A to hit. Enter on. We get the peace so you can see it's all broken out there. The A G on P, and then we can highlight all of those double click there on Phil down on. That's just a quick example of how you can use left right and made functions in Excel 8. Searching and Extracting Text with MID and SEARCH: So in this video, we're gonna be looking at the mid on search functions on also how you can combine these two functions to create a really powerful formula. So we've got a list of data here. It's actually called product details on what it is is. It's all of the information about each product, but it's all in one cell in the former, off the product name, the price, the color on the material on what you can see it is that it's kind of a bit difficult to see exactly what's what when all the information is kind of crowned into one cell like this . So the search in mid function could be really useful when you want to look at cells like this on extract certain information from them when you're looking for a particular point of interest. So let's say we want to extract the price from the cells, so we've got all of the prices and dollars broken out into a separate cell. Andi, let's start off by writing out a mid formula so equals mid on the now we're going to select our text, which is sell a to on. Then we have to select start number now. So we're looking to extract this $12 value in $16 value, etcetera. So we need to count number of characters that that is to get to this dollar sign so we can see it's 123456789 10. So it's the 11th character where the actual price starts. So we're gonna enter 11 on now. We need to select the number of characters that we want to extract. Now we've got the dollar sign, and then the double digit value on all of the values are double digits. So we're going to do three, and then we're gonna hit Answer Onda. We get $12 on because what it has done is looked in this cell. It's counted along 11 characters, and then it's extracted the three to get the price out. It's not pretty useful on if we fill this down, you'll notice that we get the same. We get the dollar value for all of these on the bar stool product. However, when we go down to the other products like chair frame table, we have a problem because it's extracting the letters because the start value off where the dollar prices is actually different. So this this function, it's not flexible enough on its own to to achieve what we want to hear. So that's where the search function comes in. Now we're going to start off by writing out a simple search function on what this does is it returns the actual location off a particular text item from self. So we're interested in the dollar sign in this scenario. So we're going Teoh, open up quotation marks, put a dollar sign and then within text. We're looking at Sal A two again. And then start number is simply just one because we want to find the dollar sign from the beginning off this sell, it doesn't matter where it is. We're just looking Teoh Count from the very beginning. So we're not gonna hit. Answer Onda. We get 11. So it's it's given us that 11 Valley, which is what we manually entered into our mid function here. So if we just fill these down, you'll see that the search function is actually finding even seven or 11 depending on which show it's looking at. So foot stores 11. The chair of seven because the word is shorter on. Yet we've only actually got those 27 and 11 for all of our examples now, this still isn't particularly useful because we are no actually getting our dollar value exactly for all of these. We've got the numbers here of where it starts and in here we've got this mixture of some of its working some of its No. So what we could do now is we could actually do a mid for formula. Andi, we're going to select sell A to write text now because we've used that search function in column C. We actually know exactly where the start number is So robber than manually counting 11 which isn't flexible enough, Teoh. Factoring all these different scenarios, we're just gonna select cell C two on. That means that the start number is actually going to change depending on where that dollar sign is. And then the number of characters the game we need to enter $3 sign, plus the two numerical values, the double digit, and then we're gonna close the off. Enter. Now, When we drag this down, double click and Phil down, you see that we get the price correctly. So here we're getting 2025 2015 etcetera on. We're not getting letters because the formula is changing, depending on what's in column C. So it's starting off with seven characters in or 11 on that's that's actually giving us what we need. So last of all, what we're gonna do is we're actually going to combine these two functions into one formula , so we don't have to rely on separate formulas in different columns to get this type of information. So if we just type out equals amid on, then we start off with Soleil to this is still the text that we're interested in. But now we're actually going to you open up a search function within this so search. Now we need to find the dollar sign again. So we're gonna enter the dollar sign of quotation marks around it. Then we're gonna do a coma on within text is backing sell a two again? So it will do that, and then we're gonna close this part of the formula off. Now we've got the option off starting, um, which is going to leave that blank? We're going to accommodate on the number of characters that we need is three. So we're gonna enter through either again. It's that same same number to get the dollar sign and double digit value. Then we're gonna close that off. Hit Answer Onda. We get our value of $12 which is correct. And if we just feel this down, we get a lot of the correct answers. So what this function is actually doing is it's searching for that dollar sign, and then it's extracting when it finds that dollar sign is extracting the three characters . So it's getting us the price on We've got all of that written out in one formula that works really well. So it's a great example of how you could combine the mid function with search function to create a really powerful formula in Excel. 9. Changing Text with SUBSTITUTE: So in this video, we're gonna look at the substitute function using a couple of examples. So we've got our product I ds listed in column A here on at the moment, the product I ds all have hyphens between each part of it. So the product and then ah, hyphen on, then the color and then another hyphen on, then the code for the material plastic, metal or wood. What if we wanted to change the hyphen to a forward slash for example on that's where the substitute can come in handy the substitute function. So let's just run through one example so equals substitute. Now, first of all, you have to select your text where the item is that you're going to be replacing. So which conflict sell a to then we have Teoh put in What are old Texas? And that is the hyphen. So open quotation mark, hyphen, close quotation mark on then the new text. Now, this is gonna be a ford slash in this example, gonna put forward slash in there on then instance number in this particular example, which gonna leave that blank. But we'll show you in next example how you can use that on then. So for the time being, what you gonna hit? Enter on, then what you'll see is all of the hyphens. Both of them have been swapped to forward slashes. And then we can just fill this down on all of our product. Ideas are changed. So we've now got Ford slashes instead off hyphens. So let's just have another go of this formula. Except this time, we're going to play around with the instance number on. What we're gonna do is we're gonna add in to hear now what? The instance Number of 1st 2 is which instance off the hyphen were actually substituting. So in this case is going to skip Teoh the second hyphen on Only substitute that one because we wanted to. Let's hit, Enter now and then. What you'll see is we get be a hyphen, g force slash p because we're only substituting that second hyphen and then we can just Phil down there on all of our product codes have changed. So that is thesis opportune function were just a basic example. As you can see, it's another useful text function. Teoh understand and know how to use in Excel 10. How to Convert data to Text: A Useful Tip: in this video, we're going to look at the text function on how you can use it to convert dates into text values. So we've got a short list of data. All it is is the six of July through to the 12th of July 2020. So let's just start off with the text day and we're gonna put a text function there and then we're going to specify Sell A two is our value on then format text. We just open quotation marks on There's three options we can do. We'll start off with the two D's here and then close that off hit, enter and then fill that down on. What you can see is by doing it in this text. Former is taken the 1st 2 digits effectively. It's just taken the day Onda. We now have it in just these two digits here. However, we could also change this. We could make it in sort of written format, like months you with their etcetera. If we change this to three D's hit, enter, then fill down. So each of these dates has been given their correct day in text. Former in three letter tax former on then we could also change this toe. Have it is the foreword by making this forties hit, enter and then just fill this down and we get Monday, Tuesday, Wednesday, president Friday, Saturday Sunday, which is the correct day that all of these actual dates relate to. So that's pretty cool now Text month on text year working the same way, except it's M barbed and D, and why rather than d a year. So if we just run through those quickly so text specify, sell a to format text will start off with the double them and then text year with images as well. Text while you yet answer. And if we just fill these down so it's taken the month part their text format on then the year also text former. And then, if we change this to three m's and so and feel that down, we get Joel J. UL for July or written out there, and then if we change this to three wise, what you'll notice is it. We actually get the full year that we get all four, so we don't actually need to do four for it. We do put four wives. They're still works, but it doesn't change. 2020 Still saying, Obviously this, however, we can get it in the full word format so we can just do it forth and and then fill that down. And then we have everything in text format, so that's pretty cool. It's another useful text function to be aware off in Excel. It's really useful. Want to having a talk it? 11. Thanks for watching!: Thanks for watching this class on tax functions in Excel. Let me know if you have any questions and you can find the class project instructions below this video to test what you've learned. If you want to learn more by XL, check out my other classes and follow me seal the first to hear about my newest classes. Thanks again and see you in the next class.