Kristin wants to have a fast way to remove leading a trailing spaces from an entire range of Excel cells. In this episode, a 7-line macro to solve this problem.
Sub TrimSelection()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = Trim(cell.Value)
End If
Next cell
End Sub
Sub TrimSelection()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = Trim(cell.Value)
End If
Next cell
End Sub
Transcript of the video:
Learn Excel from Mr. Excel podcast 2268. VBA Macro to trim all cell and selections.
Alright. So I was doing a power excel seminar and Kirstin, Kirstin said, “Hey, we’re talking about VLOOKUP and one of the common things that causes VLOOKUP to not work is if your data has trailings basis.” Alright, so right here instead of just Kiwi, I have Kiwi space, space and that’s causing things to not work. And I usually suggest coming out here and using the trim function. Equal trim, that whole thing or even using the trim function inside. But Kirstin just said she’s downloading data and she has multiple columns that all need to be trimmed every single time.
And if there’s some each way to do this.
I said, “Well what we could do is, we could knock out a quick little macro, a VBA Macro.” You know, hey, I always tell people I use Power Query 90% of the time instead of macros.
But every once in a while macro is really, really good.
Alright, so Alt F11 and we have this, we have this little macro right here. Sub, trim selection and I dim cell is arranged. For each cell in selection, cell.value equals the trim of the cell.value. But I don’t want to screw up any formulas, right incase someone space their formulas out, I’ve seen people do that. So if not, cell. Formula then do that whole thing. So that’s trim selection here.
Let’s come back and we’ll customize the quick access tool bar, go to macros and find personal xls trim selection right there, click add, I already added it and then modify to come up with a nice little icon there and nice words of trim selections.
Alright, so now let’s check this out. Here’s how it works. We choose all the cells that we want to trim. And then the trim gets rid of—if you have multiple spaces between two words, trim, we’ll get rid of those, alright.
So I’m going to just trim the selection here. That one. And all of our VLOOKUP starts to work. Wrong format, but other than that, although, really interesting, the VBA version of trim doesn’t rid of the extra spaces inside. It got rid of the leaving spaces and the trailing spaces but not the spaces inside.
And very nice, it didn’t touch our formulas.
So that little VBA there for Kirstin, I want to thank you for stopping by. We’ll see you next time for another netcast from Mr.
Excel.
Alright. So I was doing a power excel seminar and Kirstin, Kirstin said, “Hey, we’re talking about VLOOKUP and one of the common things that causes VLOOKUP to not work is if your data has trailings basis.” Alright, so right here instead of just Kiwi, I have Kiwi space, space and that’s causing things to not work. And I usually suggest coming out here and using the trim function. Equal trim, that whole thing or even using the trim function inside. But Kirstin just said she’s downloading data and she has multiple columns that all need to be trimmed every single time.
And if there’s some each way to do this.
I said, “Well what we could do is, we could knock out a quick little macro, a VBA Macro.” You know, hey, I always tell people I use Power Query 90% of the time instead of macros.
But every once in a while macro is really, really good.
Alright, so Alt F11 and we have this, we have this little macro right here. Sub, trim selection and I dim cell is arranged. For each cell in selection, cell.value equals the trim of the cell.value. But I don’t want to screw up any formulas, right incase someone space their formulas out, I’ve seen people do that. So if not, cell. Formula then do that whole thing. So that’s trim selection here.
Let’s come back and we’ll customize the quick access tool bar, go to macros and find personal xls trim selection right there, click add, I already added it and then modify to come up with a nice little icon there and nice words of trim selections.
Alright, so now let’s check this out. Here’s how it works. We choose all the cells that we want to trim. And then the trim gets rid of—if you have multiple spaces between two words, trim, we’ll get rid of those, alright.
So I’m going to just trim the selection here. That one. And all of our VLOOKUP starts to work. Wrong format, but other than that, although, really interesting, the VBA version of trim doesn’t rid of the extra spaces inside. It got rid of the leaving spaces and the trailing spaces but not the spaces inside.
And very nice, it didn’t touch our formulas.
So that little VBA there for Kirstin, I want to thank you for stopping by. We’ll see you next time for another netcast from Mr.
Excel.
