RIGHT. I LEFT. In the MID of…

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.Excel Tips - LEFT MID RIGHT 1.png

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:Excel Tips - LEFT MID RIGHT 2.png

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:

Number stored as text

Fixing trouble dates

More text-related functions to come.  Stay tuned!

This entry was posted in Formula and tagged , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s