Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
In this lesson, I'm going to show you how you can split up data using text functions.
And if we jump across to the Formulas tab and take a look in the text group in our functions library,
this is where are you going to find all of the commands that are going to be using?
And you can see we have so many different text functions in here.
Now, I will say that for certain pieces of text, using text functions is a pretty easy endeavor,
but sometimes it can get extremely complicated and I'm going to show you how complex it can get at the
end of this lesson.
And then in the next lesson, I'm going to show you the way that I split up my data all the time because
it is so much easier.
But it's good to have this basic knowledge of text functions because you will find yourself using them
from time to time, particularly when combined with other functions.
So let's take a look at some of them and how they work.
Now, in this first example, I have just a small list of part numbers, and maybe I want to split these
up.
So I have the first three characters in the code column the middle, three characters in the identifier
column and the last three characters in the part column.
Now I could use text two columns to do this, but we're not going to do that.
We're going to use text functions.
So the first thing I want to extract from this string is the code, and the code is the first three
characters of this string.
So the function I'm going to use here is equals left because we're going to work from the left hand
side of the cell.
We have two arguments for this function.
The first argument is text.
So that is the cell that contains the text we want to extract.
And this is a simple case of entering in how many characters we want to extract from the left.
So because all of these are consistent, I want to extract three characters close the bracket, enter
and I can simply double click to copy that down.
Because all of these part numbers are very consistent in their format.
They all have three characters three characters, three characters.
Now, if I want to extract the middle three characters for this, I would use another text function
called Mitt, and we have an additional argument here.
So our first argument is text.
So that is the cell.
We then want the start number.
So I need to count to cross until I get to the first character that I want to extract.
So one two three four, I want to start from the fifth character and then I want to extract the next
three characters.
Close the bracket control answer and I can double click to copy that down, and I've extracted the middle
part of this string.
And then the final one.
I want to extract the last three characters.
For this, we use the write function.
Once again, we select the text string and then the number of characters from the right that we want
to extract.
So three, in this case control enter and then double click to copy down.
Now, extracting text in this way is all well and good.
And if you have very consistent numbers or strings, then this works really well.
It's super simple to put together, but more often than not you have data that isn't consistent.
So let me show you an example.
And again, if we take a look at this data down here, maybe this is something that we've imported.
We basically have four pieces of information combined into one cell and separated by a comma.
So if I want to extract the employee name from each of the cells, I'm not going to be able to do it
using the left function.
Because if this works for the first one, so let's say left, I want to extract from this cell number
of characters.
Well, for this first one, if I want to extract the entire name, that is one two three four five six
seven eight nine nine characters.
We need to count the space as a character so I could type in nine.
Close the bracket answer and once that works for the first one.
When I copy this down, it doesn't work for the others because these are not all consistent in their
length.
And this is where using text functions becomes a lot more complex.
So let me show you how I could extract the name from this cell.
We need to do something a little bit different.
So what I'm going to do here is we are going to use left, but we're going to go about this in a different
way.
Now we're going to specify the cell A12.
But when it comes to the number of characters, we need to think about this in a different way.
I can't just count across to extract the number of characters.
So what I'm going to do is look at this.
Data and think to myself, is there any common identifier that I could use here?
Well, I know that after every single name, we have a comma.
So maybe I could find the comma in this string and then extract everything before it.
And that would give me the name.
So the first thing I need to do here is I need to find that first delimiter, that first comma.
So I'm going to use the find command, another text function.
Now we find we have a few different arguments.
It's asking us which text we want to find.
Well, I want to find a comma now because this is a comma.
It's essentially text.
It needs to go in quote marks.
So I'm going to say, look for this comma.
If you had something else, so maybe you've got a space in there, you could type in a space and put
that between quote marks.
It all depends how your fields are separated.
I'm looking for a comma.
Where am I looking for that comma?
Well, I'm looking for it in cell A-12.
So let's close that off, and let's also close off our left function.
Now, if I was to hit, enter just here, I'm going to get that.
And if I copy it down.
Notice that it has extracted the name, but it's also giving me a comma on the end.
So what I can do to get rid of that comma double click to edit the formula is just on the end here between
these two brackets.
I'm going to say minus one to effectively go back to character, and that's going to get rid of that
comma.
So that formula just there, if you look at it in the formula bar, it isn't too bad.
We start with left.
We then specify the cell and we then just say, find the comma in cell.
A-12 extract a minus one.
So we're not including the comma in that extract.
So that formula isn't too bad.
Where things start to get really complicated is if I then want to extract the job title because that's
in the middle of this data.
Now the formula that you need to put together in order to extract the job title using text functions
is extremely long, very complicated and very much outside the scope of this intermediate course.
But just so you can see how long this formula is.
I've got it over here.
So if I click on this and we take a look at that in the formula bar, would you take a look at that?
We have so many different things going on there.
It's a very long and complicated formula.
It does work.
You can see it has extracted that job title.
But for me, trying to remember this and taking the time to type all of this into a cell is never the
method that I use if I want to extract the job title.
And in the next lesson, I'm going to show you by far the easiest way to split up your data.
So I'm going to jump across there now, and I look forward to you joining me.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.