Text to Column is a great tool to break apart the contents of cells. However there are easier ways to do it without performing tasks like that. You can use Excel Functions to do the trick. To do this, we will use the following Functions: LEFT,MID,RIGHT, and SEARCH.
Download sample file here
So let’s start off by looking at what we want to split apart:
In rows 3 to 5, we have some cities with States listed, all seperated by a colon. If you want to just get the city name, you could use the LEFT function, however we don’t know how many characters from the left side to pull. The length of the cities will vary.
We can use the SEARCH function to look for the colon though. It will bring back the character that the colon is on. So for cell A3, if we did
=SEARCH(“:”,A3) it would bring back an 8. What this does is look for the colon, which you need in parenthesis. It looks in cell A3 which is Seattle:WA. If you count the characters, the colon is in the 8th spot.
So we can now nest that formula in our LEFT function to pull the left most characters. Since we don’t want to include the colon in our results, we have to subtract 1 from it.
So we are pulling the left 7 characters in cell A3, which is Seattle. Then if I copy it down to the other cities, we get:
So even though Boston is 6 characters, Excel knows to search for the colon and subtract 1 and it will return the left 6 characters in that instance.
This also works for middle initials. Say we want to pull out the middle initial for a full name. The name is Harry S Truman.
Since we are pulling an item from the middle of a cell, we would want the MID function.
First we want to find out what character the middle initial starts on. So we would use the SEARCH function again but this time we are searching for a space.
Excel searches for the space in cell A8 and returns a 6. It pulls the first space it finds, which would be the space after the first name. So this would work with any name.
So the middle iniital would be character 7 in this case. So we just have to add a 1 to the SEARCH.
So now, all we have to do is put in our MID function and insert the SEARCH function into it.
We use =MID(A8,SEARCH(” “,A8)+1,1)
To explain this, MID looks at cell A8. Then we need the starting character. So we inserted our SEARCH function above which pulls a 6. However that is where the space is, so that is why you see a +1 at the end of it.
Then the final part is a 1. That tells how many characters to pull. Sinec we only want the middle initial, we use 1.
So there you go. A new way to pull items from a cell that are not all the same length. Have a great day!