Manipulating Strings in Excel

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

2017-07-23_222809

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

2017-07-23_222720

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

2017-07-23_222605

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

2017-07-23_222509

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

2017-07-23_222401

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

2017-07-23_221824

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.

 

6 thoughts on “Manipulating Strings in Excel

  1. Alex

    You actually make it appear so easy together with your presentation however I find this matter to be actually something which I believe I’d never understand. It seems too complicated and extremely vast for me. I am looking forward in your subsequent post, I will try to get the dangle of it!

    Like

    1. peterglobus

      Hi, Alex. Thanks for reaching out. Can you let me know which function you find most challenging? I have considered writing more on this subject, but I’m unsure where people need the most guidance. I would greatly appreciate your input. Thanks!

      Like

  2. Alex

    Wow, superb weblog layout! How long have you ever been running a blog for? you make running a blog glance easy. The full glance of your site is great, let alone the content!

    Like

  3. Alex

    You actually make it seem really easy along with your presentation however I to find this matter to be actually something which I think I might by no means understand. It sort of feels too complicated and extremely broad for me. I’m looking forward on your next put up, I will try to get the grasp of it!

    Like

  4. Shelly Michael

    Thanks, I’ve just been looking for information about this subject for a long time and yours is the best I’ve found out till now. However, what concerning the bottom line? Are you certain concerning the supply?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s