ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to use the Left and Right Function in Excel

Updated on March 24, 2014
Learning to Extract Data From Within a Cell
Learning to Extract Data From Within a Cell | Source

I thought I knew a lot about Microsoft Excel until I left my first job out of college. I learned more in one year at my new job than I had learned in the previous seven years. I did not even have to take any advanced Excel training. My boss taught me many different tips and tricks in Excel including how to use macros, vlookup, auto filtering, keyboard shortcuts, and much more. I am so thankful that my boss took the time to teach me the ins and outs of Excel, which is why I love to share my Excel knowledge to try to help others. I want to share with you how to use the Left and Right functions in Excel as well as the Mid function.

Have you ever used the Left, Right, or Mid Functions in Excel?

See results

Left, Right, & Mid Functions in Microsoft Excel

The left, right, and mid functions are useful to pull text or numeric values out of a specified cell. Knowing how to use these functions can save you a lot of time. They are great tools if you are trying to automate a spreadsheet for month and year changes. They are also great for separating first and last names for mail merge purposes. Once you are comfortable using these functions, you fill find many different ways to use them.

Left Function Example
Left Function Example | Source

Left Function

The Left function is great for pulling text or numeric values starting on the left side of the cell that it is pointed to. In the formula, you have to tell Excel how many spaces to pull into the current cell.

=Left([cell where you want to pull the data from],[number of characters to return])

In the picture above, the “Basic Example” has a brief list of month end dates where the name of the month is abbreviated. Lets say for some reason that you need to pull the month name out of that cell. Using the Left function, the basic formula would look like this:


Left Function Example #2
Left Function Example #2 | Source

This formula will grab the first three characters in cell A7. It works great as long as all of the month abbreviations are thee characters long. Lets say that we have a list of month end dates but this time the full month is listed. We cannot use the same formula as we did with the basic example because each month has a different number of letters in it. We have to use the Find function, which looks for a specific character and returns the position of the character. For the example, we will search for the space (“ “) after the name of the month. The Find function should be included in the second half of the Left function. The formula would look like this:

=Left(A15,Find(“ “,A15))

Left Function Example #3
Left Function Example #3 | Source

I want to look at one more common example that is relevant if you are working with a list of names. Lets say that we have a list of names, last name first separated by a comma. This formula is almost identical, but we need to put in one additional item.


The minus one tells Excel to find the comma and back up one space. If you did not use it, the results would be “Doe,” or “Brown,” and would not be right.

Right Function Example
Right Function Example | Source

Right Function

The Right function formula is similar to the Left function formula, except that it starts on the right side and moves left. If there are a set number of characters from the right side of a cell that you want to pull, the formula would look like this:


Right Function Example #2
Right Function Example #2 | Source

This will pull in the four-digit year. If you only want the last two digits of the year, change the formula the 4 to a 2. If you are trying to pull something out of the right side of a cell that is not a set number of characters, you will have to get fancy and use the Find function with the Len function. The Len function counts all of the characters in a cell. Under the Left function, we pulled out the last name from a list of names. Now let me show you how to pull out the first name. The formula would look like this:


Helpful Resource for Learning Excel

Mid Function Example
Mid Function Example | Source

Mid Function

Out off all three functions, Mid can be the most useful because you can use it to grab things on the left, right, and middle of a cell. However it is easier to use the Left and Right functions when they are applicable. The Mid function does just what it says, it will grab characters from the middle of a cell based upon the parameters that are included in a formula. The formula starts out by defining what cell that you are looking to pull data out of, and then you will need to define the starting point where Excel should begin to return the data. This needs to be the first character that you want to return. The final part that needs to be defined is the length of the string to be returned. For instance, if the string is three digits long, the number would be 3.

=Mid(([cell where you want to pull the data from],[starting point],[length of string to return])

Let us return one more time to the basic example of pulling the parts for a date apart where all of the number of characters for each cell is constant. The formula would look like:


Mid Function Example #2
Mid Function Example #2 | Source

The formula will look at cell A7, and then it will go to the fifth character, and then return the fifth and sixth characters. Now let us look at a more typical use by looking at the example of having a full month plus the data and the year that we used when we looked at the Left function. This time we want to grab the day of the month from the middle of the cell. Here is what the formula will need to look like:

=Mid(A15,Find(“ “,A15)+1,2)

The “+1” is included in the formula to tell Excel to start one character to the right of what the Find function returned. The Find function does a major limitation. If there are two of the same characters in a cell, it will only find the first one. For instance, cell A15 has two spaces included in the cell. If you were to want to pull something after the second space, I would advise you to find the comma instead and put a “+2” after the Find statement.

Vlookup Example
Vlookup Example | Source

Using the Left, Right, and Mid Functions in Other Formulas

Left, Right, and Mid can be used in vlookup functions or any other kind of formula, to tell Excel what you are trying to find in a corresponding table. I choose to use Vlookup because it is one of the more advanced functions in Excel that most people will use. For this example, we are starting with a table consisting of state abbreviations and also the full name of the states. We also have an address for a company that includes a state abbreviation in the middle of a cell. Here is what the formula would look like:


Learning to use these Excel spreadsheet functions will help to make your life. Feel free to ask me any questions that you may have.


This website uses cookies

As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

Show Details
HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
LoginThis is necessary to sign in to the HubPages Service.
Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
AkismetThis is used to detect comment spam. (Privacy Policy)
HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
MavenThis supports the Maven widget and search functionality. (Privacy Policy)
Google AdSenseThis is an ad network. (Privacy Policy)
Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
Index ExchangeThis is an ad network. (Privacy Policy)
SovrnThis is an ad network. (Privacy Policy)
Facebook AdsThis is an ad network. (Privacy Policy)
Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
AppNexusThis is an ad network. (Privacy Policy)
OpenxThis is an ad network. (Privacy Policy)
Rubicon ProjectThis is an ad network. (Privacy Policy)
TripleLiftThis is an ad network. (Privacy Policy)
Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
ClickscoThis is a data management platform studying reader behavior (Privacy Policy)