Obviously this post is about the most popular text related functions in Excel. Meanwhile, it is also a message from me…
Yes, you are RIGH! I LEFT my ex-company and have landed on a new job which is much more busier… and I am in the MIDdle of probation. Although I am trying hard to update this blog every weekend, I am not sure if that can be achieved. Don’t be surprised if you don’t see new post every week as it used to be. On the positive side, I’ve got more chances working with data and Excel in my new job. That would absolutely give me more ideas on writing. 🙂
Let’s get back to the functions now.
Attention! LEFT! RIGHT! LEFT! RIGHT!… and MID!
The functions themselves may give you a rough idea of what they do. Right?
They are commonly used to extract part of a text string; be it on the RIGHT, in the MIDdle, or on the LEFT of a text. What additional information required is the number of characters you need; and starting point (for MID).
The syntax of these functions is simple:
LEFT(text,[num_chars])
It tells Excel to look into the text (1st argument), and return n characters (2nd argument) on the LEFT (the function). The [] means the argument is optional. When omitted, Excel assumes 1.
RIGHT(text,[num_chars])
It tells Excel to look into the text (1st argument), and return n characters (2nd argument) on the RIGHT (the function). The [] means the argument is optional. When omitted, Excel assumes 1.
The syntax, of LEFT and RIGHT is more or less the same, provided that you can distinguish LEFT from RIGHT. 🙂
MID(text,start_num,num_chars)
It tells Excel to look into the text (1st argument), starting from the nth character (2nd argument),and return n characters (3rd argument) on the RIGHT (the function) from the starting point. Note that the 3rd argument num_chars is no longer optional.
MID is like an enhanced LEFT. MID allows you to specify the starting point. If you put 1 as the second argument start_num, it does what LEFT does basically.
=MID("Apple",1,3)
=LEFT("Apple",3)
Both of the above formula returns the same result “App”. So we do not need MID if the starting point is 1. Simply LEFT. RIGHT?
Again, a picture tells thousand words. Let’s take a look at below example.
Note: num_chars required any number equal or larger than 0. negative num_chars returns #VALUE!.
For the highlighted text shown in the screenshot above, think about this: If you have only 10 dollars in your wallet, the maximum amount you can pull out from your wallet is 10 dollars regardless of the amount “requested”. Right? 🙂
Tips: You may use MID and input a big number as the final argument num_chars if you simply want to extract all the characters on the right of the starting the starting position. If you are not certain about how big the number should be, LEN(text) would always be sufficient.
Here’s another example for illustration:
When text follows a specific pattern (and meaning), LEFT, MID and RIGHT are very handy to extract useful information for you.
Final note: LEFT, MID, RIGHT always return result as text, although the result may look like a number.
Read also:
More text-related functions to come. Stay tuned!
wmfexcel 