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".

Practice

1. What does the Text to Columns feature in Excel do?

easy

A. Splits text in one cell into multiple cells based on a separator

B. Combines multiple cells into one cell

C. Changes the font size of text in a cell

D. Sorts data alphabetically

Solution

1. Step 1: Understand the purpose of Text to Columns

   This feature is used to split text that is combined in one cell into separate cells.

2. Step 2: Identify the correct description

   Among the options, only splitting text based on a separator matches the feature's function.

3. Final Answer:

   Splits text in one cell into multiple cells based on a separator -> Option A

4. Quick Check:

   Text to Columns = Splitting text [OK]

Hint: Remember: Text to Columns splits, not combines [OK]

Common Mistakes:

• Thinking it combines cells instead of splitting
• Confusing it with sorting or formatting features
• Assuming it changes text style

2. Which of these is the correct first step to use Text to Columns on a cell with data separated by commas?

easy

A. Select the cell, right-click and choose Format Cells

B. Select the cell, go to Data tab, click Text to Columns, choose Delimited, then select Comma

C. Select the cell, press Ctrl+C, then paste special

D. Select the cell, then click Sort A to Z

Solution

1. Step 1: Identify the correct menu path

   Text to Columns is found under the Data tab and starts with selecting the cell and clicking Text to Columns.

2. Step 2: Choose the correct options for comma-separated data

   Choosing Delimited and then selecting Comma as the separator is the correct procedure.

3. Final Answer:

   Select the cell, go to Data tab, click Text to Columns, choose Delimited, then select Comma -> Option B

4. Quick Check:

   Data tab > Text to Columns > Delimited > Comma [OK]

Hint: Always pick Delimited for separators like commas [OK]

Common Mistakes:

• Skipping the Data tab and looking in wrong menus
• Choosing Fixed width instead of Delimited
• Not selecting the correct delimiter

3. You have a cell with the text "John;Doe;35;New York". Using Text to Columns with semicolon as delimiter, what will be the content of the third cell after splitting?

medium

A. 35

B. Doe

C. John

D. New York

Solution

1. Step 1: Split the text by semicolon delimiter

   The text splits into four parts: "John", "Doe", "35", and "New York".

2. Step 2: Identify the third part after splitting

   The third part is "35" which will be placed in the third cell.

3. Final Answer:

   35 -> Option A

4. Quick Check:

   Third split part = 35 [OK]

Hint: Count parts after splitting; third part is third cell [OK]

Common Mistakes:

• Confusing the order of split parts
• Using wrong delimiter
• Assuming spaces affect splitting

4. You tried to split a cell with data "apple orange banana" using Text to Columns but selected comma as delimiter. What is the result?

medium

A. Excel shows an error message

B. The text splits into three cells: apple, orange, banana

C. The text splits into two cells: apple orange, banana

D. The entire text stays in one cell

Solution

1. Step 1: Understand the delimiter effect

   Since the text uses spaces but the delimiter chosen is comma, no splitting occurs.

2. Step 2: Predict the result of incorrect delimiter choice

   Text remains in one cell because no commas exist to split on.

3. Final Answer:

   The entire text stays in one cell -> Option D

4. Quick Check:

   Wrong delimiter = no split [OK]

Hint: Match delimiter to actual separator in text [OK]

Common Mistakes:

• Expecting split without correct delimiter
• Thinking Excel auto-detects delimiter
• Assuming error shows for wrong delimiter

5. You have a list of full names in one column like "Anna Smith", "Bob Lee", and want to split first and last names into two columns. Which steps correctly achieve this using Text to Columns?

hard

A. Select the column, Data tab, Text to Columns, choose Fixed width, set break after first name

B. Select the column, Home tab, click Merge & Center

C. Select the column, Data tab, Text to Columns, choose Delimited, select Space as delimiter

D. Select the column, Insert tab, click Table

Solution

1. Step 1: Choose the correct splitting method for space-separated names

   Since names are separated by spaces, Delimited with Space delimiter is appropriate.

2. Step 2: Apply Text to Columns with Space delimiter

   This splits first and last names into separate columns correctly.

3. Final Answer:

   Select the column, Data tab, Text to Columns, choose Delimited, select Space as delimiter -> Option C

4. Quick Check:

   Space delimiter splits first and last names [OK]

Hint: Use Delimited with space for splitting names [OK]

Common Mistakes:

• Using Fixed width which is harder to set correctly
• Trying to merge cells instead of splitting
• Confusing Insert Table with splitting text