Episode 466 shows how to get rid of leading spaces while leaving the interior spaces intact. Plus news on Microsoft MVP Summit from Seattle and preview of Excel guest appearances on the MrExcel podcasts.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel Podcast.
I'm Bill Jelen.
That's early early early Friday morning.
I'm on my way home on an overnight flight from Seattle back to Cleveland to spent the this week at the Microsoft MVP's Summit.
Which is fabulous because there were 32 Excel experts there.
Plus experts in word Power-point and all of the Office products.
Now, it's great to get to see all these people and catch up but the beautiful thing is we spent a lot of time with the folks at Microsoft, who are already busy working on office 14 which will come out maybe in 2009 or 2010.
Some of the things that I complain about it in the new version of Excel.
They're already working on fixing.
They might even have a service release and about a year fixing some of the cool problems.
But it was great to see what they're planning, and it's really going to be a great evolution of Excel.
Excel definitely is not finished.
They have many more ideas to come.
Now, we have a question today that was sent in by George.
George has some data over in column A and this data happens to have leading spaces.
But it also has interior spaces.
Though George said "I used edit replace to get rid of all the leading spaces It's also going to get rid of that interior space" You wanted to get rid of the leading spaces, but leave the interior space.
So, my solution to this is I have to insert a new column between Column A and the data I'm going to use a function that Excel has called the trim function.
=TRIM Trim of A1 will take the leading space out But leave single interior spaces in.
Now, if you would happen to have for example the letters a,b,c and then space space space Tef.
TRIM will take those three spaces and reduce it down to one space, but it also takes the leading and trailing spaces out.
So I'll copy that data down that we need to copy and Paste Special values of course.
So, my trick for that is to Right Click drag it over.
Let go and say copy here is values only.
I can aptly call it B and was good to go.
Now, for the next couple of weeks, I have something specials while I was in Seattle.
I would approach various people and say hey, could you come up with a two minute tip in Excel?
And so, I for the next ten podcast have guests Podcasters To Show you their favorite tips you'll see a variety of people.
Either people that you know that because they run their blogs like Dip from Daily Dose Of Excel or people who are writing books like Bottomless.
Be sure to watch over the next couple weeks to see all of these experts their favorite tips.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
That's early early early Friday morning.
I'm on my way home on an overnight flight from Seattle back to Cleveland to spent the this week at the Microsoft MVP's Summit.
Which is fabulous because there were 32 Excel experts there.
Plus experts in word Power-point and all of the Office products.
Now, it's great to get to see all these people and catch up but the beautiful thing is we spent a lot of time with the folks at Microsoft, who are already busy working on office 14 which will come out maybe in 2009 or 2010.
Some of the things that I complain about it in the new version of Excel.
They're already working on fixing.
They might even have a service release and about a year fixing some of the cool problems.
But it was great to see what they're planning, and it's really going to be a great evolution of Excel.
Excel definitely is not finished.
They have many more ideas to come.
Now, we have a question today that was sent in by George.
George has some data over in column A and this data happens to have leading spaces.
But it also has interior spaces.
Though George said "I used edit replace to get rid of all the leading spaces It's also going to get rid of that interior space" You wanted to get rid of the leading spaces, but leave the interior space.
So, my solution to this is I have to insert a new column between Column A and the data I'm going to use a function that Excel has called the trim function.
=TRIM Trim of A1 will take the leading space out But leave single interior spaces in.
Now, if you would happen to have for example the letters a,b,c and then space space space Tef.
TRIM will take those three spaces and reduce it down to one space, but it also takes the leading and trailing spaces out.
So I'll copy that data down that we need to copy and Paste Special values of course.
So, my trick for that is to Right Click drag it over.
Let go and say copy here is values only.
I can aptly call it B and was good to go.
Now, for the next couple of weeks, I have something specials while I was in Seattle.
I would approach various people and say hey, could you come up with a two minute tip in Excel?
And so, I for the next ten podcast have guests Podcasters To Show you their favorite tips you'll see a variety of people.
Either people that you know that because they run their blogs like Dip from Daily Dose Of Excel or people who are writing books like Bottomless.
Be sure to watch over the next couple weeks to see all of these experts their favorite tips.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
