Look Who it Was Random video

Excel - Data Parsing in Excel | Solving Formatting Issues & Handling Large Datasets - Episode 782

715 views · Published 21 January 2009 · 3:23 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Data Parsing in Excel | Solving Formatting Issues & Handling Large Datasets.

Welcome back to the MrExcel netcast! In today's video, we'll be tackling a question from Hamilton about parsing data with leader lines in Excel. Hamilton sent in a text file with a description, a P/N (part number), and leader lines in between. While Word can handle this, it's causing some issues in Excel. So, let's dive in and find a solution!

The first approach we tried was using the "Text to Columns" feature and setting the delimiter as a period. However, since the number of periods in each row is different, the part numbers ended up in different columns, making it a messy situation. Next, we tried changing the font to a fixed width font, but unfortunately, the data still didn't line up perfectly.

Another option in the "Text to Columns" feature is to treat consecutive delimiters as one. This would group all the leader lines together as one delimiter, but it still didn't work perfectly due to periods in the product descriptions. So, we decided to take a different approach.

We changed the delimiter to a colon and used the "Text to Columns" feature again. This time, the part numbers were in the correct column, but we still had issues in the description column. To fix this, we used the "Replace" function to remove the extra periods and the "P/N" text. While it may seem like a hassle to go through multiple steps, it's much easier than dealing with thousands of rows manually.

In the end, we were able to successfully parse the data and get the desired result. I hope this solution helps Hamilton and anyone else facing a similar issue. Thanks for watching this netcast from MrExcel. Don't forget to like, comment, and subscribe for more Excel tips and tricks. See you in the next video!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Hamilton sends in a question about parsing data that has leader......lines between the columns. This ends up being trickier than you might think. Episode 782 shows you how I approached the problem.

Table of Contents:
(00:00) Introduction
(00:21) Leader lines and dots
(00:31) Using DATA, TEXT TO COLUMNS
(00:41) Problem with different number of periods
(00:57) Attempting to use fixed width font
(01:12) Using DELIMITED and TREAT CONSECUTIVE DELIMITERS AS ONE
(01:33) Issues with periods in product descriptions
(01:44) Solution using colon as delimiter
(02:01) Final result
(02:35) Explanation of steps taken
(03:00) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Avoiding errors in Excel data parsing
Efficient data parsing techniques in Excel
Excel data parsing problem
Excel tips for working with huge amounts of data
Fixing formatting issues in Excel
Handling large datasets in Excel
How to use TEXT TO COLUMNS in Excel
MrExcel netcast on data parsing techniques
Removing unwanted characters in Excel using REPLACE
Solving text parsing issues in Excel
Using delimiters in Excel TEXT TO COLUMNS
YouTube video on parsing text in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152097/

More from this channel