I discussed how to parse data using various functions in an earlier post. This time, I’ll demonstrate to you how much simpler Power Query makes it to accomplish that. This will greatly simplify your life if you are uncomfortable using the LEN or MID functions. The data set I used in my previous post, which you can download from here, will be used again in order to keep things simple.
How to Parse Data in Excel Using Various Functions
Why learn how to parse data in Excel?
Learning how to parse data in Excel has several benefits. The first is that it can assist you in more effectively organizing your data for presentations and use by you or other data specialists. This includes separating out information that is crucial for meetings and sending it to other experts as needed. The ability to use Excel and computers more proficiently is a further advantage of learning to parse data in Excel. Developing these abilities can make you more productive at work by enabling you to complete tasks more quickly. The ability to convert data into a wide range of formats is another benefit of parsing data.
The biggest advantage is being able to format large amounts of data by parsing it in Excel. For instance, it takes a very long time to convert 1,000 or more rows of data to a particular format one at a time. All of your data can be changed in format simultaneously, saving you time and preventing mistakes brought on by fatigue and repeated sessions of changes.
What is parsing data?
Data parsing involves converting a string of data into a different format. By splitting a single string of data into two or more strings using a set of rules, people most frequently parse data. Other ways that you can parse data include:
How to parse data in Excel
You can use the steps listed below to separate data in an Excel spreadsheet:
1. Insert your data into an Excel spreadsheet
Entering your data into an Excel spreadsheet is the first step in parsing it. Professionals typically enter their data in the sheet’s organized columns and rows. This is due to the fact that manipulating these formats within the program is simpler than if the data were spread across various other locations. In order to select all of your data at once when data parsing, it’s critical to organize the data you want to parse into a column. An illustration of how to enter data into an Excel column is provided below:
A**1Smith, Jamie2Cooper, John3Richardson, Erik4**Brynhild, Ragnar### 2. Choose the column containing the information you want to parse.
Select the column where you stored the data once you’ve entered the data you want to parse. You can do this in two ways. The first technique involves moving your mouse cursor over the top cell of the data you want to select until it turns into a “+” sign, then clicking and dragging it down until all of the cells are highlighted. If the data you are using is unlikely to change and the entire data set is relatively small, this method can be helpful. You can begin highlighting in the cell that says “Smith, Jamie” using the information above. “.
The second approach involves clicking the letter at the column’s top. This enables you to choose every cell in the column, whether or not they contain data. This method applies the formatting you select to the entire column and affects new data as you add it, making it useful if you have a lot of data you want to parse or if more data will be added to the dataset. You can choose the letter “A” to select the entire column using the aforementioned example data.
3. Choose the “Data” tab in the ribbon
All of your Excel tools are located in the section at the top of the Excel program called the ribbon. By default, the program starts on the “Home” tab. The tabs at the top of the ribbon are listed in this order:
Use your mouse to click on the data tab to select it.
4. Click the “Text to Columns” option
Once you’ve chosen the “Data” tab, navigate to the “Data Tools” section and choose the first choice, “Text to Columns.” This option opens a dialog box with formatting options based on various features in your data that you can choose from. Additionally, this is the quickest method for parsing data in Excel. In conclusion, this technique enables you to format your existing data strings into two or more strings that apply to additional empty columns in your spreadsheet, which is helpful for data organization. Additionally, by choosing this option, Excel will format the data as you prefer.
5. Select “Delimited” in the dialog box
You can separate your data using the “Delimited” option by using a delimiter. Spaces and commas are the two most popular delimiters in Excel. For instance, the comma is a delimiter in your data if it separates your data, as in “Carter, Logan.” Another illustration is when spaces are used to delimit data, as in the case of “Logan Carter.” In some Excel spreadsheets, the pipe symbol “|” is used as a delimiter to divide items inside of a single cell.
6. Choose a delimiter that exists within your data
You can select the delimiter you want to use for your data once you’ve clicked the “Delimited” option in the dialog box. The delimiter can change from one data set to another. The data set below, for instance, uses commas as the delimiter:
The following data set uses spaces as the delimiter: A1Smith, Jamie2Cooper, John3Richardson, Erik4, Brynhild, Ragnar
Finally, this data set uses pipes as the delimiter: A1Carder Sauk2Katie Richardson3Blair Smith4Amanda McLaurin
A10001, Baker, Ryan, 20002, Cash, Emily, Daniels, Doretha, 4003, Edwards, Veronica, ### 7, and Baker, Ryan. Check the box next to “Treat consecutive delimiters as one”.
You can more easily parse data with the help of this dialog box option. It enables Excel to recognize that multiple delimiters placed close together only count as one, assisting in the transfer of the desired data into the appropriate columns. For instance, if the delimiters in your data are pipes and spaces, your spreadsheet will treat them as a single delimiter and only move the values on either side of the pipes to new cells. Similar to this, Excel treats two pipes next to each other as a single delimiter, enabling accurate data transfer.
8. Choose “General” from the “Column data format” section
Changing the format of your data is possible by opening this new section of the dialog. This is a crucial step because it gives you complete control over where exactly your data is transferred within the spreadsheet, how it is parsed, and how the new data columns are formatted. This gives you the most control over the entire data movement process and enables you to experiment with different data formats until it takes the specific form you want or need for your work, presentations, or submissions to other professionals for data analysis.
9. Click the red arrow or spreadsheet symbol at the far right of the destination text box
Following Excel’s data parsing, you can choose where to store your data in this step. You can access your spreadsheet and find an empty column to input your data by clicking the red arrow or spreadsheet button. You can also access additional options in your spreadsheet and alter the appearance of the data there. For instance, you could move information from columns B and C to fit your parsed data in this step.
10. Select the columns where you want the parsed data to be displayed
You choose where to store your newly parsed data in this step. Professionals typically do this by storing their data in the columns that follow the non-parsed data. For instance, if you previously stored your data in column A, you can now store the newly parsed data in columns B and C. Make sure you choose the number of columns that corresponds to the amount of space you require. If there is only one delimiter in your data, two columns will fit it. If it has two delimiters, three columns fit.
11. Return to the first dialog box and select “Finish”
Return to the dialog box and click “Finish” at the bottom to finish parsing your data. When you do, Excel transforms your data in accordance with the guidelines and specifications you specified in the dialog box. Check your data after Excel has finished processing it to make sure it was formatted correctly and parsed where you wanted it to be. This will assist you in ensuring that your data is accurate and free of any difficult parsing errors the program might have made. You can save your Excel spreadsheet once it has been properly formatted by Excel.
Below is an example of parsed data:
A
B
C
1
Smith, JamieSmithJamie2
Cooper, JohnCooperJohn3
Richardson, ErikRichardsonErik4
Brynhild, RagnarBrynhildRagnar### 12. Save your Excel spreadsheet
You can save your data in Excel in three main ways. The first is by selecting the “File” tab from the program’s ribbon’s top. There is a “Save as” option in that section that lets you choose the location and name of the file on your computer. The second choice is to choose the “Save” button located on the “File” tab. Doing so will instantly save your file to a location on your computer and give it a name. The last option is to press “Ctrl+S” on your keyboard.
Regardless of the method you select, saving your file will help you keep your data for later use, send it to other professionals as a file, and make sure you don’t repeat the steps you took to parse your data when you already made the changes. Additionally, by taking this step, you ensure that you have backup files in case a problem arises and you need to access a previous version of your Excel spreadsheet.
Please be aware that Indeed is not connected to any of the brands or businesses mentioned in this article.
FAQ
What does parse data mean in Excel?
Parsing data means you break it down into separate components. You might divide a column of full names into a column for first names and a column for surnames, for instance. There are various methods for removing data from Excel and sending it elsewhere.
Where is parse in Excel?
To access it, select Microsoft Office from the Windows menu and then click Microsoft Excel. Open the file that contains the data. The file can be found by clicking the rounded Windows icon in the top-left corner of Excel, choosing Open, and then choosing the desired file. Click the header of the column you want to parse.
How do I parse a Text file in Excel?
- Click the Data tab in the Excel spreadsheet you want to save the data to.
- In the Get External Data group, click From Text.
- Click Import after choosing the TXT or CSV file you wish to convert.
- Select “Delimited”. …
- Click Next.
How do you separate data in Excel?
Click Text to Columns under the Data Tools section of the Data tab. The Convert Text to Columns Wizard opens. If it is not already selected, select Delimited, then click Next. To specify the locations where you want to divide the content of the cell, choose a delimiter or delimiters.
