Text to columns in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Splitting Full Names into First and Last Names

You have a column A with full names like "John Smith" in each cell. You want to split these into first and last names using Excel's Text to Columns feature with space as the delimiter.

What will be the content of cells A1 and B1 after applying Text to Columns on A1 containing "John Smith"?

A A1: John, B1: (empty)

B A1: John Smith, B1: (empty)

C A1: John, B1: Smith

D A1: Smith, B1: John

💡 Hint

Think about how Text to Columns splits text based on the delimiter and places parts in adjacent columns.

Text to Columns splits the text at the space and places the first part in the original column and the second part in the next column to the right. So A1 gets "John" and B1 gets "Smith".

❓ Function Choice

intermediate

Choosing the Correct Delimiter for Text to Columns

You have a list of email addresses in column A like "[e-mail]". You want to split the username and domain into two columns using Text to Columns.

Which delimiter should you choose to correctly split the email into username and domain?

A At symbol (@)

B Comma (,)

C Space ( )

D Semicolon (;)

💡 Hint

Look at the character that separates the username and domain in an email address.

The '@' symbol separates the username and domain in an email address, so using '@' as the delimiter splits the email correctly.

🎯 Scenario

advanced

Splitting Dates Stored as Text

You have dates stored as text in column A in the format "2024-06-15" (year-month-day). You want to split these into three columns: year, month, and day.

Which delimiter should you use in Text to Columns to split the date correctly?

A Hyphen (-)

B Slash (/)

C Comma (,)

D Space ( )

💡 Hint

Look at the character that separates year, month, and day in the date format.

The date parts are separated by hyphens, so using hyphen (-) as the delimiter splits the date into year, month, and day.

❓ data_analysis

advanced

Analyzing the Result of Text to Columns on a CSV List

Column A contains the text "apple,banana,orange" in cell A1. You apply Text to Columns using comma as the delimiter.

What will be the values in cells A1, B1, and C1 after the operation?

A A1: apple, B1: banana,orange, C1: (empty)

B A1: apple, B1: (empty), C1: banana,orange

C A1: apple,banana,orange, B1: (empty), C1: (empty)

D A1: apple, B1: banana, C1: orange

💡 Hint

Text to Columns splits text at each comma and places each part in adjacent columns.

The text is split at commas, so "apple" goes to A1, "banana" to B1, and "orange" to C1.

📊 Formula Result

expert

Effect of Text to Columns on a Cell with Multiple Spaces

Cell A1 contains the text "John Michael Smith" with multiple spaces between names.

You apply Text to Columns using space as the delimiter and check the option to treat consecutive delimiters as one.

What will be the values in cells A1, B1, and C1 after the operation?

A A1: John, B1: (empty), C1: Michael Smith

B A1: John, B1: Michael, C1: Smith

C A1: John, B1: , C1: Michael Smith

D A1: John, B1: Michael, C1: (empty)

💡 Hint

Treating consecutive delimiters as one means multiple spaces count as a single separator.

With the option enabled, multiple spaces are treated as one delimiter, so the text splits into three parts: "John", "Michael", and "Smith".