Excel LEFT Function: Nothing Going Right, Go Left!
The Excel LEFT Function is another Text manipulation function that is great for grabbing every character to the left of the given starting point within another string of text. It is another one of the most used functions that is great to know, when you need to locate specific text within a long string of characters.
Excel LEFT Function:
Syntax of the Excel LEFT Function:
- LEFT(text, [num_chars])
- text (Required) - The text string that contains the characters you want to grab.
- num_chars (Optional) - Specifies the number of characters you want the Excel LEFT Function to extract.
- The num_chars argument must be greater than or equal to zero. If num_chars is greater than the length of text, LEFT returns all of text. If num_chars is omitted, it is assumed to be 1.
- The entire text string is counted, including space characters. So the string
"Hello There" would have 11 characters, including the space between the words.
- =LEFT("Hello There", 5) = Hello
- =LEFT("Hello There", 8) = Hello Th
- =LEFT("Hello There") = H
- =LEFT(A1, 3) = The First 3 characters of the text in A1
The last example, in the above spreadsheet image, requires a little bit of an explanation:
If you know that the number of digits in a street number is either 3 or 4, you can use the Excel TRIM Function, in combination with the Excel LEFT Function, to remove any SPACE Characters from the result.
In this case, the number is only 3 digits, so it captures the 4th character, which is a SPACE character. The Excel TRIM Function removes this additional character, so you are left with just the number itself.
If the number of digits was 4, then there would be no SPACE Characters and the Excel TRIM Function wouldn't need to remove anything.
Download The Example Spreadsheet Here:
Why use the Excel LEFT Function?
The Excel LEFT Function, in addition with the other Text Manipulation functions, can really make a huge difference when you need to format data that needs a lot of massaging to be presentable. If the Cell data has more information than you need and all of that is on the left side, then you can use this function to extract it.
Although this function is great on its own, it really shines when used in conjunction with other Text functions and even more so within Excel's VBA programming language. If you have reports that you need to pull data from, this function will get you going on the LEFT track!
Share, Comment, Ask a Question or Rip Me a New One!
If you liked this article, please feel free to share it on social media and comment below if you have any suggestions, questions or corrections. Thank you for reading!
P.S. - Remember to sign up to the newsletter email list, so you can catch all of the new posts and learn about upcoming topics, products and/or services.