So you need to remove some extra spaces. Well, you might have some spaces between words, you might have a big gap between text and some symbols, or you have some prices and currency kind of not lining up that you need to clean up, or you have some mixed quotes where you have different symbols and then bunch of spaces, or even just you're p- copying and pasting data into a cell. You wanna keep that data in the cell, but you wanna get rid of this really large white space. I'm gonna show you how to do that with a simple formula . A little bit of, a little bit of code inside of the formula . Du- it's not actually like code code, it's just a certain sort of symbol that we have to do. We're gonna do regex or regular expressions , and this is a formula that has… There's actually like three different formulas.

We can match , we can extract information, so take a little bit of information and get the exact thing out of it. We can look for does the… Is, is there a match ? Or we can say, "If there's a match , just replace it with something," which is what we're gonna use today. So we're gonna use regex , regular expressions , replace. We're gonna use this formula , and we need the text to start off with to say, "Hey, in this text," and then put a comma or a delimiter depending on what locale you're in. Maybe this is a semicolon. Up to you and your locale. Then we're gonna say, "Search for something." Well, we're gonna search for /s+. What this means is that look for more than one space. End quote. And then we're gonna add one more thing, which is replace it with one single space.

So this is saying any spaces we find, as many spaces as there is, just replace it with one single space. I think you saw before I added that, if I have this, it shows me what the result will be before I even do it. So I wanna be like, "Hmm, that's not exactly correct," so let's just add a space. End parentheses and hit enter, and it'll say, "Oh, suggest more." And you can go and look at those. But I wanna look at this again and look at the difference. It's minor, but it makes a huge difference in cleaning up your data and making sure that it looks and feels correct. Also, it can get rid of if for some reason there is new lines sometimes

in text and we copy and paste it into a cell, it becomes large spaces. Or tab separated values are actually like more than one space. So this is really cool. I can also use this one formula across all of these if I wrap it with ARRAYFORMULA . I'm gonna wrap it with ARRAYFORMULA and do one minor change, which just changes B3 to B3 colon B to say, "Hey, look at that whole range , and then give me each response individually in a cell." End parenthesis, and there is all of our results out of this one formula , which is really, really cool, really, really easy to clean up our data without having to go up to data cleaning or do it with some other thing.

We can do it ourselves, and now we have this result that we can copy and paste our values back onto our text and be like, "Okay, that was our information, and now it's clean." You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.