Excel - Parsing Variable Length - Episode 1043

3,280 views · Published 24 June 2009 · 2:33 · Indexed 31 October 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Parsing Variable Length Data.

Welcome back to the MrExcel netcast, where we dive into all things Excel. In today's episode, we're tackling a common issue faced by many Excel users - parsing variable length data. We often find ourselves with massive amounts of data and need to figure out how to analyze it efficiently. Well, fear not, because we have a solution for you - pivot tables.

In this episode, we'll be addressing a question sent in by Don via Facebook. Don has a file with company names and addresses in two separate columns. However, there is no delimiter and the company names are of varying lengths. This can be a frustrating issue, especially when trying to use the text to columns function. But don't worry, we have a workaround that will make your life easier.

First, we'll add a new column and call it "address only". Then, we'll use the MID function to extract the address from the company name column. But here's the tricky part - we need to specify the starting point for the MID function, which will be different for each company name. To do this, we'll use the LENGTH function on the company name in column A and add 2 to account for the space between the company name and address. Then, we'll use a large number for the number of characters to ensure we get the entire address. And just like that, we have successfully parsed the variable length data.

To make things even easier, we can double click the fill handle to apply the formula to the rest of the column. And voila, we now have a column with just the addresses. This is just one example of how the MID function can be used in a creative way to solve a common Excel problem. Thank you for tuning in to this episode of the MrExcel netcast. Don't forget to like, comment, and subscribe for more Excel tips and tricks. See you next time!

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/


Dawn's file has name in A and address in B. Except: the address field in column B contains the name again. Episode 1043 shows how to parse the variable-length name out from column B.

Table of Contents:
(00:00) Introduction to the MrExcel netcast with Bill Jelen
(00:14) Today's question from Don via Facebook
(00:24) Parsing company names from addresses in Excel
(01:02) Using the MID function to extract data from varying locations
(01:55) Double-clicking the fill handle to apply the formula to multiple cells
(02:05) 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:
Annoying data formatting issue in Excel: Company names and addresses in one column
Efficient method for extracting addresses from mixed data in Excel
Excel functions for extracting text: MID, LEN
Excel netcast tutorial: Extracting address data from mixed format
Excel tutorial: Removing company names from addresses using text functions
How to parse company names from addresses in Excel
Resolving data formatting issues in Excel: Splitting company names and addresses
Solving the problem of removing company names from addresses in Excel
Tips for manipulating data in Excel: Removing company names from addresses
Using text functions to manipulate data in Excel: MID, LEN, text to columns
Using the MID function to extract text in Excel
YouTube video tutorial on using pivot tables in Excel


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

More from this channel