Separating data
The Text to Columns feature in Excel is a wonderful tool to separate data from a single cell into multiple columns. Let’s take a look:
- Open the workbook named Customers.xlsx.
- Notice that the data is bundled in the first column of the workbook. This sometimes happens when data is imported.
- Select Column A, then go to Data | Text to Columns:
Figure 9.61 – Convert Text to Columns Wizard
- Choose the file type that suits your dataset – this dataset is separated by commas, so we will leave the selection set to Delimited. Click Next > to continue.
- Select the delimiter that suits your data on the next screen. For this dataset, we will select Comma. Click Next > to continue:
Figure 9.62 – Select Comma, then Next >
- Notice that the data has been separated in the bottom half of the window. Check that this is what you require.
- On the third screen, select the formats you require for each column of data. Normally, I leave this step and sort it out once I have converted the data into separate columns.
- Click Finish to complete the process and display the data in separate columns on the worksheet.
Summary
You have learned an abundance of skills throughout this chapter. Now, you can confitldy use the new features of Excel 2021, as well as making use of the updates for various tools. Using Advanced Filter should now be a simple task, and manipulating data using the new FILTER function should make your data manipulation efforts more effi t. You also learned more about conditional formatting and about importing, cleaning, joining, and separating data while incorporating some new functions, such as UNIQUE.
In the next chapter, you will learn about the latest functions and more about PivotTables, lookup functions, and date functions.
