Accountancy Part CSpreadsheet

Which formula can be used to prevent leading or trailing spaces in a…

Which formula can be used to prevent leading or trailing spaces in a data validation rule?

Answer & explanation

Correct answer: option 1

The correct answer is Option (1) → =TRIM(A1)=A1

A data validation rule with the “Custom” setting accepts an entry only when the formula given returns TRUE. TRIM removes the spaces before and after a text. So the formula =TRIM(A1)=A1 compares the entry with its trimmed form: if the user has typed a space at the start or the end, the two differ, the formula returns FALSE and the entry is refused.

Option (2) =LEN(A1)>10 — Incorrect. LEN counts the characters, so this rule only checks that the entry is longer than 10 characters; it says nothing about spaces.

Option (3) =ISNUMBER(A1) — Incorrect. This rule allows only numbers in the cell; it does not look for extra spaces in a text.

Option (4) =WEEKDAY(A1)<>1 — Incorrect. WEEKDAY returns 1 for a Sunday, so this rule blocks dates that fall on a Sunday; it is used to stop entries on holidays.

NCERT (2026-27 edition):
Data Validation – “… We can prevent user from adding spaces before or after the text in entry. The TRIM function removes spaces before and after the text. This formula checks that the entry is equal to trimmed entry (Figure 2.50(b).”

NCERT (2026-27 edition):
Data Validation

Extract from the NCERT textbook