MO-211 practice question 20 of 35

What is the function of the SUBSTITUTE function in Excel?

Full set: 355 questions

Domain Insert and format text, shapes, and images

Answer options
A: It calculates the sum of a range of numbers.
B: It counts the number of cells in a range that meet a specific condition.
C: It finds and replaces a specific character or text string within a text string. Correct answer
D: It creates a new worksheet.
Correct Answer:
C. It finds and replaces a specific character or text string within a text string.
Explanation

The SUBSTITUTE function is a text manipulation function in Microsoft Excel. Its primary purpose is to replace one or more instances of a specific text string (oldtext) with another text string (newtext) within a given text string. The function is case-sensitive. Its syntax is SUBSTITUTE(text, oldtext, newtext, [instancenum]), where the optional instancenum argument specifies which occurrence of oldtext to replace. If instancenum is omitted, all occurrences are replaced.

Why Incorrect

A. This describes the SUM function, which is used for mathematical aggregation, not text manipulation.
B. This describes the COUNTIF or COUNTIFS functions, which are used for conditional counting of cells.
D. Creating a new worksheet is an application-level action performed via the user interface or VBA, not a cell formula function.

References

1. Microsoft Corporation. (n.d.). SUBSTITUTE function. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df383e2a.

Reference Details: The "Description" section explicitly states

"The SUBSTITUTE function replaces existing text with new text in a text string."

2. University of Wisconsin-Madison

Division of Extension. (n.d.). Excel - Text Functions. Retrieved from https://fyi.extension.wisc.edu/grain/files/2016/07/Excel-Text-Functions.pdf.

Reference Details: Page 1

in the table of Text Functions

describes SUBSTITUTE(text

oldtext

newtext

instancenum) as: "Substitutes newtext for oldtext in a text string."

Scroll to Top