Microsoft Excel Excel Text to Columns and Data Cleanup 2 — Questions and Answers
Question 1: 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 2: 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 3: 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 4: 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 5: 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 6: 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.
Microsoft Excel Excel Text to Columns and Data Cleanup 2
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?