What’s the LEFT System in Excel?
The LEFT System in Excel means that you can extract a substring from a string ranging from the leftmost portion of it (meaning from the beginning). It’s an inbuilt Excel perform particularly outlined for string manipulations.
Let’s suppose you have got typed the phrase “Rose Watts” in a cell, and you have an interest in extracting simply the identify “Rose” in one other cell. With the LEFT components, you possibly can obtain this process simply through the use of the components “=LEFT(A2, 4)“. On this components, “A2” signifies the cell containing “Rose Watts” whereas “4” specifies that you really want the primary 4 characters.
End result:
Desk of Contents
- What’s the LEFT System in Excel?
- Find out how to Use LEFT System in Excel?
- Can You Use the Left System With Dates?
- Left System Errors
- Superior Purposes of LEFT System in Excel
Now, let’s delve right into a extra detailed understanding of the LEFT perform, beginning with its syntax and arguments:
Syntax
=LEFT(textual content, num_chars)
Arguments
The LEFT components’s syntax includes two main arguments:
- textual content: This required argument represents the supply cell or vary from which you wish to extract characters.
- num_chars: This argument specifies the variety of characters you wish to extract from the start of the textual content string. If omitted, the components extracts just one character.
Find out how to Use LEFT System in Excel?
We have now two strategies to make use of the LEFT components in Excel.
.free_excel_div {
background: #dff4f9;
font-size: 16px;
border-radius: 7px;
place: relative;
margin: 30px;
padding: 25px 25px 25px 45px;
}
.free_excel_div:earlier than {
content material: “”;
background: url(https://cdn.educba.com/pictures/excel_icon.png) heart heart no-repeat #207245;
width: 70px;
peak: 70px;
place: absolute;
prime: 50%;
margin-top: -35px;
left: -35px;
border: 5px strong #fff;
border-radius: 50%;
}
A) Direct Cell System Method
- Begin typing =LEFT( within the cell the place you wish to show the end result
- Now add the arguments, knowledge and shut the bracket like this: =LEFT(A1,2)
- Press Enter, and the end result will show in the identical cell.
B) Utilizing Excel Ribbon
- Choose the cell the place the end result will seem (e.g., B2)
- Go to the Formulation tab
- Discover Textual content within the classes
- Click on the LEFT perform
- A dialog field will open. Enter the cell (e.g., A2) in Textual content and the variety of characters you wish to extract (e.g.,4) in Num_chars
- Click on OK to insert the LEFT perform
- You possibly can see the end result within the backside left of the dialog field.
Instance #1: Extracting a Single Character
Let’s discover the way to make the most of the LEFT components in Excel to extract the preliminary letter “D” of the phrase “Disneyland” current in Cell A1.
- Choose the cell the place you wish to show the end result (e.g., B2).
- Enter the components =LEFT(A1) in Cell B2.
- Click on Enter.
End result:
We get the end result as D.
Instance #2: Extracting Greater than One Character
Suppose you have got knowledge in cell A1 containing the textual content “My identify is Elsa”, and it is advisable to extract the primary 7 characters from this textual content. Let’s see the way to do it utilizing the LEFT components in Excel:
- Choose the vacation spot cell, i.e., B1
- Enter the components =LEFT(A1,7)
- Press Enter.
End result:
We get the end result as “My identify.”
Instance #3: Extracting Numbers
Allow us to see how we will use the LEFt components to extract the leftmost numbers from a cell.
- Choose Cell B1
- Enter =LEFT(A1,2) because the components
- Press Enter to show the end result.
End result:
Instance #4: Extracting Values for a Vary of Cells
Suppose you have got a variety of cells from A1 to A4, and also you wish to extract the primary 3 characters from every cell. Let’s see how to try this utilizing the LEFT perform in Excel.
- Choose all of the end result cells the place you need the extracted knowledge, resembling B1 to B4
- With these cells chosen, enter the components with a variety like this: =LEFT(A1:A4,3)
- Click on Ctrl+Shift+Enter for the end result.
End result:
Instance #5: Utilizing Cell Reference for Num_chars Argument
Let’s say you have got a cell that incorporates the variety of characters you wish to extract. Allow us to see the way to use a cell reference within the Num_chars argument.
Given:
Suppose Cell B1 has the worth 4. Now, you should use the cell B1 as a cell reference within the LEFT components to extract the primary 4 characters from cell A1. You should utilize the under components:
=LEFT(A1,B1)
End result:
Can You Use the Left System With Dates?
It isn’t potential to make use of the LEFT components with dates as a result of dates have a particular format. Excel shops date values as numbers on the backend. Thus, utilizing the LEFT components on a date in Excel gives you a sequence of numbers representing the date moderately than part of the date itself.
Subsequently, once you use LEFT perform on a date:
- You may lose essential date particulars just like the day or 12 months.
- Totally different date codecs can provide you totally different outcomes.
- It could possibly result in errors if dates aren’t within the anticipated format.
Resolution: Change the format of the cell
To work with dates accurately, you possibly can both use capabilities designed for dates, or you possibly can convert the format of the cell from Date to Textual content.
Consequently, it is possible for you to to extract the date from the cell.
Left System Errors
Utilizing the LEFT components with sure varieties of characters (non-printable, non-text, and so on.) or making every other errors can result in errors. Listed below are a couple of errors that may happen whereas utilizing the LEFT perform.
1. LEFT Operate does Not Print Non-Textual content Knowledge
Because the LEFT perform normally works with textual content knowledge, it may not work as anticipated if you happen to apply it to dates, or different non-text knowledge (pictures, audio, binaries, forex, and so on). Thus, be certain that the info you might be working with is in textual content or common.
Within the picture under, when attempting to extract the primary three characters (“$20”), the perform doesn’t account for the greenback image. As a substitute, it reveals the next three characters as “200.”
2. #VALUE Error Because of Adverse Character Depend
If the num_chars argument just isn’t a constructive quantity, i.e., if it’s zero or damaging, the LEFT perform will return the #VALUE! Error.
3. Further Areas or Non-Printable Characters can have an effect on the end result
If there are additional areas within the textual content, it may possibly have an effect on the outcomes of the LEFT perform.
As an example, within the under picture, the textual content in cell A1 has a single house at first of the sentence. So, once we attempt to extract 5 characters, we get the end result as “ Lead” moderately than “Leadi”.
Superior Purposes of LEFT System in Excel
Aside from utilizing the LEFT perform by itself, we will mix the LEFT perform with different capabilities for extra superior purposes.
1. LEFT with LEN Operate
Let’s say you have got the textual content string in cell A1 and also you wish to take away the final 3 characters, we will use the mixture of LEFT and LEN capabilities:
=LEFT(A1, LEN(A1) – 3)
2. LEFT with FIND Operate
Let’s say, you wish to extract textual content from cell A1 as much as “New”. You should utilize the under components:
=LEFT(A1, FIND(“New”, A1) – 1)
End result:
This components finds the place of the “New” utilizing FIND, then extracts the specified textual content utilizing LEFT.
Notice: The FIND perform is case-sensitive when looking for textual content inside a cell. Thus, ensure to put in writing the phrase within the correct case.
For instance, if you happen to put “New” in lowercase, then it is going to present a #VALUE! Error.
3. LEFT with SEARCH Operate
The LEFT with SEARCH components works the identical manner as LEFT with FIND. The one distinction is SEARCH is not case-sensitive.
As an example, once you use SEARCH, it is going to discover the specified textual content no matter whether or not it’s written as “new,” “NEW,” or “New,” focusing solely on the time period “New.”
4. LEFT with VALUE Operate
Allow us to take an instance of a cell that has alphanumeric values. Suppose we wish to show the primary three characters in a quantity format. If we merely use the LEFT components, we’ll get the lead to textual content format not quantity format.
Thus, we will use the LEFT perform with the VALUE perform and convert the characters into numerical format.
Notice: Within the above picture, the content material in cell A1 is in textual content format whereas the numbers in cell B1 are in quantity format.
Continuously Requested Questions (FAQs)
Q1. What are the opposite softwares by which we will use the LEFT perform?
Reply: Aside from Excel, you should use the LEFT perform in varied software program and programming languages, together with:
- Tableau
- Energy BI
- DAX (Knowledge Evaluation Expressions)
- SQL (Structured Question Language)
- JavaScript
These instruments and languages present the LEFT perform to control and extract characters from textual content strings equally throughout totally different platforms.
Q2. Does the LEFT perform work equally to Excel in Google Sheets?
Reply: Sure, the LEFT perform works equally in Google Sheets because it does in Excel. It extracts characters from the beginning of a textual content string, and its utilization is constant between the 2 applications.
Q3. Can you utilize the LEFT perform with Excel VBA?
Sure, we will use the LEFT components with VBA, for which you should use the Microsoft Visible Fundamental Editor.
You possibly can both give sheet reference for the supply cell or enter the phrase straight, as proven under,
=LEFT(“Ball”,1)
Beneficial Articles
This text is a information to the LEFT components in Excel. Right here we talk about the way to use the LEFT perform in Excel utilizing a number of examples. We have now additionally offered a downloadable Excel template. You can too undergo our different recommended articles,
The publish LEFT System in Excel appeared first on EDUCBA.
