Excel’s text functions are essential tools for manipulating and analyzing string data. Below is a concise overview of key text functions, categorized for clarity:
Basic String Extraction Functions
– LEFT(text, [num_chars]): Extracts a specified number of characters from the beginning of a text string. Example: `=LEFT(A1, 3)` gets the first three characters from cell A1.
– RIGHT(text, [num_chars]): Extracts a specified number of characters from the end of a text string. Example: `=RIGHT(A1, 2)` gets the last two characters.
– MID(text, start_num, num_chars): Extracts a substring from the middle of a text string, starting at a specified position. Example: `=MID(A1, 2, 4)` starts from the second character and takes four characters.
String Length and Case Functions
– LEN(text): Returns the number of characters in a text string. Example: `=LEN(A1)` counts the characters in cell A1.
– UPPER(text): Converts text to uppercase. Example: `=UPPER(A1)` makes all letters uppercase.
– LOWER(text): Converts text to lowercase. Example: `=LOWER(A1)` makes all letters lowercase.
– PROPER(text): Capitalizes the first letter of each word in a text string. Example: `=PROPER(A1)` formats text like a proper noun.
Text Combination and Cleaning Functions
– CONCATENATE(text1, [text2], …) or CONCAT(text1, [text2], …): Joins two or more text strings into one. Example: `=CONCAT(A1, ” “, B1)` combines values from A1 and B1 with a space.
– TRIM(text): Removes extra spaces from text except for single spaces between words. Example: `=TRIM(A1)` cleans up messy text.
– SUBSTITUTE(text, old_text, new_text, [instance_num]): Replaces existing text with new text in a string. Example: `=SUBSTITUTE(A1, “old”, “new”)` swaps “old” with “new”.
Search and Replacement Functions
– FIND(find_text, within_text, [start_num]): Returns the starting position of specified text within a string (case-sensitive). Example: `=FIND(“a”, A1)` finds the position of “a”.
– SEARCH(find_text, within_text, [start_num]): Similar to FIND but not case-sensitive. Example: `=SEARCH(“A”, A1)` ignores case.
– REPLACE(old_text, start_num, num_chars, new_text): Replaces part of a text string with new text. Example: `=REPLACE(A1, 1, 2, “New”)` replaces the first two characters.
Advanced Tips
– These functions can be nested for complex operations, e.g., combining MID with UPPER for extracted text in uppercase.
– Always use them in combination with other functions like IF or VLOOKUP for dynamic data handling.
– For newer Excel versions, TEXTJOIN is useful for joining arrays of text with a delimiter: `=TEXTJOIN(“, “, TRUE, A1:A10)`.
This overview covers the most commonly used text functions to help with everyday tasks in Excel.
Table of Contents
- Part 1: OnlineExamMaker AI Quiz Generator – Save Time and Efforts
- Part 2: 20 Excel Text Functions Quiz Questions & Answers
- Part 3: OnlineExamMaker AI Question Generator: Generate Questions for Any Topic
Part 1: OnlineExamMaker AI Quiz Generator – Save Time and Efforts
What’s the best way to create a Excel Text Functions quiz online? OnlineExamMaker is the best AI quiz making software for you. No coding, and no design skills required. If you don’t have the time to create your online quiz from scratch, you are able to use OnlineExamMaker AI Question Generator to create question automatically, then add them into your online assessment. What is more, the platform leverages AI proctoring and AI grading features to streamline the process while ensuring exam integrity.
Key features of OnlineExamMaker:
● Combines AI webcam monitoring to capture cheating activities during online exam.
● Allow the quiz taker to answer by uploading video or a Word document, adding an image, and recording an audio file.
● Automatically scores multiple-choice, true/false, and even open-ended/audio responses using AI, reducing manual work.
● OnlineExamMaker API offers private access for developers to extract your exam data back into your system automatically.
Automatically generate questions using AI
Part 2: 20 Excel Text Functions Quiz Questions & Answers
or
Question 1:
What does the LEN function in Excel do?
A) Converts text to lowercase
B) Returns the number of characters in a text string
C) Extracts text from the right side of a string
D) Joins two or more text strings
Answer: B
Explanation: The LEN function calculates the length of a text string, including spaces, which is useful for determining the number of characters.
Question 2:
Which function extracts a specified number of characters from the beginning of a text string?
A) RIGHT
B) MID
C) LEFT
D) TRIM
Answer: C
Explanation: The LEFT function takes a text string and a number of characters as arguments, returning the substring from the start.
Question 3:
What is the purpose of the RIGHT function?
A) Converts text to proper case
B) Extracts characters from the end of a text string
C) Finds the position of a substring
D) Replaces text in a string
Answer: B
Explanation: The RIGHT function extracts a specified number of characters from the end of a text string.
Question 4:
How does the MID function work?
A) It returns the middle character of a string
B) It extracts a substring from any position in a text string
C) It converts text to uppercase
D) It removes extra spaces from text
Answer: B
Explanation: The MID function requires a text string, a starting position, and the number of characters to extract, allowing for flexible substring retrieval.
Question 5:
What does the UPPER function do?
A) Converts all letters in a text string to uppercase
B) Trims spaces from a string
C) Searches for a substring
D) Concatenates strings
Answer: A
Explanation: The UPPER function changes all lowercase letters in a text string to uppercase, while leaving other characters unchanged.
Question 6:
Which function is used to convert text to lowercase?
A) PROPER
B) LOWER
C) LEN
D) SUBSTITUTE
Answer: B
Explanation: The LOWER function converts all uppercase letters in a text string to lowercase, making text uniform.
Question 7:
What is the main function of the PROPER function?
A) Capitalizes the first letter of each word in a text string
B) Replaces occurrences of a substring
C) Finds the exact position of text
D) Joins text with a delimiter
Answer: A
Explanation: The PROPER function capitalizes the first letter of each word and converts the rest to lowercase, useful for titles.
Question 8:
How does the TRIM function help with text?
A) It removes all spaces from a text string
B) It eliminates extra spaces between words but keeps single spaces
C) It converts text to a numerical value
D) It extracts the first word
Answer: B
Explanation: The TRIM function cleans text by removing leading, trailing, and extra spaces between words, improving readability.
Question 9:
What does the FIND function do?
A) Locates the position of a substring within a text string (case-sensitive)
B) Replaces text in a string
C) Converts numbers to text
D) Joins multiple strings
Answer: A
Explanation: The FIND function returns the starting position of one text string within another, and it is case-sensitive.
Question 10:
Which function is similar to FIND but is not case-sensitive?
A) SEARCH
B) REPLACE
C) LEN
D) MID
Answer: A
Explanation: The SEARCH function works like FIND but ignores case, making it more flexible for text searches.
Question 11:
What is the purpose of the REPLACE function?
A) It substitutes one substring for another in a text string
B) It replaces a portion of a text string based on position
C) It concatenates strings without a delimiter
D) It trims excess characters
Answer: B
Explanation: The REPLACE function changes characters in a text string by specifying the start position, number of characters, and new text.
Question 12:
How does the SUBSTITUTE function differ from REPLACE?
A) SUBSTITUTE replaces all occurrences of a substring, while REPLACE targets a specific position
B) SUBSTITUTE only works on numbers
C) REPLACE is case-sensitive only
D) SUBSTITUTE adds text instead of replacing
Answer: A
Explanation: The SUBSTITUTE function replaces all instances of a specified substring with new text, unlike REPLACE which targets a fixed position.
Question 13:
What does the CONCATENATE function do?
A) It joins two or more text strings into one
B) It splits a text string into parts
C) It converts text to a date
D) It finds the length of text
Answer: A
Explanation: The CONCATENATE function combines multiple strings or values into a single text string.
Question 14:
In Excel, which function can join text with a specified delimiter?
A) TEXTJOIN
B) MID
C) LEFT
D) UPPER
Answer: A
Explanation: The TEXTJOIN function concatenates a range or array of strings with a delimiter, such as a comma or space.
Question 15:
What is the role of the TEXT function?
A) It formats a number or date as text with a specified format
B) It extracts text from a cell
C) It converts text to a number
D) It searches for patterns
Answer: A
Explanation: The TEXT function converts a value to text in a specific number format, like currency or date, for display purposes.
Question 16:
If you want to remove non-printable characters from text, which function might you use?
A) CLEAN
B) TRIM
C) PROPER
D) LEN
Answer: A
Explanation: The CLEAN function removes non-printable characters from text, which is helpful when importing data from other sources.
Question 17:
How can you use the EXACT function with text?
A) It compares two text strings and returns TRUE if they are exactly the same
B) It extracts exact substrings
C) It exactly replaces text
D) It exactly formats numbers
Answer: A
Explanation: The EXACT function performs a case-sensitive comparison of two text strings and returns TRUE or FALSE.
Question 18:
What does the VALUE function do in relation to text?
A) It converts a text string that represents a number to a numeric value
B) It values the length of text
C) It adds values to text
D) It substitutes values in strings
Answer: A
Explanation: The VALUE function turns text that looks like a number into an actual numeric value for calculations.
Question 19:
Which function is best for repeating a text string a certain number of times?
A) REPT
B) CONCATENATE
C) MID
D) SEARCH
Answer: A
Explanation: The REPT function repeats a text string a specified number of times, useful for creating patterns or padding.
Question 20:
In a formula, how would you combine the LEFT and LEN functions?
A) To extract all characters except the last one from a string
B) To find the total length and then trim text
C) To convert the result to uppercase
D) To search within the extracted text
Answer: A
Explanation: Using LEFT with LEN can extract a substring by taking the full length minus a specified number, such as removing the last character.
or
Part 3: OnlineExamMaker AI Question Generator: Generate Questions for Any Topic
Automatically generate questions using AI