In MS-Excel, to remove extra spaces from a text except single space between words, we can use _______ function.

Question

Accepted Answer

The correct option is (2):

The function explicitly designed in MS-Excel to handle data cleaning by removing irregular spacing is the **TRIM** function. Its essential role is to eliminate all spaces at the beginning (leading spaces) and end (trailing spaces) of a text string, as well as reduce any multiple spaces found between words down to a single, standard space. This feature is fundamental in data preparation, particularly when you import data from external systems or databases that may contain hidden or erratic spacing, which could otherwise cause lookup and text-comparison formulas to fail. The simplicity of its syntax, `=TRIM(text)`, makes it an indispensable tool for standardizing textual data.

The incorrect option is (1):
**TREND** is an Excel statistical function used for forecasting and calculating values along a linear trend line. It utilizes the least squares method to fit a straight line to known data points (known\_y's and known\_x's) and returns predicted y-values for a new set of x-values. It is purely a mathematical and statistical analysis tool. The function operates exclusively on numerical data to predict future performance or model a linear relationship. It possesses no capabilities whatsoever for manipulating or cleaning text, such as removing extra spaces. Therefore, it cannot be used for the purpose stated in the question.

The incorrect option is (3):
The **TEXT** function's primary purpose is to convert a numeric value into a text string while applying a specific number format. This is commonly used for displaying dates, percentages, or currency values exactly as desired, for example, converting a number to a dollar amount format. Its syntax requires a value and a specified format code, like `=TEXT(A1, "dd-mmm-yyyy")`. It is a formatting and data type conversion utility. While it works with text as its final output, it does not clean or remove unwanted spaces from an *existing* text input.

The incorrect option is (4):
The **PROPER** function is a text manipulation function, but its intended function is to change the case of letters, not to manage spacing. It automatically converts the first letter of every word in a text string to uppercase, and all remaining letters in those words to lowercase. This is useful for ensuring names and titles follow standard capitalization rules. Although it affects the text content, it completely ignores any excess white space. If a text string has three spaces between two words, the `PROPER` function will change the case but leave all three spaces intact.