Microsoft Excel Excel Text to Columns and Data Cleanup — Questions and Answers
Question 1: On which Excel ribbon tab is the Text to Columns command located?
- Home
- Review
- Data (Correct answer)
- View
Correct answer: Data
Text to Columns sits in the Data Tools group of the Data tab.
The Text to Columns wizard is found on the Data tab in the Data Tools group. It splits the contents of one column into several based on delimiters or fixed widths. Neighboring commands include Flash Fill, Remove Duplicates, and Data Validation.
Question 2: A column contains full names like Smith, John. Which Text to Columns option best splits them into last and first name columns?
- Fixed width
- Delimited with comma as the delimiter (Correct answer)
- Delimited with tab as the delimiter
- Fixed width with a break at position 5
Correct answer: Delimited with comma as the delimiter
The comma consistently separates the two parts, making a delimited split ideal.
When a consistent character separates data parts, choose Delimited and select that character. Here the comma separates last name from first name, so a comma delimiter works regardless of name length. Fixed width would fail because names vary in length.
Question 3: In the Text to Columns wizard, what does the option Treat consecutive delimiters as one do?
- Merges all columns back into one
- Prevents empty columns when several delimiters appear in a row (Correct answer)
- Removes all delimiters from the text
- Converts delimiters to line breaks
Correct answer: Prevents empty columns when several delimiters appear in a row
It collapses repeated delimiters so blank fields are not created.
When text contains repeated delimiters, such as several spaces between words, a normal split creates empty cells between them. Checking Treat consecutive delimiters as one collapses the sequence into a single split point. This is especially helpful with space-delimited data that has irregular spacing.
Question 4: Which Excel function removes leading, trailing, and extra internal spaces from text?
- CLEAN
- TRIM (Correct answer)
- SUBSTITUTE
- PROPER
Correct answer: TRIM
TRIM strips extra spaces, leaving single spaces between words.
TRIM removes all spaces from text except single spaces between words. It is a standard first step in cleaning imported data where padding spaces cause lookups to fail. CLEAN removes non-printable characters, while PROPER changes capitalization.
Question 5: What is the purpose of the Destination box on the final step of the Text to Columns wizard?
- To choose which worksheet to delete
- To specify where the split data should be placed (Correct answer)
- To set the print area
- To name the resulting table
Correct answer: To specify where the split data should be placed
Destination controls the top-left cell where the separated columns are written.
By default Text to Columns writes results starting at the original column, overwriting adjacent cells. The Destination box lets you point the output to a different location so existing data is preserved. Setting it to an empty area is a common safety practice.
Question 6: Which Data tab command removes rows that are exact duplicates based on selected columns?
- Consolidate
- Remove Duplicates (Correct answer)
- Subtotal
- Group
Correct answer: Remove Duplicates
Remove Duplicates deletes repeated rows using the columns you check.
Remove Duplicates, located in the Data Tools group, scans the selected range and deletes rows whose values match in all checked columns. Excel keeps the first occurrence and reports how many duplicates were removed. Sorting or trimming data first improves accuracy.
Microsoft Excel Excel Text to Columns and Data Cleanup
On which Excel ribbon tab is the Text to Columns command located?