Excel LEN, LEFT, RIGHT and MID functions
Many times, you need to use a combination of functions or formulas to implement a particular feature in Excel. In case of text manipulations, you might not be able to implement functionality just with a single Excel text function. Suppose an Excel file, say EmployeeSalary.xls, is created to save salary details of all employees of a particular company. Of course, EmployeeSalary.xls, should save the names of Employees. Suppose, the manager wants to save the first name and last name of all employees in two separate columns, then how it can be achieved? Either a data entry personal can manually do it. But, if the file contains details of thousands of employees, then it would be a time consuming process. Here, Excel text functions come into play. The above functionality can be achieved combining different Excel text functions.
Excel LEN function is used to return the number of characters in a particular string and the syntax is LEN(string). So, if you enter LEN(“Hello”), then the result would be 5. The text function LEFT returns the specified number of characters from start of a given string and the function RIGHT returns the specified number of characters from the end of a given string. The syntax of LEFT function is LEFT(string, num_chars) and syntax of RIGHT function is RIGHT(string, num_chars). Here, string contains the characters you wish to extract and num_chars specifies the number of characters you wish to extract. If you omit the parameter num_chars, then its default value would be 1. If you write a function LEFT(“Hello Good Morning”,7), then the result would be “Hello G”. If your function is LEFT(“Hai”), then the result would be “H” as you have omitted second parameter. If you write a function RIGHT(“Hai Welcome”, 7), then the result would be “Welcome” and the result of the function RIGHT(“Hai Welcome”) would be “e”.
Excel function MID is used to return the characters from the middle of a string when the starting position and length is specified. The syntax of MID is MID(string, start_position, len). Here, string is the text from which you wish to extract the characters, start_position is the position of first character to be extracted and len specifies the number of characters to be extracted. If your function is MID(“Hello Good Morning”, 7, 4), then the result would be “Good”.
Now, let’s see how to implement the requirement specified earlier for EmployeeSalary.xls, that is how to separate first name and last name of all employees. To implement this, let’s have the data as follows.
Now, if we find the position of space (“ “), we could try to extract two substrings using the position value and the total length. So, to find the position of empty space, we could use the FIND function as FIND(“ “, A1). This would return the value 6. Now, you can use LEFT and RIGHT functions together to separate first name and last name. First to make the process easier, let’s have separate columns for position of space, total length of the string and then first name and last name.
Click on cell B1 and type the formula =FIND(“ “, A1). Instead of typing directly, you can also go to FormulasTextFind. Now copy this formula into cells B2 to B6, so that it will automatically update the cell reference. Now click on cell C1 and type the formula =LEN(A1). Copy this formula into cells C2 to C6. Now to extract the first name, we will use LEFT function. We could easily get the length of the string to be extracted by subtracting one from the position of space. To make it simple, if the string is “Hai Welcome”, the position of space would be 4 and we will get the string “Hai” if we extract 3 (4-1) characters from left. Now, click on cell D1 and write the formula =LEFT(A1, (B1-1)) and copy it to the required cells.
We can use the same logic to extract last name as well. If you are using RIGHT function, then to get the length, you just need to subtract position value of space from the length of the whole string. In the case of “Hai Welcome”, the length of the string to be extracted would be 7 (11-4). You can use the MID function as well instead of RIGHT function. But, the logic becomes confusing. Click on cell E1 and type the formula =RIGHT(A1,(C1-B1)). Copy this formula into cells E2 to E7. Now your results would be shown as follows.
If you don’t want to have the column values of B and C, you can include the formulas to find the position of space and length of the string in the LEFT and RIGHT functions. Then your LEFT function (function in cell D1) would be =LEFT(A1, (FIND(” “,A1)-1)) and your RIGHT function would be =RIGHT(A1,(LEN(A1)-FIND(” “,A1))) and your results would be as given below.
Read More about Excel here