Excel Text to Columns and Data Cleanup Flashcards

On which Excel ribbon tab is the Text to Columns command located?

Answer: Data

Text to Columns sits in the Data Tools group of the Data tab.

A column contains full names like Smith, John. Which Text to Columns option best splits them into last and first name columns?

Answer: Delimited with comma as the delimiter

The comma consistently separates the two parts, making a delimited split ideal.

In the Text to Columns wizard, what does the option Treat consecutive delimiters as one do?

Answer: Prevents empty columns when several delimiters appear in a row

It collapses repeated delimiters so blank fields are not created.

Which Excel function removes leading, trailing, and extra internal spaces from text?

Answer: TRIM

TRIM strips extra spaces, leaving single spaces between words.

What is the purpose of the Destination box on the final step of the Text to Columns wizard?

Answer: To specify where the split data should be placed

Destination controls the top-left cell where the separated columns are written.

Which Data tab command removes rows that are exact duplicates based on selected columns?

Answer: Remove Duplicates

Remove Duplicates deletes repeated rows using the columns you check.

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?

Answer: Run Text to Columns and click Finish without changing options

Text to Columns re-parses each cell, converting numeric text to true numbers.

Which Excel feature automatically fills a column by detecting a pattern from examples you type, such as extracting first names?

Answer: Flash Fill

Flash Fill recognizes patterns and completes the column with Ctrl+E.

Which function removes non-printable characters, such as those from web or database exports, from a text string?

Answer: CLEAN

CLEAN strips the first 32 non-printing ASCII characters.

In Text to Columns, which choice is appropriate when a report has every field starting at the same character position on each line?

Answer: Fixed width

Fixed width splits at specified character positions rather than by a character.

Which formula converts the text in A1 to have only the first letter of each word capitalized?

Answer: =PROPER(A1)

PROPER capitalizes the first letter of each word and lowercases the rest.

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?

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.