Nas T has to convert sport names to a 4-letter abbreviation. If the sport name has one word, then use the left four characters. But, if the sport name has two words, use the left 3 letters from the first word followed by the first letter from the second word. AND... Nas says we have to use the IF formula. My solution includes some LEFT, MID, IFERROR, FIND functions. Also in this episode: Flash Fill.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2388.
IF based on word count.
Hey, welcome back to MrExcel netcast.
I am Bill Jelen.
Today’s question from Nas: He has a column of sports names.
If the sports name is a single word, we need the left 4 characters of the sport name. But if it's 2 words, such as ice hockey or auto racing, then the first 3 letters of the first word and the 1st letter of the second word.
Like ICEH or AUTR.
And then the thing that just makes me bristle.
You must use the IF function.
Why?
Why must we use the function?
Who is this?
Is this your manager is just a professor?
Is this homework?
What's going on?
Alright, so I'm going to ignore that IF requirement right now because I think that's a bad way to go using IF What I am going to do: I'm going to assume always that there are two words.
So, equal LEFT of the first word, comma three.
And then ampersand.
And the MID of A8.
Comma.
Where do I want to start?
I want to start.
I want to start right after the space.
So find a space in A8.
Plus one for a length of 1.
Like that.
And I'm going to get a #VALUE! error whenever there's a single word right?
Simple enough, but that's OK because I'm going to handle that #VALUE!
error by wrapping the whole thing in the IFERROR function So if there's not 2 words, then we know that we're going to be out here and we're just going to simply ask for the LEFT of A8 comma 4 like that.
Double click and copy that down.
In his example it was uppercase and maybe wrap the whole thing in UPPER.
I don't know if that's a requirement or not.
Alright, so beer pong B-E-E-P.
Works great.
Now, why is this requirement for using the IF function?
But if we really have to use the function, I guess we could do it.
We could say equal IF the LENgth of A8 is equal to the LENgth A8 minus any spaces.
That will tell you how many words there are.
Then we take the left of A8 comma four.
Otherwise the same formula I just used.
Essentially, the formula is a lot longer than I don't particularly like it because it's going to fail when someone does this.
If they type soccer space space space.
Then we're not going to get the fourth letter because it finds the space.
So this is a case where not using it actually is better than using IF.
And,.
I wonder why the professor or manager had this requirement of having to use the if function.
Maybe they were trying to prevent someone from just typing the whole thing or using Flash Fill.
Now that the great way to use Flash Fill.
I took field hockey something with two words and moved it up here to the top.
F-I-E-H.
And then Beer pong and then baseball.
So in my examples I give it enough examples to show it what the rules are.
And then back here.
Data, out on the far right side, Flash Fill, or just control E.
Press Ctrl+E and it does the right thing.
There you are.
Alright, so Nas, that's my way to do it.
Check out my book MrExcel 2020, Seeing Excel Clearly If you like these videos please, down below, Like, Subscribe and Ring that Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
We will see next time for another netcast from MrExcel.
IF based on word count.
Hey, welcome back to MrExcel netcast.
I am Bill Jelen.
Today’s question from Nas: He has a column of sports names.
If the sports name is a single word, we need the left 4 characters of the sport name. But if it's 2 words, such as ice hockey or auto racing, then the first 3 letters of the first word and the 1st letter of the second word.
Like ICEH or AUTR.
And then the thing that just makes me bristle.
You must use the IF function.
Why?
Why must we use the function?
Who is this?
Is this your manager is just a professor?
Is this homework?
What's going on?
Alright, so I'm going to ignore that IF requirement right now because I think that's a bad way to go using IF What I am going to do: I'm going to assume always that there are two words.
So, equal LEFT of the first word, comma three.
And then ampersand.
And the MID of A8.
Comma.
Where do I want to start?
I want to start.
I want to start right after the space.
So find a space in A8.
Plus one for a length of 1.
Like that.
And I'm going to get a #VALUE! error whenever there's a single word right?
Simple enough, but that's OK because I'm going to handle that #VALUE!
error by wrapping the whole thing in the IFERROR function So if there's not 2 words, then we know that we're going to be out here and we're just going to simply ask for the LEFT of A8 comma 4 like that.
Double click and copy that down.
In his example it was uppercase and maybe wrap the whole thing in UPPER.
I don't know if that's a requirement or not.
Alright, so beer pong B-E-E-P.
Works great.
Now, why is this requirement for using the IF function?
But if we really have to use the function, I guess we could do it.
We could say equal IF the LENgth of A8 is equal to the LENgth A8 minus any spaces.
That will tell you how many words there are.
Then we take the left of A8 comma four.
Otherwise the same formula I just used.
Essentially, the formula is a lot longer than I don't particularly like it because it's going to fail when someone does this.
If they type soccer space space space.
Then we're not going to get the fourth letter because it finds the space.
So this is a case where not using it actually is better than using IF.
And,.
I wonder why the professor or manager had this requirement of having to use the if function.
Maybe they were trying to prevent someone from just typing the whole thing or using Flash Fill.
Now that the great way to use Flash Fill.
I took field hockey something with two words and moved it up here to the top.
F-I-E-H.
And then Beer pong and then baseball.
So in my examples I give it enough examples to show it what the rules are.
And then back here.
Data, out on the far right side, Flash Fill, or just control E.
Press Ctrl+E and it does the right thing.
There you are.
Alright, so Nas, that's my way to do it.
Check out my book MrExcel 2020, Seeing Excel Clearly If you like these videos please, down below, Like, Subscribe and Ring that Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
We will see next time for another netcast from MrExcel.
