Unlock the Power of Your Data: A Step-by-Step Guide to Parsing in Excel

Do you ever feel overwhelmed by massive chunks of data? Do sheets full of numbers, letters, and symbols leave you confused about how to make sense of it all?

You’re not alone. Many of us get anxious when faced with raw unstructured data.

But here’s the good news: with the right tools, you can easily parse even the most chaotic data sets. Parsing data simply means converting it from one format into another that’s easier to work with.

In this article, I’ll walk you through step-by-step how to parse data in Excel. You’ll learn

• What parsing is, and when you need it
• How to properly structure your data for parsing
• Easy steps for parsing data using Excel’s built-in “Text to Columns” tool
• Advanced parsing options for complex data sets
• Tips to parse data even faster in the future

Let’s get started unleashing the potential in your data!

What is Data Parsing, Anyway?

Data parsing refers to splitting up raw data from its original format into something more organized and structured.

For example, let’s say you copy a chunk of text from a PDF file and paste it into Excel. Without parsing, that text remains one long string in a single cell.

Parsing allows you to split it into separate columns and rows. This makes the data easier to filter, sort, analyze, and visualize.

You’re essentially converting messy, unstructured data into a tidy, organized table.

When Should You Parse Data in Excel?

Here are some common scenarios when parsing data in Excel can save the day:

• Importing data from another source – You often need to parse data copied from another program into a structured Excel table. This includes data from PDFs, CSV files, databases, and other sources.

• Cleaning up messy Excel data – Over time, even data created in Excel can become disorganized and difficult to work with. Parsing it can reset your data back to a pristine structured layout.

• Splitting up columns – Parsing allows you to quickly split data from a single column into multiple columns (e.g. splitting up full names into separate first and last name columns).

• Splitting up rows – You can also parse data from a single row into multiple rows (e.g. turning a row of comma-separated values into separate rows).

• Adding or removing delimiters – Parsing gives you control over the delimiters (characters used to separate data, like commas or tabs) used in your raw data.

The key is that parsing enables you to mold and reshape your data into the optimal structure for your needs.

Step 1: Get Your Data into Excel

Before you can parse your data, you need to get it into a basic Excel spreadsheet first. Here are some ways to input data:

• Enter data manually – For small data sets, you can simply type or copy/paste your raw data into Excel cells.

• Import from external files – Use ‘Get & Transform Data’ (Excel 2016+) or the legacy ‘From Text’ option to import CSV, TXT, PDF and other files.

• Connect to a database – Pull data from a database like SQL Server or Access directly into an Excel sheet to parse.

• Extract data from online sources – Use the ‘From Web’ option to parse tables and data from webpages into Excel.

The key is that all your raw unstructured data lands in one place on a spreadsheet. Avoid having data split up across multiple sheets or files at this stage.

Step 2: Set Up Your Data Correctly

Before parsing your data, set it up in the optimal structure to make your job easier:

• Put data you want parsed into a single column – Don’t have your raw data you want parsed split into multiple columns. Consolidate it all into one column first.

• Remove blank rows/columns – Take out any empty rows or columns in your data, leaving only the raw data you want parsed.

• Clear formatting – Select the column with your raw data and clear any formatting like colors, fonts, borders, etc. You want plain text.

• Adjust column width – Make sure the column with your raw data is wide enough to show the full length of the text strings.

Taking this preparatory step hugely simplifies parsing and ensures you don’t miss or overwrite any of the original data.

Step 3: Select the Data You Want To Parse

Next, select the column in your spreadsheet that contains the raw data you want to parse.

To select the full column:

• Click the column letter at the top to highlight the entire column.

To select part of a column:

• Click and drag your mouse over just the cells you want parsed.

Only the selected cells will be parsed in the next steps.

Step 4: Launch the Text to Columns Wizard

Here’s where the magic happens!

Go to the Data tab in Excel’s ribbon and click the ‘Text to Columns’ button. This launches the Text to Columns wizard.

Alternatively, you can launch it by going to Data => Text to Columns.

This wizard will walk you through each step of the parsing process.

Step 5: Select Delimited Data

In step 1 of the wizard, you need to select the original data format.

Choose ‘Delimited’ if your data has delimiters like commas, spaces, tabs, etc separating each value into its own section. Delimited is by far the most common scenario.

You can also choose ‘Fixed width’ if your data has fixed spacing, but this is less common.

Hit Next to proceed.

Step 6: Set Your Delimiter

Delimiters are the characters that divide each data value in your raw data.

Common delimiters include:

• Commas – e.g. red,green,blue
• Spaces – e.g. red green blue
• Tabs – e.g. red green blue
• Other characters like | / ; : etc.

In step 2 of the wizard, inspect your data and select the delimiter character that exists between each data value.

If you have a combination of delimiters (e.g. mixture of commas and tabs), choose the most common one. We can clean up the rest later.

Hit Next once your delimiter is set.

Step 7: Set Data Format for Each Column

Finally, in step 3 of the wizard you get to set the data format for each parsed column.

For each column, choose options like:

• General – For simple numbers or text.
• Text – For columns with only text strings.
• Date – For date/time columns.
• Other formats – Like numbers with fixed decimals, currency, etc.

Aim to choose the most restrictive format possible that matches your actual data. This prevents future data corruption.

Check the preview to ensure your columns and delimiters look correct. Then hit Finish.

Step 8: Check Your Parsed Data

That’s it – your raw data is now neatly parsed into a structured table!

Before proceeding, always double check that:

• The desired columns were created.
• The delimiter separated the data properly.
• Headers and text are readable.
• The correct format was applied per column.

Look for any weird formatting issues or data that seems out of place, and fix as needed.

If any mistakes are found, just re-run the Text to Columns steps above.

Advanced Tactics for Tricky Data

The basic Text to Columns wizard can parse most clean data sets.

But sometimes you’ll encounter really complex messy data with multiple nested delimiters and inconsistent formats.

Here are some tips for handling trickier parsing jobs:

Use multiple delimiter selection – If needed, you can choose multiple delimiting characters like commas and tabs. Just be careful to not split data incorrectly.

Parse in stages – Break the parsing down into multiple steps. Parse the top level first, inspect the results, then parse again as needed to finely separate the data.

Fix faulty delimiters manually – If some of the parsing is still incorrect, manually edit delimiters that are wrong or missing.

Further clean your data – Use Find & Replace, formulas, or Power Query to further refine your parsed data if needed.

Split problem columns before parsing – For a badly formatted column, split it into multiple columns first, then parse each new column separately.

While multiple parsing rounds may be needed for really messy data, the end result is clean, beautiful data!

Parse Data Even Faster in the Future

The first time parsing data in Excel can be time-consuming since you’re learning the process.

But once you get the general gist, you can parse data extremely quickly thanks to some handy tips:

• Record your steps as a Macro – When parsing a worksheet, click the Record Macro button first to save all steps as a reusable macro.
• **Use