EXCEL: TEXT Functions
- The Excel FIND function returns the position of a specific character within a text string.
=FIND(find_text, within_text, [start_num])
find_text: the character or substring you want to find.
within_text: the text string to be searched within.
start_num: the optional argument that specifies from which character the search should begin.
=FIND(“x”, “excel”) returns 2 as it is the 2nd letter in the word excel.
FIND function is case sensitive. Use the SEARCH function to find the position of text without case sensitivity.
2. The TRIM function will remove unwanted leading and trailing space and leave only a single space between words.
=TRIM(text)
text: The text from which to remove extra space.
3. The CONCAT function joins values supplied as references or constants. CONCAT allows you to supply a range of cells to join, in addition to individual cell references. CONCAT will join text values without delimiter.
=CONCAT(text1, [text2],..)
text1: First text value, cell reference or range.
A1: excel A2: spreadsheet A3: cells
=CONCAT(A1:A3) returns excelspreadsheetcells
4. The TEXTJOIN function joins values with a given delimiter. TEXTJOIN allows
=TEXTJOIN (delimiter, ignore_empty, text1, [text2], …)
delimiter — Separator between each text.
ignore_empty — Whether to ignore empty cells or not.
text1 — First text value or range.
text2 — [optional] Second text value or range.
5. The UPPER function will return the uppercase version of a given text string.
Numbers and punctuation are not affected.
=UPPER(pallavi) returns PALLAVI
6. The LOWER function returns the lowercase version of a given string.
Numbers and punctuations are not affected.
=LOWER(PALLAVI) returns pallavi.
7. The PROPER function capitalizes the first word in a given string. Numbers and punctuations are not affected.
=PROPER(san Francisco, ca) returns San Francisco, Ca
8. The REPLACE function replaces text based on location.
=REPLACE (old_text, start_num, num_chars, new_text)
old_text — The text to replace.
start_num — The starting location in the text to search.
num_chars — The number of characters to replace.
new_text — The text to replace old_text with.
9. The SUBSTITUTE function replaces text based on content.
=SUBSTITUTE (text, old_text, new_text, [instance])
text — The text to change.
old_text — The text to replace.
new_text — The text to replace with.
instance — [optional] The instance to replace. If not supplied, all instances are replaced.
10. The TEXT function returns a number in a given format, as text.
=TEXT (value, format_text)
value — The number to convert.
format_text — The number format to use.
11. The REPT function repeats a given character a given number of times.
=REPT(“*”, 5) returns *****
12. The Excel VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value.
=VALUE(“$100”) returns 100.
13. The Excel LEN function will return the length of a given text string as a number of characters.
=LEN(San Francisco) returns 13.
14. The Excel LET function makes it easier to write certain complex formulas, by making it possible to declare and assign values to variables inside a formula.
=LET (name1, value1, [name2/value2], …, result)
name1 — First name to assign. Must begin with a letter.
value1 — The value or calculation to assign to name 1.
name2/value2 — [optional] Second name and value. Entered as a pair of arguments.
result — A calculation or a variable previously calculated.
