Do More With Your Sales Data Using Text-to-Column in Excel

By John Short, Marketer at Yesware

Let’s admit that as salespeople, the data we collect isn’t always perfect. But in trying to make sense of all the pieces of info we gather, Excel’s Text-to-column function is a big help. It helps you sort and categorize data in a way that helps you make sense of the fragmented pieces of seemingly random tidbits you jot down—and fill in some of the gaps.

For example, let’s say a form on your website collects a prospective client’s email, name, phone number, but not their company’s name. Text to column can help you to use the person’s email address to separate out their domain and hopefully lead you to figure out what company they work for. Once you’ve tracked this information down, you can categorize and sort your users to see how many of them come from a specific company. This can help you gauge interest that a company has, among other things.

Getting Started

It might sound complex, but it’s simple to get started with text-to-column. Essentially it’s a process of elimination since you’re separating out contents of a cell by  defining a “delimiter.” If you’re trying to separate out the domain at the end of an email address, the delimiter would be “@”. You can define it as any character, but for this example we are going to use “@”.

Here is an example spreadsheet if you want to try it as we go through the steps. Copy and paste this into Excel)

1)   Copy and Paste the column with the email addresses into a new column that has an empty column to its right, so you can use the text-to-column function.

 2)   Paste the email addresses into a new column (I’ve labeled mine as Temporary Text to Column), then I created a new “Company” column to its right, once we utilize the text-to-column function, the company column will be populated with the domains.

 

3)   Next you should select “Delimited” to assign the Delimiter.

 

4)   Now it’s time to actually select the Delimiters. In our example the delimiter is “@”, so you should select “Other,” as shown in the image above.

 

5)   And the final result should look like my spreadsheet above. Once your company column is populated you can delete the “Temporary Text to Column” section as the data is no longer significant.

Depending on your company/product, you can assume that in most cases the domains in the Company column are the companies that people work for. You will, of course, find people using email addresses like @yahoo.com, @hotmail.com, @gmail.com and @aol.com, in those cases, try putting the email address into a Google search and that may yield a LinkedIn profile which will likely show you where they’re working.

Pivot Table

Back to Excel, once you’ve generated your new data set from text-to-column, you can also create a pivot table and add the “Company” to “Row Labels” and “Email” to the “Values” table. This will show you immediately the number of sign ups you have gotten from each domain, which will make you more informed as you approach them to close the deal.

Read more about how to use pivot tables in Excel to help you generate sweet sales reports here

There are many more applications for the text-to-column function, this is one of the more popular use cases. Here is the sample spreadsheet for you to try it yourself. As always either copy and paste the data into your own spreadsheet, or you can hit File -> Make a Copy, if you want to try to do this in Google Apps.

Have a use for text-to-columns we didn’t mention above? Let us know in the comments. Also feel free to ask questions!