How To Parse Excel Data Like A Pro

Why parse Excel data?

Microsoft Excel has helped business owners make data collection easy. Business plans, employee data, or customer data can be easily organized on Excel spreadsheets.

But sometimes business workflows require Excel data to be transformed or modified and sent to ERPs, CRMs or even Google Sheets.

Cleaning up or parsing such data manually from Excel is often time-consuming and error-prone. And unfortunately, Excel doesn’t integrate with ERPs or CRMs out of the box.

That’s where an Excel parser or Excel data parsing can help.

This guide will walk you through a few methods on how to parse data from Excel spreadsheets. You will also learn how to parse Excel data with Nanonets and automate data workflows from Excel to other business software.

---

Supercharge your Excel data parsing workflows with Nanonets. Automatically modify your Excel data in any way you prefer.

Schedule a Demo

---

How To Parse Data In Excel

Ever cleaned up data on Excel? Data imported into Excel or maintained on spreadsheets can often get cluttered and messy. And not all data is required for every business process.

This is why employees spend a lot of time parsing specific data on Excel to extract relevant data for their business workflows. Common Excel data parsing examples include:

• removing spaces in or between values
• converting text values or numbers
• splitting column values into separate columns
• adding currencies
• reformatting dates, addresses, names etc.

Let’s look at a few intelligent methods of parsing data from Excel that don’t rely on manual copying and pasting:

Excel data parsing by splitting a column into multiple columns

Follow these steps to split the data in one column into one or more columns.

• Open the Excel spreadsheet.
• Highlight the column that you wish to modify.
• Click the “Data” tab in the ribbon >> then click “Data Tools” >> and click “Text to Columns”  to open the “Convert Text to Columns Wizard”.
• Select “Delimited” and click “Next”.
• ​​Select the ‘Delimiter’ of your choice and click “Next”.

Check the box next to “Treat consecutive delimiters as one” and click “Next”.

• Select “General” in the “Column data format”.
• Click the red spreadsheet icon in the “Destination” text box.
• Select the column(s) you wish to populate with the split data.
• Click the red arrow/spreadsheet icon to return to the wizard >> and Click “Finish”.

Excel data parsing with Microsoft Excel string functions

Traditionally most parsing activities on Excel have been handled using string functions such as RIGHT(), LEFT(), MID(), FIND() and LEN().

These functions are used to determine and specify a position for each character in a string. This is then used to delimit data within larger strings.

Although pretty complex to work with, these functions are handy when trying to clean up or split data. Here’s a detailed guide from Microsoft on how to use these functions to manipulate strings of text or numbers.

Flash fill to parse data in Excel

Excel’s flash fill feature allows it to evaluate a parsing pattern based on certain examples, and then parsing the entire data accordingly.

Let’s use Flash Fill to parse the zip codes from addresses listed in a single column A.

• Enter just the zip codes for the first 2 cells in column B.
• Press Ctrl + Enter
• OR select the entire column B >> click Data >> and click Flash-fill

Excel should now pick up the dat parsing pattern you wish to implement, and accordingly auto-fill cells in column B.

This is a huge improvement from string functions and is pretty straightforward to use. Note that this might not help in cases of complicated data transformation.

---

Supercharge your Excel data parsing workflows with Nanonets. Automatically modify your Excel data in any way you prefer.

Schedule a Demo

---

Automated Excel data parsing with Nanonets

While all the methods discussed previously work pretty well on individual documents. There’s no way to completely automate these methods across thousands of Excel spreadsheets.

You would still need to manually key in the steps for splitting columns, or using string function or even flash fill.

With Nanonets, you can build a completely touchless automated Excel data parsing workflow. You can collect Excel files coming from multiple sources (email, cloud storage, desktop etc.), parse the data you need, and finally send it to any business software of your choice.

Here are the steps:

Create an account

Sign up to get started with Nanonets.

Build a custom data parser for Excel

Upload a few sample Excel files and mark the data or fields that you want to extract and train your parser. The AI will learn to identify similar data based on the examples you show – the more the better.

The Nanonets email parser can be used to extract info from all kinds of emails – e.g. exporting Outlook emails to Excel or extracting data from email attachments.

Define the source(s)

If you receive Excel spreadsheets via email, set up a Nanonets receiving address and auto-forward all relevant emails to that email id.

You can also set up an automatic import of files from cloud storage, databases or connect to other sources via API.

Set up the workflow

Create the entire workflow by connecting the source to the custom Excel data extractor you trained. You can also define parsing rules to process the extracted data into appropriate output formats.

Finally define the destination that you would like to export the refined data into. You can export the data directly into Google Sheets, CRM or ERP software via Nanonets’ API.

Nanonets can also be connected with any app of your choice via Zapier.

Takeaway

Data parsing is crucial for business workflows that need to transform Excel data and ingest them into CRMs or ERPs.

The techniques we’ve covered in this article can help you save a lot of time that would otherwise be spent in manual tiresome copy pasting. And Nanonets’ automated data parser can help completely automate Excel data transformation workflows.

This will allow employees to focus on higher value tasks.