Functions for Text Manipulation in Excel: LEFT, RIGHT, MID, LEN, and FIND
The LEFT, RIGHT, MID, LEN, and FIND functions in Excel are powerful tools for manipulating and analysing text strings by extracting specific parts or finding characters within them.
The LEFT function, with the syntax LEFT(text, [num_chars]), extracts a specified number of characters from the start (left side) of a text string. For example, returns "Geeks" (first 5 characters).
On the other hand, the RIGHT function, with the syntax RIGHT(text, [num_chars]), extracts a specified number of characters from the end (right side) of a text string. For instance, returns "eeks" (last 4 characters).
The MID function, with the syntax MID(text, start_num, num_chars), extracts a specified number of characters from the middle of a text string, starting at any position you choose. An example would be which returns "for" (3 characters starting at position 5).
The LEN function, with the syntax LEN(text), returns the length of a text string, counting all characters including spaces. For example, returns 13 (total characters).
The FIND function, with the syntax FIND(find_text, within_text, [start_num]), locates the position of a substring within a text string, starting from an optional position. It is case-sensitive. For example, returns 5 (starting position of "for").
How to use with examples:
| Function | Example Formula | Description | Result | |----------|------------------------------|---------------------------------------------|-------------| | LEFT | | Extract first 5 characters from the left | "Excel" | | RIGHT | | Extract last 7 characters from the right | "Tutorial" | | MID | | Extract 4 characters from the 6th character| "Tuto" | | LEN | | Get the length including space | 14 | | FIND | | Find position of substring "Tuto" | 6 |
Practical use case:
To extract a first name from a full name in cell A2, assuming the first and last names are separated by a space, you can use:
To extract the last name (all text after the space):
For more complex names with titles or multiple spaces, combining MID and FIND can extract first and last names even if titles like "Dr." or "Mr." appear, as shown by:
This formula extracts the first name ignoring titles.
These functions are fundamental for text manipulation, enabling substring extraction, text length checking, and locating characters within text strings.
Technology in data-and-cloud computing, such as trie data structures, can significantly improve the efficiency of text manipulation operations, including those performed using Excel functions like LEFT, RIGHT, MID, LEN, and FIND. Trie data structures, for instance, can process large text strings quickly and accurately by breaking down words into prefixes, which makes finding specific substrings faster.
By employing trie technology, it's possible to extract first and last names with greater precision, even for complex names, and perform various text analysis tasks on massive datasets more effectively. This integration of trie data structures into text manipulation technology further enhances data analysis capabilities in the field of data-and-cloud computing.
