Text Functions & Data Cleaning Test

What specific types of spaces does Excel's standard TRIM function remove from a text string?

In target cell B2, remove the leading and trailing whitespace from the client name in A2 using the TRIM function.

What is the key functional difference between the FIND and SEARCH functions in Excel?

In target cell B2, convert the vendor company name in A2 to all uppercase letters using the UPPER function.

Which of the following functions are native Excel functions used to change the letter casing of text strings? (Select all that apply)

In target cell B2, extract the 3-character department prefix from the SKU code in A2 using the LEFT function.

What is the primary role of the CLEAN function when preparing external data imported into Excel?

In target cell B2, extract the 2-character country code suffix from the batch code in A2 using the RIGHT function.

Which of the following methods can be used to combine text from multiple cells into one cell in Excel? (Select all that apply)

In the formula =MID(text, start_num, num_chars), what occurs if start_num is greater than the total length of text?

What does the formula =LEN("North Region ") return?

In target cell B2, find the 1-based character position of the hyphen delimiter "-" in the tracking number in A2 using the FIND function.

Which of the following statements correctly describe arguments and features of the TEXTJOIN function? (Select all that apply)

In target cell C2, combine the first name in A2 and the last name in B2 separated by a single space using the ampersand (&) operator.

Scenario Background: HR provided a roster with mixed-case first names and last names. The IT directory requires clean corporate email addresses formatted as lowercase 'firstname.lastname@acme.com'.

In target cell C2, generate the standardized company email address for the employee in row 2 using their first name (A2), last name (B2), and the corporate domain '@acme.com'. Ensure all characters are lowercase.

If cell A1 contains the number 45892, what data type does the formula =LEFT(A1, 2) return in Excel?

In target cell B2, dynamically extract only the first name from the full name in A2 by combining LEFT and FIND to locate the space character.

Which of the following scenarios will cause a text formula to return a #VALUE! error? (Select all that apply)

What will the formula =PROPER("annual Q4 budget report") output?

Which of the following formula techniques successfully join Street (A2), City (B2), and State (C2) with a comma and space separator ", "? (Select all that apply)

Why does the standard Excel TRIM function often fail to remove trailing spaces from data imported from web pages or ERP HTML exports?

In target cell B2, extract the domain name that appears after the '@' character from the email address in A2 using MID, FIND, and LEN.

What advantages does TEXTJOIN offer that are not natively supported by the standard CONCAT function? (Select all that apply)

Scenario Background: The marketing database has contact names with irregular capitalization and excess leading/trailing whitespace. Client communications require properly capitalized, clean full names.

In target cell B2, clean the messy raw name in A2 by removing all excess spaces and formatting the name in Proper Case (Title Case).

What should you wrap around a SEARCH or FIND formula to prevent it from returning a #VALUE! error when the search string is not found?

Scenario Background: Payroll needs employee last names separated from full names formatted as 'FirstName LastName' for alphabetical filing.

In target cell B2, extract the employee's last name from cell A2 dynamically, using RIGHT, LEN, and FIND to accommodate names of varying length.

Which of the following wildcards are supported by the SEARCH function in Excel? (Select all that apply)

Scenario Background: ERP migration received customer records with sporadic whitespace and lowercase division letters (e.g., ' c-9021-east '). The master database requires zero extra whitespace and all uppercase letters (e.g., 'C-9021-EAST').

In target cell B2, clean the raw customer code in A2 by trimming all extra whitespace and standardizing all characters to uppercase.

If cell A2 contains the text code 'INV-950' and the formula =RIGHT(A2, 3) returns the text '950', which function converts this result into a true numeric value for math operations?

Scenario Background: Operations requires a unique tracking code for warehouse assets by concatenating the Location Code (A2), Asset Category (B2), and Unit ID (C2) with hyphens.

In target cell D2, build the composite asset tag by joining Location (A2), Category (B2), and Unit ID (C2) using a hyphen "-" delimiter.