Changing parts of data within an Excel cell, AKA String Manipulation, consists of a few simple functions. Once we understand the basics, we can look at how to combine them to do perform complex maneuvers.
When I use the term string, I am referring to a series of alphabetic, numeric, and other characters (e.g. comma, semi-colon, hashtag, at symbol, etc.) viewed as text.
LEFT
The LEFT function, as seen in the image above, consists of two inputs or arguments. The first is the text we are performing the function on, and the second is the number of characters to return.
The first argument, A2 points to the cell containing the word “unconventional”. The second argument, 2 means the resulting value will be “un”. If I change that to a 4, it will be “unco”. What do you think the value will be if I entered 1?
RIGHT
The RIGHT function is similar to the LEFT function, as it also consists of two inputs or arguments. The first is the text we are performing the function on, and the second is the number of characters to return.
The first argument, A2 points to the cell containing the word “unconventional”. The second argument, 3 means the resulting value will be “nal”. If I change that to a 7, it will be “ntional”.
LEN
LEN tells me the number of characters in a string, or length. So, while the prior two functions returned text, the LEN function returns a number. I will demonstrate the use of this function later in this post.
There is only one argument in LEN, which is the string we are measuring. The word “unconventional” contains 14 characters, so LEN will return 14. If I add a space after the last letter, the length will be 15.
MID
The MID function contains three arguments. The first is the string, while the second is the starting point in the string, and the last is the number of characters from the starting point. If I use the above arguments, B2 will contain “vent”.
If I change the second argument to 7, the result will be “enti”, so we see the third argument always counts characters from the second argument.
SUBSTITUTE
Substitute will replace one character for another. In the example above, all occurrences of the letter “n” will be replaced by an exclamation point “!”. The first argument is the text we are evaluating. The second argument contains the character we are looking for, and the third is the character we will replace it with. Note, that the second and third arguments could contain one or multiple characters. If “nc” were the second argument, and everything else remained the same, then it would return “u!onventional”.
Combining functions
LEFT and LEN
If I wanted to return all but the last letter of cell A2, I could use both LEFT and LEN combined. Excel will accept the LEN function because it returns an integer. I subtract one from the length to exclude the final letter. All of these functions can be combined together to help gain access to hard to reach string combinations. Try combining them and see what you get.
