Gary points out that the TRIM Function in VBA isn't working like =TRIM() Function in the worksheet; the spaces in the middle remain after running the Macro. Bill shows us, In episode #1465, how to solve this issue with the 'Application.WorksheetFunction' to remove those spaces in the middle.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1465: VBA trim.
Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
Today's question is sent in by Gary.
Gary has some data that has leading spaces, trailing spaces, and spaces in the middle.
All right, so, the TRIM function in Excel will get rid of the leading spaces, the trailing spaces, and also the extra spaces in the middle.
So, if you have ABC space, space, space DEF, that'll reduce that down to one.
So, Gary was writing a macro in VBA, Alt F11-- let’s see here.
Insert, module, let’s call it Gary one-- no, let’s call it Gary.
Yes, for each cell in selection, cell dot value is equal to the trim of cell dot value.
Next.
So, let's select this whole range, the exact same data we had over there in column A. We'll run this macro and Gary is amazed to find out that the VBA trim gets rid of the leading spaces and the trailing spaces, but does not get rid of the extra spaces in the middle.
He's like, “What's up with that?
Why doesn't that work?” Okay, so, TRIM existed in VBA long ago; long before VBA was ever incorporated into Excel.
All right, so, what we're going to do here is we're going to instead of using the VBA version of TRIM, we’re going to use the Excel version of TRIM.
So, cell dot value is equal to application dot worksheet function dot-- and I'm holding my breath to hope that it's here.
See, they don't have all 400 Excel calculation functions here, but they have a lot of them.
If the Excel team did the right thing, they gave us their TRIM.
There it is.
Cell dot value.
So, what this is going to do, this is going to run the Excel version of TRIM instead of the VBA version of TRIM.
Let's select that range and there we go.
It goes back and does the right thing.
So, today we're talking about application dot worksheet function.
If you need to rock out something that Excel does that VBA doesn't seem to do, try that and see if you're lucky enough that the function you need is in that list.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1465: VBA trim.
Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
Today's question is sent in by Gary.
Gary has some data that has leading spaces, trailing spaces, and spaces in the middle.
All right, so, the TRIM function in Excel will get rid of the leading spaces, the trailing spaces, and also the extra spaces in the middle.
So, if you have ABC space, space, space DEF, that'll reduce that down to one.
So, Gary was writing a macro in VBA, Alt F11-- let’s see here.
Insert, module, let’s call it Gary one-- no, let’s call it Gary.
Yes, for each cell in selection, cell dot value is equal to the trim of cell dot value.
Next.
So, let's select this whole range, the exact same data we had over there in column A. We'll run this macro and Gary is amazed to find out that the VBA trim gets rid of the leading spaces and the trailing spaces, but does not get rid of the extra spaces in the middle.
He's like, “What's up with that?
Why doesn't that work?” Okay, so, TRIM existed in VBA long ago; long before VBA was ever incorporated into Excel.
All right, so, what we're going to do here is we're going to instead of using the VBA version of TRIM, we’re going to use the Excel version of TRIM.
So, cell dot value is equal to application dot worksheet function dot-- and I'm holding my breath to hope that it's here.
See, they don't have all 400 Excel calculation functions here, but they have a lot of them.
If the Excel team did the right thing, they gave us their TRIM.
There it is.
Cell dot value.
So, what this is going to do, this is going to run the Excel version of TRIM instead of the VBA version of TRIM.
Let's select that range and there we go.
It goes back and does the right thing.
So, today we're talking about application dot worksheet function.
If you need to rock out something that Excel does that VBA doesn't seem to do, try that and see if you're lucky enough that the function you need is in that list.
Well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
