Hi, this is Wayne again with a topic “Combining Excel’s LEFT and Find Functions”.
In this article, we’re going to look at how we can combine two powerful functions, the left function and the find function in microsoft excel and how we can use them together to extract complicated data from a cell in a spreadsheet. So here we have a list of synth pop bands and albums, but when this data was imported into excel notice, that the band names and the album names have both been put in the same column and i’d really like to separate them. I’D like to have depeche mode, erasure, etc in one column and the album names in another column, now there’s a couple of different ways: you could do this, let’s look at how to set up a left and find formula to pull that information out here in b2. I’M just going to type equals left and take a look at this tip from microsoft. Excel the left function returns the specified number of characters from the start of a text string, so i have equals left i’ll put in the left parenthesis now.
It needs some text i’ll just select a2 and i’m just going to type it in a2. Of course, i could have clicked to select it and then i’ll put a comma now i need the number of characters for the band name. So if you look at this in this first example, black audio – we have one two three, four five characters. Now space does count as a character. So 6, 7. 8.
9. 10. 11.. I want the first 11 characters, so i type in 11. I should put in my right parenthesis: tap, enter and look.
It pulled out the first 11 characters and put them in cell b2. Now, if that worked so well using the left function, then why do i even need the find function? Well, take a look at what happens when i copy this formula down the spreadsheet. If i double click on the autofill handle in this case, it will extend the formula down the sheet and it works great right.
Well, it does until it gets to a band that doesn’t have exactly 11 characters in its name, so you can see it didn’t work well for these other bands, except for the killers, so am i going to have to type in the formula over and over for Each one of these different bands – fortunately no – i don’t have to do that instead i’ll click back on b2. So i’m going to go up here and erase the right parenthesis and the number of characters instead of 11, i’m going to type in the find function. So we’re using two functions in one formula after typing, find i’ll put in a left parenthesis, and now, let’s look at the tip that we’re getting from microsoft excel the first thing it’s looking for. The first argument is find text. What text are we looking for? The second argument is within what text and then there’s an optional third argument. So here in our formula, what text are we looking for? Well, it’s really this dash or hyphen or minus sign. That’S what we’re looking for in this case and because it’s part of a cell, that’s a text cell. This isn’t numbers it’s text i need to put it within quotes in my formula, so i’ll tap, minus and put it in quotation marks, comma. Now, within what text am i searching? Well, it’s again a2. So i could type that in or click to select a2 and then i don’t need this optional third argument, so i’m going to put in my right parenthesis – and i should put in another parenthesis to finish off my formula – tap, enter and look what it did.
It worked, but it included the dash or minus at the end of the band name. So let’s look at what happened. If we take a closer look at the formula, we used the left function to count from the left in a2. How far did we count? We didn’t put in a number instead of putting in a number we use the find function to just find wherever the dash was and then to count the number of characters, including spaces that it took to get from the left to that space, and so that portion Of the formula produced the number 12 in this case, and so the first 12 characters appear in the cell in b2.
Now that’s almost exactly what we want, but not quite so, i’m going to select b2 and then go up to this formula and after the find part of the formula, i’m just going to click and put in -1 tap enter, and so now the formula does just What it did before, it’s counting from the left in cell a2. How far does it count? Well, it finds the dash in a2, but then it subtracts one. So it goes one to the left, which removes the dash out of the results. So that worked great now, if i double click the autofill handle notice what it does so look how beautifully that worked! It’S now pulling the exact number of characters necessary to display the band name, no more, no less, there’s no dash, it’s just the band names.
So that’s how you can combine the left function and the find function to extract the exact number of characters that you need out of a cell or column or row. Now, of course, i could play with this formula a little more. I could subtract three characters or whatever i want to do.
I could even add some characters to the end, so feel free to adjust that formula as needed so that you can get the data that you need out of a range in excel in a future video. I might show you how to use the write function with find to do a similar thing. I bet you can figure it out, though, but if there’s a lot of interest, look for a future video on that topic and thanks for watching this video, i hope you found it to be helpful.
If you did please like follow and subscribe, and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, consider clicking the thanks button below the video or you could support me through my patreon account, or by buying channel merch and you’ll, find more information about those options. In the description below the video you .