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.
Question 7: A column of numbers imported from a text file is stored as text and will not sum. Which technique converts the entire column to numbers at once?
- Run Text to Columns and click Finish without changing options (Correct answer)
- Apply bold formatting
- Change the font to Calibri
- Use Freeze Panes
Correct answer: Run Text to Columns and click Finish without changing options
Text to Columns re-parses each cell, converting numeric text to true numbers.
A well-known trick for text-stored numbers is to select the column, open Text to Columns, and click Finish. Excel reprocesses every cell and recognizes numeric text as numbers. Alternatives include multiplying by 1 or using the VALUE function.
Question 8: Which Excel feature automatically fills a column by detecting a pattern from examples you type, such as extracting first names?
- AutoSum
- Flash Fill (Correct answer)
- Goal Seek
- Data Validation
Correct answer: Flash Fill
Flash Fill recognizes patterns and completes the column with Ctrl+E.
Flash Fill, introduced in Excel 2013, watches the examples you type in a column adjacent to your data and proposes the rest of the values. Pressing Ctrl+E or using Data > Flash Fill applies the pattern. It is a fast alternative to Text to Columns for simple extractions.
Question 9: Which function removes non-printable characters, such as those from web or database exports, from a text string?
- TRIM
- CLEAN (Correct answer)
- LEN
- EXACT
Correct answer: CLEAN
CLEAN strips the first 32 non-printing ASCII characters.
CLEAN removes characters with codes 0 to 31 that often arrive in imported data as line feeds or control characters. It is frequently combined with TRIM, as in TRIM(CLEAN(A1)), to fully sanitize text. Note that CLEAN does not remove the non-breaking space (code 160).
Question 10: In Text to Columns, which choice is appropriate when a report has every field starting at the same character position on each line?
- Delimited by space
- Delimited by semicolon
- Fixed width (Correct answer)
- Delimited by comma
Correct answer: Fixed width
Fixed width splits at specified character positions rather than by a character.
Fixed width lets you place break lines at exact character positions in the wizard's preview. It is ideal for aligned reports where columns begin at consistent positions. Delimited options depend on separator characters, which such reports may not contain.
Question 11: Which formula converts the text in A1 to have only the first letter of each word capitalized?
- =UPPER(A1)
- =LOWER(A1)
- =PROPER(A1) (Correct answer)
- =CAPITAL(A1)
Correct answer: =PROPER(A1)
PROPER capitalizes the first letter of each word and lowercases the rest.
PROPER standardizes name and title capitalization, changing text like jOHN sMITH to John Smith. UPPER and LOWER convert the entire string to one case. CAPITAL is not a valid Excel function.
Question 12: You need to replace all hyphens in a phone number column with nothing so the digits are continuous. Which method accomplishes this in one action for the whole column?
- Find and Replace with hyphen in Find what and nothing in Replace with (Correct answer)
- Text to Columns with fixed width
- Sort the column ascending
- Apply a Number format
Correct answer: Find and Replace with hyphen in Find what and nothing in Replace with
Find and Replace (Ctrl+H) removes a character across the selection instantly.
Find and Replace lets you search a range for a character and replace it with an empty string, effectively deleting it. Selecting the column first limits the change to that range. The SUBSTITUTE function achieves the same result via formula if you need a non-destructive approach.
