numchars Returns the number of characters specified starting from the beginning of the text string Syntax Text The text that contains the characters you want to extract numchars Specifies the number of characters you want to extract starting from the leftmost character ID: 1044580
Download Presentation The PPT/PDF document "Excel Text Functions 1 LEFT(text, [" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
1. ExcelText Functions1
2. LEFT(text, [num_chars]))Returns the number of characters specified starting from the beginning of the text stringSyntaxText: The text that contains the characters you want to extractnum_chars: Specifies the number of characters you want to extract starting from the leftmost character.Default: 12More… 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“ (all A1 chars), because the number 15 exceeds the string length of 10 characters. =LEFT(A1) Returns "J", a single character. Not specifying the number of characters, will default to 1.
3. RIGHT(text, [num_chars])Returns the number of characters specified starting from the end of the text stringSyntaxText: The text that contains the characters you want to extractnum_chars: Specifies the number of characters you want to extract starting from the rightmost character.Default: 13More… 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“ (all A1 chars) because the number exceeds the string length of 10 characters. =RIGHT(A1) Returns "d", a single character. Not specifying the number of characters, will default to 1.
4. MID(text, start_num, num_chars)Returns a specific number of characters from a text string, starting at the position you specify, based on the numbers you specifySyntaxText: The text that contains the characters you want to extractStart_num: The position of the first character you want to extract in the text.Num_chars: Specifies the number of characters you want mid to display4More… Cell A1 contains the text string "James Bond“: =MID(A1,2,6) Returns "ames B". Specifies that 6 characters be returned starting from the second character, "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.
5. CONCATENATE(text1,[text2],…)Joins up to 255 text strings into one text stringConcatenate operator & (other use: function “criteria”)SyntaxText1: The first text item to be concatenatedText2: The second text item to be concatenated--optional5
6. TRIM(text)TRIM strips extra spaces from text, leaving only single spaces between words and no space characters at the start or end of the text.TRIM only removes the ASCII space character (32) from text.6
7. LEN(text)Returns the number of characters in a text string.7
8. UPPER(text), LOWER(text)Converts text to uppercase/lowercase8
9. PROPER(text)Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.9
10. EXACT(text1, text2)Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. 10
11. REPLACE(old_text,start_num,num_chars,new_text)Replaces part of a text string, based on the number of characters you specify with a different text string. SyntaxOld_text: Text which you want to replace some charactersStart_num: The position of the character in old_text you want to replaceNum_chars: The number of characters in the old_text that you want to replace with the new_textNew_text: The text that will replace the characters in the old_text11
12. SUBSTITUTE(text, old_text, new_text, [instance_num])Substitutes new_text for old_text in a text string.Syntaxtext: Text or reference to a cell containing text for which you want to substitute charactersOld_text: The text you want to replaceNew_text: The text you want to replace old_text withInstance_num: Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.12More… 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".
13. FIND(find_text, within_text, [start_num])Locates one text string within a second text string and displays the starting position of the first text string from the first character of the second text string… IS case sensitive (along with CONVERT)SyntaxFind_text: The text you want to findWithin_text: The text containing the text you want to findStart_num: Specifies the character at which to start the searchDefault 113More… 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 #7. The answer is NOT 4 since the function is defined to say “displays the starting position of the first argument from the first character of the second argument”
14. Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text. Can use the MID or REPLACE functions to change the text (see examples)You can use the wildcard characters — question mark (?) and asterisk (*) in find_text. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.Differences between FIND and SEARCH: FIND is case sensitive and SEARCH is not case sensitiveFIND doesn't allow wildcard characters but SEARCH does allow wildcard characters14SEARCH(find_text, within_text, [start_num])
15. SEARCH wildcard examples15
16. Functions with Case Sensitive issuesCase-sensitiveEXACTCONVERTFINDEffect casePROPERUPPERLOWER16