How to Use LEFT Until Specific Character in Excel

This guide will discuss how to use LEFT until specific character in Excel

The rules for using the LEFT function in Excel are the following:

  • When num_chars is left blank or empty, the function will default to the value of 1, which means that the function will return 1 character. 
  • When the value inputted for num_chars is greater than the total length of the selected text string, the function will return all of the text. 
  • The LEFT function will always return a text string.  

Excel is a powerful tool containing several functions and tools we can use for tasks such as calculating, organizing, and manipulating data. Specifically, there are different functions in Excel for manipulating text data. 

Furthermore, these functions used for manipulating text are called the Text function. And one of the functions we will be using is the LEFT function. So the LEFT function extracts or returns a specific number of characters starting from the left side of the selected text string. 

Additionally, the LEFT function is an excellent tool that is easy to manipulate for different uses. In this case, we will specifically use the LEFT function to return all characters starting from the left side of a specific text string until a specific character is encountered. 

Let’s take a sample scenario wherein we need to use LEFT until a specific character in Excel. 

Suppose you are given a list of company usernames. And you are tasked to extract the names which are found on the left side of an underscore character. So you need to use the LEFT function to extract all the characters until the underscore. 

Since the usernames have a different number of characters before the underscore, we need to utilize the function and create a formula that will extract all the characters before the underscore of the text string. 

Great! Before we move on to discuss a real example, let’s first learn how to use the LEFT function in Excel.

 

The Anatomy of the LEFT Function

The syntax or the way we write the LEFT function is as follows:

=LEFT(text, [num_chars])

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we start any function in Excel.
  • LEFT() is our LEFT function. And this function will return or extract the specific number of characters we input starting from the left side of the selected text string. 
  • text is a required argument. And this refers to the text string we want to extract the characters from. Usually, this is a cell reference containing the text string.
  • num_chars is an optional argument. So this refers to the specific number of characters we want the function to extract. By default, the num_chars is 1. 

Amazing! Now let’s dive into a real example of using LEFT until specific character in Excel. 

 

 

A Real Example of Using LEFT Until Specific Character in Excel

Let’s say we have a data set containing a list of usernames. And we need to extract the names from the username. So our initial data set would look like this:

Initial data set

 

So each username contains the name of the employee underscore the department they belong to. For example, the format of the username is name_department. In this case, we want to get the employee’s name from the username. But, the number of characters for each name is different. 

Rather than specifying the number of characters to extract for each username, we can manipulate the formula to extract all characters from the left side of the text string until the underscore is encountered. 

So our formula would extract all characters starting from the left string and stop when the underscore is encountered. In this case, the issue of having different number of characters for each username is solved. 

For instance, we have a username of Kate_Marketing. And the function will extract starting from the left side and stop when a specific character is encountered, which is the underscore. So the return value would just be Kate.

Additionally, the function will return a #VALUE! error if no underscore is found in any of the usernames. But, we can also specify what value the function will return. 

So we can utilize the IFERROR function to return a “No Match” if an underscore is not found in any of the usernames. 

Finally, our final output would look like this:

Final data set

 

You can make your own copy of the spreadsheet above using the link attached below. 

Now we can finally start learning the steps of how to use LEFT until specific character in Excel. 

 

 

How to Use LEFT Until Specific Character in Excel

In this section, we will discuss the step-by-step process of how to use LEFT until specific character in Excel. Additionally, each step has detailed instructions and pictures to guide you through the process. 

1. Firstly, we need to create a new column to input the results from the LEFT function. Then, we can start inputting the LEFT function formula to extract the characters until the underscore. So we will type in the formula “=LEFT(B3,FIND(“_”,B3) -1)”.

Lastly, we will press the Enter key to return the result.

Use LEFT Until Specific Character in Excel

 

2. Secondly, we will drag down the Fill Handle tool to copy and apply the formula to the other cells.

Drag the fill handle

 

3. And tada! We have successfully used LEFT until specific character in Excel.

Final result

 

4. Next, let’s try using the IFERROR function with the LEFT function to return a value if no underscore is found. In this case, we want the function to return “No Match” when no underscore is found. To do this, we will input the formula “=IFERROR(LEFT(B3,FIND(“_”,B3) -1), “No Match”)”.

Then, we will press the Enter key to return the result.

Use LEFT Until Specific Character in Excel

 

5. After, we can drag the Fill Handle tool down to apply the formula to the rest of the cells.

Use LEFT Until Specific Character in Excel

 

6. And tada! We have successfully used the LEFT function with the IFERROR function.

Use LEFT Until Specific Character in Excel

 

And that’s pretty much it! We have explained how to use LEFT until specific character in Excel. Now you can use this method to extract from a list of text strings containing a different number of characters. 

Are you interested in learning more about what Excel can do? You can now use the LEFT function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like