MS Excel: LEFT() & RIGHT() Functions
Microsoft’s Excel has 2 functions that operate very much the same called LEFT() and RIGHT(), these functions can be used to find a substring within a string.
Understanding the way Excel uses LEFT() & RIGHT()
These formula’s required 2 different inputs; A HAYSTACK and a CHARACTER TALLY.
The formula’s are as follows:
LEFT(HAYSTACK, CHARACTER TALLY)
LEFT() returns the string that follows the left most character. (The beginning of the string)
RIGHT(HAYSTACK, CHARACTER TALLY)
RIGHT() returns the string that preceeds the right most character. (The end of the string)
Using LEFT() & RIGHT()
If I had the string “Ian Blott, Web Developer, Sydney Australia” in cell A1 and I wanted to use the substring that is the first 20 characters i would use LEFT()
LEFT(A1,20)
This would return the value “Ian Blott, Web Devel”
On the other hand, if O wanted to use the last 20 characters of the string O would use RIGHT()
RIGHT(A1,20)
This would return the value “er, Sydney Australia”
Getting Fancy
A few days ago I posted about the SEARCH() function. I can use this in my LEFT() or RIGHT() formula’s and vice versa.
I can use a LEFT() or a RIGHT() inside a SEARCH() to limit the HAYSTACK to either end of the original string.
SEARCH(",",LEFT(A25,10),1)
This wills cause the SEARCH() function to only look for the first comma in the first 10 characters of the string in A1, thus changing the HAYSTACK for the SEARCH() to “Ian Blott,”
SEARCH(",",RIGHT(A25,10),1)
RIGHT() does the same but for the last 10 characters with the effective HAYSTACK being ” Australia”
Side note:
As with all my posts, quite often they are going somewhere and require 2,3 or even 4 different functions to perform the final task. Bellow are the related posts that tell an overall story
