What formulas in Excel are used to retrieve certain letters or words in Excel? When working using Excel, sometimes we have difficulty when faced with data that requires taking a few letters, words, or even numbers in a table. Either take it from the right, take it from the left, and maybe take some words and numbers from an excel table. For example, you need to take a certain number or letter. another example wants to separate the first name, middle name, and last name.
To retrieve a specific word or number of letters from a text in an excel table, several special formulas are available. And what we need is the LEFT function, the RIGHT function, and the MID function. Excel’s LEFT, MID, and RIGHT formulas are the most commonly used text functions for manipulating text data. The three formulas are categorized as text functions in Microsoft Excel. Here’s an explanation, how to use it, and examples of text data that we want to manipulate.
|
Excel Formula
|
Description
|
|
LEFT
|
Excel formula to get letters/words/numbers from the left direction of text
|
|
RIGHT
|
Excel formula to get letters/words/numbers from the right direction of text
|
|
MID
|
Excel formula to get letters/words/numbers in the middle of a text
|
On this occasion I will discuss how to use all three functions to get some text or some specific characters from a cell containing text.
IMPORTANT: The use of commas ( , ) or semicolon ( ; ) in FORMULA is adjusted to the version of Microsoft Excel and the language configuration on your computer. So don’t panic when there’s a warning pop-up, you just have to switch between a comma or a semicolon..
Excel LEFT Formulas
The LEFT formula in excel usually uses to retrieve the first number of characters or some text characters from the left, based on the number of characters we specify. In other words, the LEFT function/formula is a function used to retrieve the left part of the text data as much as a certain character from left to right.
How to Use Excel’s LEFT Function
The syntax or writing of left formulas in excel is as follows:
Description:
- LEFT: Excel formula name.
- Text: Is either text typed directly on an excel table, cell reference, or data in the form of text or numbers resulting from an excel formula that we want to take some of its characters from the left position.
- num_chars: The number of characters we want to take from the left.
Excel LEFT Formula Example
Note the following when using left Excel formulas, see below:
- IMPORTANT: This formula also counts for spaces in a sentence, so you have to count them as well when it comes to retrieving letters or numbers in the table!
When using the Left function to retrieve a certain number of text characters make sure num_chars argument is greater than zero (should not be negative). Otherwise, it will generate an error #VALUE!. Note examples number 1, 2, and 3. - If the num_chars type is clear, it is assumed to be 1 (one). See example number 4.
- If num_chars is greater than the length of the text, then the LEFT function will retrieve all the text. See example number 5.
Excel RIGHT Formulas
The RIGHT function in excel is used to retrieve the last number of characters or several characters from the right of a cell, according to the num_chars characters specified. The RIGHT function takes some characters in the form of numbers or letters from right to left or can also mean excel formulas used to take the right part of the data as much as we need, so just adjust it to your needs.
How to Use Excel’s RIGHT Function
Here’s how to write right function in excel formula:
Description :
- RIGHT: Excel function name.
- text: This is a data number of text that some characters will take from the right.
- num_chars: the number of characters to be taken from right to left.
Excel RIGHT Formula Example
Some of the following you need to note when using right formula in microsoft excel:
- IMPORTANT: This formula also counts for spaces in a sentence, so you have to count them as well when it comes to retrieving letters or numbers in the table!
- The num_chars in the Right function should be greater or equal to zero.
- If it is zero it will generate blank text (Example 3). If it is negative (less than zero) it will generate an error message #VALUE! (Example 2)
- The num_chars can also not be filled(cleared), if so then the Right function will only take one character from the right (Example 4).
- If the num_chars is greater than the length of the text, the RIGHT function will retrieve all text (Example 5).
Excel MID Formulas
Excel’s MID function will retrieve a certain number of characters from a text, starting from the specified position, as well as with the specified number of characters as well. In other words, the MID function can be used to retrieve some words in the text from left to right such as left function, but the beginning of retrieval of the character or word can be determined by ourselves.
How to Use Excel MID Function
How to use excel MID formulas, for example below:
- MID: Excel function name.
- text: The text we will take some of the words or characters
- start_num: The position of the first character we want to take. If you want to retrieve from the first character a text then start_num we fill it with the number 1 value.
- num_chars: Many text characters we will take some of the words or letters, starting with the start_num.
Excel MID Formula Example
When using the MID formula in excel, there are a few things you need to pay attention to:
- IMPORTANT: This formula also calculates the spaces in a sentence, so you have to calculate it too when you have to take letters or numbers from the table!
- If start_num is greater than the text length, MID returns the text table to empty. example No. 3
- If start_num is smaller than text length, but start_num plus num_chars exceeds text length, MID returns the characters up to the end of the text. See Example no. 4.
- If start_num is less than 1, MID returns the #VALUE! Error value. for example, you can see on number 5.
- If num_chars is negative, MID returns the #VALUE! Error value. See Example number 6.
How’s it going, buddy? it turns out that it makes it easy to use the LEFT, RIGHT, and MID formulas in Microsoft Excel. Hopefully, it can be useful, so many Excel Formula Tutorials LEFT MID and RIGHT… Thank you for visiting….
