🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Text Functions

Quiz me Quiz Compact player layout Large player layout
Focus video player for keyboard shortcuts
Auto
  • HD
  • 720p
  • 540p
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

We hope you enjoyed this lesson.

Get the Excel for Accounting course for more great video tutorials.

Start free trial

Cool lesson, huh? Share it with your friends

Facebook Twitter LinkedIn WhatsApp Email

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

In this lesson, we learn how to use LEN, FIND, SEARCH, LEFT, RIGHT and MID.

Exercise files

Download this lesson’s exercise file.

Text functions
16.7 KB

Quick reference

Text Functions

Learn the basic uses of LEN, FIND, SEARCH, LEFT, RIGHT and MID

When to use

These functions are useful to find or extract parts of text from a larger text (or string).

Instructions

  • LEN            -   counts the number of characters in a string
  • FIND           -   will find a certain character in a string and is case sensitive
  • SEARCH    -   finds a certain character in a string and is NOT case sensitive
  • LEFT           -   extracts a certain amount of characters starting at the left of a string
  • RIGHT        -   extracts a certain amount of characters starting at the right of a string
  • MID             -   extracts a certain amount of characters from a designated character in a string
Login to download
  • 00:04 In this example, we're going to look at text functions.
  • 00:08 We've looked at trim and ampersand before.
  • 00:10 Quick reminder of what each one of those does.
  • 00:13 Trim removes or trims all the extra spaces in a string.
  • 00:18 So if we apply it to A3, it will leave a single space between words.
  • 00:22 It will also remove the spaces at the beginning and at the end.
  • 00:27 Ampersand is the same as concatenate.
  • 00:30 If I want to combine the text in column A with the text in column B,
  • 00:34 I can use an ampersand, but I need a space in there.
  • 00:38 So let's add an ampersand space and B5, and that looks better.
  • 00:46 I can also use concatenate to combine A5, a space and B5 for the same effect.
  • 00:55 And you can also just use concat, it's the same thing.
  • 01:01 So the two types of text functions I want to look at in this video are ones that
  • 01:06 track characters.
  • 01:07 So it can tell you a position of characters in a string, or
  • 01:11 can count the characters in a string.
  • 01:13 And the others are, if you give it a position and a number of characters,
  • 01:18 it extracts and returns a number of characters.
  • 01:22 So, what does LEN do?
  • 01:25 LEN of a string gives you the number of characters in it.
  • 01:29 LEN of that up above is 10.
  • 01:33 It's a little misleading now because I only see nine letters in the string, but
  • 01:37 there's a space after the I.
  • 01:39 If I go up and remove the space and hit enter, the LEN returns a value of nine.
  • 01:46 The find and search functions do the same thing with one difference.
  • 01:50 Find is case sensitive, but search is not.
  • 01:54 So, if we want to find the lowercase c in the string above, we type find,
  • 01:58 then the lowercase c in quotes, and click on the cell with the string.
  • 02:04 And that will return the position of the c, which is three, third position.
  • 02:10 Search does exactly the same thing.
  • 02:12 Let's type in the search function and you'll see it also returns a three.
  • 02:18 But just to highlight the difference here if I do a find function with an uppercase
  • 02:24 C, and there's only a lowercase c in that string find will return a value error.
  • 02:31 Search will work though, search will give you the position of the first c it
  • 02:36 encounters, whether it's lowercase or uppercase.
  • 02:40 Now here's something else we can do with strings.
  • 02:43 We can extract characters,
  • 02:45 meaning we can take a piece of a larger string and use it somewhere else.
  • 02:50 To extract characters from a string, I can use left, right, and mid.
  • 02:55 Left gets the characters from the left side of a string.
  • 03:00 Let's say I want the left three characters.
  • 03:04 Right takes the right characters.
  • 03:08 We'll do the three right characters here.
  • 03:11 For left and right, you just reference the string, and
  • 03:14 then tell Excel how many characters you want from either the left or right side.
  • 03:19 Mid takes characters from the middle of a string.
  • 03:22 But for mid, you need to tell Excel where in the middle
  • 03:25 of the string to start extracting characters.
  • 03:29 For example, if you want to start at the fifth character, type in a five,
  • 03:34 and then type in how many characters you want,
  • 03:37 we'll say three characters again, close parentheses.
  • 03:41 And in this example, we're grabbing from the string at E9.
  • 03:45 We'll start at position five, the characters in positions five,
  • 03:51 six, and seven, that will be E, F, and G.
  • 03:54 And those are text functions.
  • 03:56 They become really powerful if start to combine them to get things done.
  • 04:01 Let's say for example, I want to grab three characters from that string above.
  • 04:05 And I want the E and whatever the next two characters are after it.
  • 04:10 But what if I don't know where the E starts, we can find it.
  • 04:14 So let's use the mid function mid, click on the string, and
  • 04:18 then we use the find function, find the E inside the string.
  • 04:23 That'll return a five since it's the fifth character, and
  • 04:26 then give us the three characters starting at that position in the string.
  • 04:31 That will also give us E, F, G.
  • 04:33 So, we just combined a couple of functions there and that's pretty cool.
  • 04:38 Those are a few basic text functions.
  • 04:40 We'll do some more with text in the next lesson.

Lesson notes are only available for subscribers.

Grouping Data: Remove Groupings, Fix Borders and Copy Visible Data
06m:16s
Text Functions: Combine
03m:58s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement