Microsoft Excel – Top 10 String Functions

There are many string functions available in Microsoft Excel. In this article, I will talk about my top ten favorite and most common string functions. I will follow each string function with an example where the left side of the equal sign is the result and the right side is a call to the function.

At some point or another I have used most of these string functions in a Microsoft Excel worksheet. It would be a good idea to know what string functions are available to you before you start performing any operations on text strings in Microsoft Excel.

These top ten string functions are in no particular order.

1. Concatenate – This string function allows you to join multiple strings into one string. This function allows you to Concatenate up to 30 strings.

Concatenate string function example:

“This is a test” = CONCATENATE(“this”,”is”,”a”,”test”)

2. Substitute – you can substitute one piece of string with another piece of string in a larger string using this string function.

Substitute string function example:

“Monday the 13^th” = SUBSTITUTE(“Friday the 13^th“, “Friday”, “Monday”)

3. Replace – This string function lets you replace a certain part of a large string with your own string.

Replace string function example:

“!esting” = REPLACE(“testing”, 1, 1, “!”)

4. Search – With this string function, you can find out at what character in the overall string a certain substring appears.

Search string function example:

3 = SEARCH(“e”, “Great!”)

5. Vallue – You can convert a text that represents a number into an actual number that you can use in a calculation using this string function.

Value string function example:

3000 = VALUE(“$3,000”)

6. Len – This string function tells you how many characters are in a given string.

Len string function example:

4 = LEN(“blue”)

7. Lower – You can convert all upper case letters into lower case letters using this string function.

Lower string function example:

“lower case” = LOWER(“LoWeR CaSE”)

8. Proper – With the PROPER string function, you can convert a string into a proper sentence so that it will capitalize the first letter of the first word of the sentence.

Proper string function example:

“Hello, world.” = PROPER(“hello, world.”)

9. Day – With this string function, you can find out the day part of the string that represents a date.

Day string function example:

15 = DAY(“15-Apr-2008”)

10. Rept – You can use this string function to repeat a certain text a certain number of times.

Rept string function example:

“jjj” = REPT(“j”, 3)