|
Most of the times, we use Excel to crunch numbers, but what a lot of people don’t know is that Excel is a very effective tool to crunch text as well.
We can use Excel to clean text fields, extract specific information from text fields, or join text fields together.
Let’s have a look at the example below where have some text fields extracted from a database (columns A to E) and we would like to do some manipulations over them in the green columns F to J.
You can download the sample workbook from here. 
In the first column, we have the State-Zip codes. We know that the state is the first 2 letters from the left, and the Zip code is the 5 numbers from the right. Let’s separate those to have the state in column F and the Zip code in column G.
To do this, we use the functions LEFT and RIGHT. In cell F2, we type =LEFT and open a bracket. Then we will find 2 arguments. The first one is the text, which in this case is the State-Zip cell, and then the number of characters, which is 2 since we need only the first 2 characters. We then drag the formula along the column. 
For the Zip code, we go to cell G2, and type =RIGHT then open a bracket, and again the same 2 arguments are the text and number of characters. In this case the number of characters will be 5 since we need the 5 characters from the right. We then drag the formula along.
Next, we see that in columns B and C we have first names and last names respectively. Now we need to join these 2 names together to form the Full Name in column H. There are 2 ways to do this.
The first one is using the CONCATENATE function. We go to cell H2, type CONCATENATE, open a bracket, and type as many texts as you need to join, separated by a comma. 
When we concatenate cells B2 and C2, we find that the result in H2 actually joins the 2 names but without any spaces in between. We can include the space as an argument in the concatenate function by typing space between speech marks (i.e. “ “). The final formula and result will look like this:
The second way to join text strings is using the Ampersand sign "&" (which you can type using Shift + 7 on your keyboard).
To do this, we don’t have to use any functions. We just type the = sign, choose the text and then join it using the & sign with another text and so on. The formula will look like this: 
Note that we joined the space “ “ with a & sign as well.
Now let’s extract the product model from the product number in column D. We know that the model is the 2 letters in the middle of the product number (i.e. in 475AR9096, the model is AR). To do this, we use the MID function. We go to column I, type MID and open a bracket. We see that the MID function has 3 arguments:
The final output will look like this: 
Finally, we would like to clean the Web Site column because it includes a lot of spaces either before or after the text strings (it’s obvious in some fields, but not very obvious in other fields where the space comes at the end of the text. You can examine this further in the worksheet yourself).
To clean this, we use the TRIM function which basically removes any additional spaces before or after any text. To use it, we just type TRIM and choose the text we need to clean. It’s very straight forward. 
As a bonus step, we can precede the result with a “www.” and finish it with a “.com” for a better display of the website using CONCATENATE function or the & sign.
Now we have seen how can you use Excel to extract, join, separate or clean text strings. Now go ahead and try it on your own data. Feel free to post your questions in the comments field below.
Happy Excelling! |