Clean Up Names in a List

Beginner

About this Tutorial

In this video, I will show you how to clean up a list of names that may have extra white spaces using a formula. I will demonstrate how to identify and remove the extra spaces programmatically, making the list of names unique. By using the TRIM formula, we can easily clean up the names and create a new list with only the unique names. No action is required from you, just watch and learn!

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

TRIM() UNIQUE()

Video Transcript

<div>0:00 So typically when we have a list of names and we want to create a list of Unique names like we have many names and we could get these names sort of by many means maybe Google Form insert of these names or We enter these ourselves we type them in and sometimes the list becomes a little bit what I call 0:18 sort of dirty We need to clean up these names what I mean by that is that there are some problems with these names which in this particular case is a white space extra white space how do we do that sort of quote unquote programmatically or with a formula and let me show you sort of this problem we can 0:35 go equals Unique we'll use range B.

And right away we see that these are not unique these are names these Samson Samson and Andrew and Andrew are the same name so why are they up here in a unique list together it's because at the end of the R or the end of the name there is. 1:00 Is an errant white space and in this case of this one errant there is no white space so to the unique function they are actually different and in order to change this we need to find out you know that there's a something wrong with this we. 1:20 Delete it and there we go we've cleaned it up but there's another one down here somewhere and I don't know where right and we could you know there's sort of some weird ways we could do this we could do Len which is the length of these so we can do B3 and sort of find out that we can copy paste this  1:39 all the way down and we.

Find out look over there's 14 this has the errand space but that's that's not very nice we don't want to do that. 1:47 That'll take a long time we have to compare numbers and stuff what we have available to us is the formula trim equals TRIM and here let's put that space back and do equals TRIM. 2:01 And do B3 here and now we're going to copy paste this formula all the way down and you can see this unique did not change because we're not changing the original entrant here. 2:17 We have changed however the actual data .

Here in the clean names so we could do is create a unique list again actually use the same unique list but instead of B3 we will use C3 colon C. 2:34 And now we have only three unique names because we have trimmed the white space. So let's look at the trim help here and see that anytime we have some extra spaces leading spaces trailing spaces and repeated spaces in text it's going to trim those off. 3:01 Let's let's see what else we can do. We can add some spaces in front and see now we have a clean name. 3:08 Let's see if we have that middle spaces.

We have extra spaces there, two or three spaces there. It also cleans that. 3:17 So this cleans off all white spaces that are repeated and are leading and also at the trailing. So even if inside here we did spaces, it all comes down to just the spaces. 3:31 The amount of space we need, which is the front is zero and the end is zero and then in the middle is one. 3:37 So that's really cool. That helps us clean up our data a lot. This is one of the easiest things.

To fix that sort of errant trailing spaces sometimes people put that in, sometimes programs put that in. 3:49 I've had sometimes a Google form or when I scrape the web and I have trailing spaces from scraping The web, copying, pasting, a ton of cells , a ton of tables and stuff. 4:01 We get all kinds of things. In here, it perturities up our data , but now we can clean it up with that trim formula . 4:08 Hope it was helpful.</div>