Look Who it Was Random video

Excel - Removing Names from a Dataset | 5 Powerful Formula Variations - Episode 1097

827 views · Published 8 September 2009 · 2:42 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Removing Names from a Dataset | 5 Powerful Formula Variations.

Welcome back to another episode of the MrExcel netcast. In this video, we will be discussing different ways to remove names from a dataset in Excel. As we all know, working with large amounts of data can be overwhelming, but with the help of pivot tables, we can easily analyze and manipulate our data. So let's dive in and see if you can solve this problem.

In episode 1043, I shared a formula to remove names from a dataset using the MID and LEN functions. However, a member of the MrExcel message board, with the username "if shift+delete", shared four alternative formula variations that caught my attention. In this video, we will be exploring these variations and how they can be used to efficiently remove names from a dataset.

The first variation uses the SUBSTITUTE function. This function allows us to find and replace specific text within a cell. By using the SUBSTITUTE function, we can easily remove the name from column B by replacing it with an empty string. However, this method may result in a leading space, which can be solved by using the TRIM function or by adding an extra space in the SUBSTITUTE function.

Another variation is the REPLACE function, which replaces a specific part of a text string with another text. In this case, we can use the REPLACE function to replace the name in column B with an empty string, starting from the first character and going to the length of the name in column A. This effectively removes the name from the cell.

The third variation uses the RIGHT function, which returns a specified number of characters from the end of a text string. By using the RIGHT function, we can determine the number of characters to remove by subtracting the length of the name in column A from the length of the text in column B. This may seem like a convoluted method, but it is another effective way to remove names from a dataset.

As always, there are multiple ways to solve a problem in Excel, and in this video, we have explored five different functions that can be used to remove names from a dataset. I want to give a special thanks to "if shift+delete" for sharing these variations with us. If you send me your postal address, I will send you one of my Excel Master pins as a token of appreciation. And to all our viewers, thank you for stopping by. Don't forget to subscribe to our channel for more Excel tips and tricks. See you in the next netcast from MrExcel.

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/


Table of Contents:
(00:00) Using MID and LEN functions to extract data 
(00:33) Cool formula variations from MrExcel message board 
(00:45) Using SUBSTITUTE function 
(01:00) Solving leading space issue with TRIM function 
(01:17) Using SUBSTITUTE function with additional criteria 
(01:35) Using REPLACE function 
(01:49) Using RIGHT function 
(02:05) Multiple ways to solve problems in Excel 
(02:17) 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:
Analyze data
Excel functions
Excel master pins
Excel problem-solving
Excel tips
Formula variations
Pivot table
REPLACE function
RIGHT function
SUBSTITUTE function
TRIM function


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


Shift-Del sends in some alternative formulas to solve the problem from episode 1043. To see uses of SUBSTITUTE and REPLACE in Excel, check out Episode 1097.

More from this channel