This is one very powerful tool to handle data management and analyze with many functions to treat text easily. Among the several functions, LEFT, RIGHT, and MID hold extreme value to extract some specific part of text from cells. Working with these functions will help save time and increase productivity, mainly for data cleaning and organizing purposes.
What is the LEFT Function in Excel?
That comes in very handy, as LEFT is a function; if your text string has meaningful left-hand starting words such as codes, prefixes or abbreviations then you can capture those starting words with LEFT.
Its syntax,
=LEFT(text, num_chars)
Text string or Cell reference
Number of Characters to return. For example if A1 contained “ABC12345 then the formula =LEFT(A1,3) will return “ABC”
This can be very helpful to pull out, say area codes from a phone number or first names from a full name.
Using the RIGHT Function in Excel
The RIGHT function essentially does what the LEFT function does, only it pulls characters from the right-hand side, or end, of a text string. This is pretty useful when the last few characters in a string might be important and include, for example, suffixes or unique identifiers.
=RIGHT(text, num_chars)
text: Is the text string, or cell reference.
num_chars: How many characters to extract.
Example
If cell A1 contains “ABC12345”, the formula =RIGHT(A1, 5) would return “12345”
This function is very useful when extracting serial numbers from file names or file extensions, respectively.
Knowing the MID Function in Excel
The MID function extracts characters from the middle of a text string from a given position. It works best with formatted text where the information one wants to extract will always be in the same block.
Syntax
=MID(text, start_num, num_chars)
text: Text string or cell reference.
start_num: Position of the character to extract.
num_chars: Number of characters to extract.
Example:
If cell A1 contains “ABC12345”, the formula =MID(A1, 4, 3) returns “123”.
It can really come in handy to extract the middle names from a full name or the dates from timestamp strings.
Examples of Using LEFT, RIGHT, and MID
Extract Names from Email Addresses
Let’s say you have email addresses like [email protected] in column A. You can extract the first name, last name, or domain like this:
First Name: =LEFT(A1, FIND(“.”, A1) – 1)
Last Name: =MID(A1, FIND(“.”, A1) + 1, FIND(“@”, A1) – FIND(“.”, A1) – 1)
Domain: =RIGHT(A1, LEN(A1) – FIND(“@”, A1))
Product Codes
For “PRD-12345-USA,” you can split the pieces with these formulas:
Product Prefix: =LEFT(A1, FIND(“-“, A1) – 1)
Product ID: =MID(A1, FIND(“-“, A1) + 1, FIND(“-“, A1, FIND(“-“, A1) + 1) – FIND(“-“, A1) – 1)
Region Code: =RIGHT(A1, LEN(A1) – FIND(“-“, A1, FIND(“-“, A1) + 1))
Data Formats
When working with phone numbers, IDs, and other data types of this sort, LEFT, RIGHT, and MID can be used to standardize formats. From a phone number “123-456-7890,” last four digits can be pulled using =RIGHT(A1, 4).
Using Multiple Functions to Manipulate Intricate Text
If you combine these with others such as FIND, LEN, and TEXTJOIN, the power of Excel increases exponentially. You could separate full names into first, middle, and last names using LEFT, MID, and RIGHT combined:
First Name: =LEFT(A1, FIND(\” \”, A1) – 1)
Middle Name: =MID(A1, FIND(” “, A1) + 1, FIND(” “, A1, FIND(” “, A1) + 1)) -FIND(” “, A1) – 1)
Last Name: =RIGHT(A1, LEN(A1) – FIND(” “, A1, FIND(” “, A1) + 1))
Conclusion
LEFT, RIGHT, and MID are of prime importance to the functions one needs to know if he or she involves text data. Since their power of extracting particular lengths of texts accurately has no comparison, these have turned out to be indispensable for text-cleaning purposes, text-formatted output, and further analysis. Use them effectively, master these, and your productivity in textual data handling improves manifold.
