Excel Text and String Functions:

 

Excel Functions for Finding and Replacing Text, with Examples: LEFT, RIGHT, MID, LEN, FIND, SEARCH, REPLACE, SUBSTITUTE

Related Links:

1. Excel Text and String Functions: TRIM & CLEAN.

2. Excel CODE & CHAR functions.

————————————————————————————————————————-

This section deals with the Excel Worksheet String Functions – click here for detailed explanation of Excel VBA String Functions.

LEFT Function (Worksheet / VBA)

The Excel LEFT function can be used both as a worksheet function and a VBA function. The LEFT function returns the specified number of characters in a text string, starting from the first or left-most character. Use this function to extract a sub-string from the left part of a text string. Syntax: LEFT(text_string, char_numbers). It is necessary to mention the text_string argument which is the text string from which you want to extract the specified number of characters. The char_numbers argument is optional (when using as a worksheet function), which specifies the number of characters to extract from the text string. The char_numbers value should be equal to or greater than zero; if it is greater than the length of the text string, the LEFT function will return the text string in full; if omitted, it will default to 1. While using as a VBA function, it is necessary to specify both the arguments, and if text_string contains Null, the function also returns Null.

Examples:

Cell A1 contains the text string “James Bond”.

=LEFT(A1,7)

Returns “James B”, which are the first 7 characters. Note that space is counted as a distinct character.

=LEFT(A1, 15)

Returns “James Bond”, which are all characters in cell A1, because the number 15 specified in the function exceeds the string length of 10 characters.

=LEFT(A1)

Returns “J”, a single character. Not specifying the number of characters, will default to 1.

————————————————————————————————————————–

RIGHT Function (Worksheet / VBA)

The Excel RIGHT function can be used both as a worksheet function and a VBA function. The RIGHT function returns the specified number of characters in a text string, starting from the last or right-most character. Use this function to extract a sub-string from the right part of a text string. Syntax: RIGHT(text_string, char_numbers). It is necessary to mention the text_string argument which is the text string from which you want to extract the specified number of characters. The char_numbers argument is optional (when using as a worksheet function), which specifies the number of characters to extract from the text string. The char_numbers value should be equal to or greater than zero; if it is greater than the length of the text string, the RIGHT function will return the text string in full; if omitted, it will default to 1. While using as a VBA function, it is necessary to specify both the arguments, and if text_string contains Null, the function also returns Null.

Examples:

Cell A1 contains the text string “James Bond”.

=RIGHT(A1,7)

Returns “es Bond”, which are the last 7 characters. Note that space is counted as a distinct character.

=RIGHT(A1, 15)

Returns “James Bond”, which are all characters in cell A1, because the number 15 specified in the function exceeds the string length of 10 characters.

=RIGHT(A1)

Returns “d”, a single character. Not specifying the number of characters, will default to 1.

———————————————————————————————————————–

MID Function (Worksheet / VBA)

The Excel MID function can be used both as a worksheet function and a VBA function. The MID function returns the specified number of characters in a text string, starting from a specified position (ie. starting from a specified character number). Use this function to extract a sub-string from any part of a text string. Syntax: MID(text_string, start_number, char_numbers). The text_string argument is the text string from which you want to extract the specified number of characters. The start_number argument specifies the character number from which to start extracting the sub-string, the first character in a text string being start_number 1 and incrementing towards the right. The char_numbers argument specifies the number of characters to extract from the text string.

If start_number is greater than the length of the text string, an empty string (zero length) is returned; if it is less than the length of the text string but together with char_numbers (ie. start_number PLUS char_numbers) it is greater than the length of the text string, the MID function will return the text string in full from the start_number position to the end of the text string.

Using MID function as a worksheet function, if a negative value is specified for char_numbers, MID will return the #VALUE! error value; if start_number is less than 1, MID will return the #VALUE! error value. All arguments are necessary to be specified when using as a worksheet function.

Using MID function as a VBA function: The char_numbers argument is optional when used as VBA function, and if omitted the function will return the text string in full from the start_number position to the end of the text string. All other arguments are necessary to be specified when using as a vba function. If text_string contains Null, the function also returns Null.

Examples:

Cell A1 contains the text string “James Bond”.

=MID(A1,2,6)

Returns “ames B”. Starts from the second character ie. “a”, and then specifies that 6 characters be returned starting from “a”.

=MID(A1,2, 15)

Returns “ames Bond”. Returns all characters starting from the second character of “a”, because the specified characters number 15 plus start number 2 (ie. total of 17) exceed the string length of 10 characters.

=MID(A1, 12,2)

Returns empty text (), because the start number of 12 exceeds the string length of 10 characters.

=MID(A1,0,7)

Returns the #VALUE! error value, because the start number (ie. zero) is less than 1.

—————————————————————————————————————————

LEN Function (Worksheet / VBA) 

The Excel LEN function can be used both as a worksheet function and a VBA function. The worksheet LEN function returns the number of characters in a text string. Use this function to get the length of a text string. Syntax: LEN(text_string). It is necessary to mention the text_string argument which is the text string whose length you want to get in number of characters. Note that spaces also count as characters. The worksheet LENB function returns the number of bytes used to represent the characters in a text string – counts each character as 1 byte except when a DBCS language [viz. Japanese, Chinese (Simplified), Chinese (Traditional), and Korean] is set as the default language wherein a character is counted as 2 bytes. Syntax: LENB(text_string).

While using LEN as a VBA function – Syntax: Len(text_string) or Len(variable_name) – you can use either a text string or a variable name, and the function will return a Long value representing the number of characters contained in the string or the number of bytes required to store a variable. Using the vba Len function for a variable of type variant will treat the variable as a String and return the number of characters contained in it. A text_string or variable containing Null, will also return Null. The vba Len function returns the number of characters in the string where the variable is of subtype String or Variant, and wherein the variable is of subtype numeric the function returns the number of bytes used to store the variable.

Example:

Cell A1 contains the text string “James Bond”.

=LEN(A1)

Returns 10, the string length measured by its number of characters.

————————————————————————————————————————–

FIND Function (Worksheet)

The FIND function returns the starting position of a text string, which it locates from within a second text string. The starting position is the number, which is calculated beginning from the first character of that second text string. FIND is case-sensitive. Syntax: FIND(text_to_find, within_text, start_number). It is necessary to mention the first 2 arguments, while start_number argument is optional. The text_to_find argument is the text string that you want to find or locate, and it cannot contain any wildcard characters. The within_text argument is the second text string within which you want to find or search. The start_number argument specifies the character number of within_text from where you want to start the search. The first character number of within_text is 1, incrementing towards the right. Omitting the start_number argument will start your search from the first character ie. number 1. The Excel FIND function is a worksheet function. FIND is not a VBA function but can be called or used in VBA code (worksheet functions can be called from VBA using the Application or Application.Worksheet objects).

If text_to_find cannot be located in within_text, or if start_number is specified as less than 1 or greater than the length of within_text, the FIND function will return the #VALUE! error value.

If you specify text_to_find as empty text (“”), the FIND function will match the character numbered start_number (or 1, if start_number is omitted) in the within_text.

Difference with SEARCH is that FIND is case sensitive and does not allow wildcard characters. If you don’t want to do a case sensitive search or use wildcard characters, you can use SEARCH.

Examples:

Cell A2 contains the text string “Australia”.

=FIND(“a”,A2)

Returns 6, which is the position of the first small cap character “a” in the string.

=FIND(“A”,A2)

Returns 1, which is the position of the first large cap character “a” in the string.

=FIND(“A”, A2,7)

Returns the #VALUE! error value, because text “A” is not found in the string.

=FIND(“a”, A2,7)

Returns 9, which is the position of the first small cap character “a” in the string, starting from character number 7.

—————————————————————————————————————–

SEARCH Function (Worksheet)

The SEARCH function returns the starting position of a text string, which it locates from within a second text string. The starting position is the number, which is calculated beginning from the first character of that second text string. SEARCH is not case-sensitive. Syntax: SEARCH(text_to_find, within_text, start_number). It is necessary to mention the first 2 arguments, while start_number argument is optional. The text_to_find argument is the text string that you want to find or locate, and it cannot contain any wildcard characters. The within_text argument is the second text string within which you want to find or search. The start_number argument specifies the character number of within_text from where you want to start the search. The first character number of within_text is 1, incrementing towards the right. Omitting the start_number argument will start your search from the first character ie. number 1. The Excel SEARCH function is a worksheet function. SEARCH is not a VBA function but can be called or used in VBA code (worksheet functions can be called from VBA using the Application or Application.Worksheet objects).

The text_to_find argument can include the wildcard characters, question mark (?) and asterisk (*). Any single character is matched using a question mark; any sequence of characters are matched using an asterisk. To find an actual question mark or asterisk, type a tilde (~) before the character.

If text_to_find cannot be located in within_text, or if start_number is specified as less than 1 or greater than the length of within_text, the FIND function will return the #VALUE! error value.

If you specify text_to_find as empty text (“”), the FIND function will match the character numbered start_number (or 1, if start_number is omitted) in the within_text.

Difference with FIND is that SEARCH is not case sensitive but allows use of wildcard characters. If you want to do a case sensitive search and not use wildcard characters, you can use FIND.

Examples:

Cell A2 contains the text string “Australia”.

=SEARCH(“a”,A2)

Returns 1, which is the position of the first character “a” or “A” in the string. Note: using the FIND Function will return 6, because FIND will look for the first small cap character “a”, being case-sensitive.

Cell A5 contains the text string “Daily is every day”.

=SEARCH(“d?y”, A5)

Returns 16, which is the starting character number of the first string which meets the criteria. This is the position of the first string that starts with “d” and ends with “y” and has only a single character inbetween.

=SEARCH(“d*y”, A5)

Returns 1, which is the starting character number of the first string which meets the criteria. This is the position of the first string that starts with “d” and ends with “y” and has any number of characters inbetween. Note: The formula “=SEARCH(“d*y”,A5,2)” will return 16.

—————————————————————————————————————————

REPLACE Function (Worksheet)

The REPLACE function replaces part of a text string with a new text string, based on specified number of characters and starting from a specified position. Syntax: REPLACE(old_text, start_number, number_of_chars, new_text). It is necessary to specify all arguments. The old_text argument is the text string in which you want to replace with new text. The start_number argument is the position of the character in old_text, which you want to replace (ie. position of the first character from which replacement should start). Position is the character number, first character being number 1 & incrementing towards the right. The number_of_chars is the number of charactres which will be replaced in the old_text (with new_text). The new_text is the text string which will replace the characters in old_text.

There is also a REPLACE Function in vba, which is not being discussed here. Refer our VBA section for details.

Example:

Cell A2 contains the text string “Australia”.

=REPLACE(A2, 8,2, “Asia”)

Returns “AustralAsia”. Replaces 2 characters, starting with the character number 8, with “Asia”.

————————————————————————————————————————–

SUBSTITUTE Function (Worksheet)

Use the SUBSTITUTE function to replace old text with new text in a text string. Substitute Function vs Replace Function: The difference is that in Substitute you replace specific text with new text while in Replace you replace any text based on its position and length within a text string. Syntax: SUBSTITUTE(text, old_text, new_text, instance_num). It is necessary to specify all arguments except instance_num. The text argument is the text string which contains the old text which you want to replace. The old_text argument is the text string which you want to replace with new text. The new_text argument is the new text which replaces the old text. The instance_num argument is a numerical value which specifies the occurrence of old text which you want to replace with new text. All occurrences of old text within the text string will be replaced with new text if you omit to specify this argument, else only the specified number of occurrences of the old text will be replaced with new text.

Examples:

Cell A2 contains the text string “Australia”.

=SUBSTITUTE(A2,”ia”, “Asia”)

Returns “AustralAsia”. Substitutes “ia” with “Asia”.

Cell A4 contains the text string “Tue, Feb 01, 2011”.

=SUBSTITUTE(A4,1, 2,3)

Returns “Tue, Feb 01, 2012”. Substitutes the third instance of “1” with “2”.

=SUBSTITUTE(A4, 1,2)

Returns “Tue, Feb 02, 2022”. Substitutes all instances of “1” with “2”.

————————————————————————————————————————–

Nesting FIND within MID (Example)

Cell A3 contains the text string “James Bond Daniel Craig”, and we want to replace a part of the text so as to have the new string “James Bond 007”.

=MID(A3,1,FIND(“Daniel Craig”, A3)-1)& “007”  [Formula]

Returns the string “James Bond 007”.

=FIND(“Daniel Craig”, A3) -> This part of the formula returns 12, which is the position of “Daniel Craig” in the text. Deduct 1 from this number, to arrive at the position of the last character in the text before “Daniel Craig”.

=MID(A3,1,FIND(“Daniel Craig”, A3)-1) -> This part returns the text “James Bond “, having deleted the text “Daniel Craig” which we wish to replace. MID Function returns 11 characters (note that there is a space after James Bond) from the text string, starting from character number 1.

=MID(A3,1,FIND(“Daniel Craig”, A3)-1)& “007” -> This completes the formula by adding the text “007”. We have now replaced the text “Daniel Craig” with “007”, to return the string “James Bond 007”.

Nesting FIND within REPLACE (Example)

Cell A3 contains the string “James Bond Daniel Craig”, and we want to replace a part of the text so as to have the new string “James Bond 007”.

=REPLACE(A3, FIND(“Daniel Craig”, A3),13, “007”)  [Formula]

Returns the string “James Bond 007”.

=FIND(“Daniel Craig”,A3) -> This part of the formula returns 12, which is the position of “Daniel Craig” in the text.

=REPLACE(A3, FIND(“Daniel Craig”,A3), 12,”007″) -> The REPLACE Function replaces in the A3 string, starting with character number 12, and replaces 12 characters (“Daniel Craig”) in the old text. This completes the formula having now replaced the text “Daniel Craig” with “007”, to return the string “James Bond 007”.

Splitting a String into Separate Cells (Examples)

Example 1

We have used the LEFT, RIGHT, MID, LEN and FIND Functions to manipulate text, and split a string into separate cells – in the string (column A) each word is separated with a comma and a blank space, and the length of Month, Date & Year is fixed (3, 2 & 4 characters respectively) while number of characters for Day vary.

Refer Table 1 – Dates in column A have been split into separate cells, in columns C to F. We take each cell at a time, and explain how it is done.

Cell C2 contains the formula “=LEFT(A2, FIND(“,”, A2)-1)”, and returns the day of week as “Tuesday”. FIND(“,”, A2)  part of the formula returns 8, which is the position of the first occurrence of the character “,” in the string. Reducing this by 1 (to 7), we use the LEFT function to extract the 7-character text (which is the day of week) appearing to the left of the character “,”.

Cell D2 contains the formula “=MID(A2, FIND(“,”,A2)+2, 3)”, and returns the month as “Feb”. FIND(“,”, A2)  part of the formula returns 8, which is the position of the first occurrence of the character “,” in the string. Increasing this by 2 (to 10), we use the MID function to return the 3-character text (which is the month), starting from character number 10.

Cell E2 contains the formula “=MID(A2, FIND(“,”, A2)+6, 2)”, and returns the date as “01”. FIND(“,”, A2)  part of the formula returns 8, which is the position of the first occurrence of the character “,” in the string. Increasing this by 6 (to 14), we use the MID function to return the 2-character text (which is the date), starting from character number 14.

Cell F2 contains the formula “=RIGHT(A2, 4)”, and returns the year as “2011”. RIGHT returns the last 4 characters in the text string, which is the corresponding year.

Example 2

The formulas have been made more complex, by using the full month name instead of the abbreviated form. Now the number of characters in the month name will vary, whereas the length of abbreviated month name was fixed (viz. 3).

Refer Table 2 – Dates in column A have been split into separate cells, in columns C to F. We take each cell at a time, and explain how it is done.

Cell C2 contains the formula “=LEFT(A2,FIND(“,”, A2)-1)”, and returns the day of week as “Tuesday”. FIND(“,”, A2)  part of the formula returns 8, which is the position of the first occurrence of the character “,” in the string. Reducing this by 1 (to 7), we use the LEFT function to extract the 7-character text (which is the day of week) appearing to the left of the character “,”.

Cell D2 contains the formula “=MID(A2, FIND(“,”, A2)+2, LEN(A2)-9-(FIND(“,”, A2)+1))”, and returns the month as “February”. FIND(“,”,A2)  part of the formula returns 8, which is the position of the first occurrence of the character “,” in the string. Increasing this by 2 (to 10), we use the MID function to return the text (which is the month), starting from character number 10. But for this, we need to know the length of this text (ie. month name), which is determined by “LEN(A2)-9- (FIND(“,”, A2)+1)”. The string length of 26 [LEN(A2)] is reduced by: (i) 9, which is the number of characters after the month name ie. ” 01, 2011″ (note the 2 spaces inbetween); and (ii) the formula part (FIND(“,”,A2)+1) which returns 9, which are the number of characters before start of month name ie. “Tuesday, ” (note the 1 space inbetween). Therefore, length of the month name (“February”) is determined to be equal to: 26 minus 9 minus 9, which is 8.

Cell E2 contains the formula “=MID(A2,FIND(“,”, A2, FIND(“,”, A2)+1)-2, 2)”, and returns the date as “01”. We use the MID function to return the 2-character text (which is the date), but the starting character number is to be determined. The formula part FIND(“,”,A2,FIND(“,”,A2)+1)-2 determines this character number to start from. The formula FIND(“,”,A2)+1 returns 9, which is the next character number after the first occurrence of “,”. The formula FIND(“,”,A2,FIND(“,”,A2)+1)  returns 21, whihc is the position of the second occurrence of “,”. Reducing this by 2 (to 19) will give us the starting character number of date, which we had set to find out. Therefore, the MID formula to find the date, will return the 2-character text date, starting from character number 19.

Cell F2 contains the formula “=RIGHT(A2, 4)”, and returns the year as “2011”. RIGHT returns the last 4 characters in the text string, which is the corresponding year.