Sounds simple, huh? In fact it is but it gets really interesting ,when you combine it with some other functions like we will do in our example below. By the way, LEFT has a twin, called RIGHT which does nearly with the one difference that it is starting from the other direction. It is only his stepbrother (or stepsister if you want to) because in a world where you write and read from left to right the function LEFT will start "from the beginning". The LEFT function has the following syntax:
=LEFT(text,num_chars)
This means that the first element we have to enter is the text from which we will extract our information. The number of characters (or chars) determines is the count that we will use starting with 1 as the first letter. Let's imagine we habe a list of international product IDs where the first 2 digits always describes the country, the product is manufactured in and the last 4 digits describe the article itself:
| A | |
| 1 | Value |
| 2 | DE2589 |
| 3 | US9204 |
| 4 | UK6325 |
| 5 | FR6321 |
We now want to extract both elements. LEFT and RIGHT are perfect functions for this purpose. For the first part (the country element) we use LEFT. The formula would be
=LEFT(A2,2)
That tells MSXL: Take the value in A2 and display the first two characrters. With the article ID we have to start from the right, so our formula looks like
=RIGHT(A2,4)
That tells MSXL: Take the value in A2 and display the last (because we read from left to right) 4 characters. The completed table looks like this:
| A | B | C | |
| 1 | Value | Country | Product |
| 2 | DE2589 | DE | 2589 |
| 3 | US9204 | US | 9204 |
| 4 | UK6325 | UK | 6325 |
| 5 | FR6321 | FR | 6321 |
| Formulas in the table | ||||||||||||||||||
|
Display Excel tables online >> Excel Jeanie HTML 4
Now let's go further with another a little bit more advanced example. Assuming we have a table with four names. By the way, this is the so called Queen table (labeled this way by me) because it shows the names of the members of the greatest rockband that ever existed on this planet: QUEEN 
| A | |
| 1 | Full Name |
| 2 | Freddy Mercury |
| 3 | Brian May |
| 4 | John Deacon |
| 5 | Roger Taylor |
We now want to separate first name and surname out of the given values in cells A2 to A5. We will utilize - you guessed it - the LEFT function. But this function alone would not help us because as you can see from the table, the first names habe different lengths. But there is one thing the values in the cells have in common and that is their separator. Each first name and surname is sepaeated by a SPACE. We can search for this separator and this will help us determine the length. Therefore we will also use the SEARCH function, which has the following syntax
=SEARCH(find_text,within_text)
This function returns the position number of the text we have been looking for. It als has a third element - start_num - which is not covered in this example. So we let Excel search for the separator - the SPACE - in our text. The formula we will use for displaying the first name is
=LEFT(A2,SEARCH(" ",A2)-1)
In detail this means
=LEFT(A2,
Start extracting the text from the left position in cell A2
SEARCH(" ",A2)-1)
This is the second part in the left function and it is another function. The information in it says: search for the SPACE - " " - in cell A2 and return its position number. Also subtract 1 from this number because we only want to return the name and not the name + SPACE.
Now let's go to the surname. Unfortunately we have to follow another approach because similar to the LEFT function, the second element in the formula - syntax: =RIGHT(text,num_chars) - is the same. This means - as with the LEFT function - we cannot define a "starting point". Don't worry, we have a workaround for this because we will utilize another function ,called LEN (stands for length).
This function will return the total number of characters in a given value. So what we have to do here to find out the length of surname is subtract the position of the separator, again the SPACE, from the total length. Sounds complicated? For th name "Freddy Mercury" the total length is 14 characters and the position number of the SPACE is 7 so subtracting the latter from the first will lead is to the total number of charcters for the surname: 7.
The formula for displaying the surname is:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))
In detail this tells MSXL to do the following
=RIGHT(A2,
Start extracting the text from the right position in cell A2.
LEN(A2)
Detemrine the total length of the information in cell A2
-SEARCH(" ",A2))
Determine the position of the SPACE in cell A2 and subtract it from the first element, length (LEN) resulting in the overall order to extract the 7 right characters. The overall table looks the following.
| A | B | C | |
| 1 | Full Name | First Name | Surname |
| 2 | Freddy Mercury | Freddy | Mercury |
| 3 | Brian May | Brian | May |
| 4 | John Deacon | John | Deacon |
| 5 | Roger Taylor | Roger | Taylor |
| Formeln der Tabelle | ||||||||||||||||||
|
Display Excel tables online >> Excel Jeanie HTML 4
This concludes our short overview of the LEFT and RIGHT function in MSXL. Any ideas or suggestions? What would you use this nice little function for? Tell us in the comments or share this post via social bookmarking.
Discuss this blog post in the forums (click the blue button)
