In the realm of research, data is king. However, the way this data is presented and shared can significantly impact its accessibility and utility. While many researchers publish their findings in the form of text on a web page or in a .txt file, to make use of it you’ll need to convert this text into a table, a more organised and analysable format, such as using Microsoft Excel. If you’ve tried just copying and pasting, you’ll just get an unusable column of text in your spreadsheet.
In this blog post, I delve into why research data gets published in text, why Excel is a preferred format for analysis, and how to easily transform text-based data into a usable spreadsheet format using Excel’s ‘Text to Columns’ tool. I embed my recent video explainer below, but otherwise read on for more…
Why Does Data Get Published as Text?
There are several reasons why researchers and others publish their data as text on websites and in text files.
The biggest reason is file size. When publishing data in Excel or similar, the file size can be dozens of megabytes or bigger. This causes headaches (even in modern times) in terms of uploading to websites, maintaining websites, or for the end user downloading it from a website. Storage space and transfer speeds are therefore key factors. Conversely, text files are relatively tiny, normally no more than 50 kilobytes; a fraction of the size.
So size matters (that’s what she said!). But there’s some other important reasons too…
- Universal Compatibility: Text-based formats like plain text or CSV (Comma-Separated Values) files are universally compatible across different platforms and software. They are lightweight and can be easily opened with basic text editors, making them accessible to a wide range of users regardless of the software they have installed.
- Ease of Publication: When researchers publish their findings, they often opt for text-based formats because they are straightforward to create and can be easily incorporated into academic papers, reports, or online publications. Text formats also ensure that the data remains intact and readable across different mediums.
- Flexibility: Textual data allows for flexibility in terms of content and structure. Researchers can include descriptive narratives, annotations, or other contextual information alongside the data, providing additional insights that may not be easily conveyed through structured formats like Excel.
The text however is practically unusable in a text string format. That’s why it get’s published with ‘delimiters’, to allow for conversion into an analysable format. But what is a delimiter exactly?
A delimiter is a character or sequence of characters used to separate and distinguish individual pieces of data within a larger data set or string. Delimiters are commonly employed in various contexts, such as text files, databases, and programming languages. They exist to structure and organise data for easier processing and manipulation. Common examples of delimiters include commas (,), tabs (\t), spaces ( ), semicolons (;), and pipes (|).
The Appeal of Excel to Liberate the Data
Excel is the world’s most popular tool to liberate and make use of such data, for several good reasons:
- Organised Structure: Excel offers a structured layout with rows and columns, making it ideal for organising and analysing data in a tabular format. This structure allows researchers to easily manipulate and visualise data, perform calculations, and generate charts or graphs for better interpretation.
- Data Analysis Capabilities: Excel provides a wide range of built-in functions and tools for data analysis, such as sorting, filtering, and pivot tables. These features make it easier to derive meaningful insights from data, efficiently identifying patterns or trends.
- Integration with Other Tools: Excel seamlessly integrates with other data analysis and visualisation tools and business intelligence platforms. This interoperability enhances the versatility of Excel as a tool for research and data management.
Now let me quickly show you exactly how to get your text data into a usable Excel format (or view embedded video above), so you can liberate your text string data…
Transforming Text to Excel Table, Using ‘Text to Columns’
Converting text-based data into a table, a usable Excel format, can be achieved with the ‘Text to Columns’ tool. This is a powerful Excel feature allowing you to split text strings into separate columns based on a delimiter (e.g., comma, space, tab).
Here’s a brief overview of how to use the Excel ‘text to columns’ tool in simple steps:
1. Open Excel: Launch Microsoft Excel and create a new workbook or open an existing one containing the text-based data you wish to convert.
2. Select Source Data: Highlight the text-based data you want to transform into Excel format. This could be a single column or multiple columns of data separated by a delimiter.
3. Copy and Paste: Copy your source data and paste it (as values only) into your Excel spreadsheet.
4. Access Text to Columns: With your Excel data still all selected, navigate to the ‘Data’ tab on the Excel ribbon. Locate the ‘Text to Columns’ button within the ‘Data Tools’ group. Click on it to open the ‘Convert Text to Columns Wizard’, shown below.
5. Choose Delimiter: In the first step of the wizard, select the type of delimiter that separates your data (e.g., comma, space, tab). You can also specify additional options such as text qualifier or treat consecutive delimiters as one.
6. Preview and Adjust: Excel will display a preview of how your data will be split into columns based on the chosen delimiter. Review the preview and make any necessary adjustments to ensure the data is parsed correctly.
7. Specify Data Format: In the final step, you can see how the table looks and specify the format of each column (e.g., General, Text, Date) and designate the destination cell where the converted data should be placed.
8. Complete the Wizard: Once you’re satisfied with the settings, click ‘Finish’ to execute the conversion process. Excel will split the text-based data into separate columns based on the specified delimiter, transforming it into a structured Excel format that is ready for analysis.
The full potential of your data is now unlocked, allowing further exploration and insights. Feel free to take a look at my ‘Excel at Excel’ series on YouTube for some tips on analysing and presenting your data.
I hope you found this blog on turning data text into a table helpful. I do this blog at my own expense around a day job, so please consider supporting my work by buying me a coffee if you’d like to see more.
Kind Regards, Adrian
I hope this guidance is supportive and saves some MS Office frustrations! Subscribe to my blog and EATO YouTube channel for more useful info. Liking the content? Please like and share with friends and colleagues who may also find it useful. If so inclined, supporting my work helps keep free content flowing. Want fantastic, time-saving templates or dashboards tailored to you and your business? Then please get in touch to arrange a bespoke quote. Finally, please feel free to leave your feedback below, including what tutorials or templates you’d like created.