Mysteriously, some Excel cells are showing a leading space. But when you double-click to edit the cell, the leading space disappears! The cells are left-justified, no indent. What is causing the problem?
Transcript of the video:
MrExcel Podcast is sponsored by International Day Against DRM – Tuesday, May 6th.
You know, it’s funny, a lot of publishers are offering 50% off their DRM-free Ebooks today, which I guess, as I’m saying, “Hey, please give us money because we support this DRM-free initiative.
The books that I published are DRM-free because I believe you, the customers, should be allowed to move your Ebook from any devise to another without hassles.
After today’s podcast, stick around for the outtake, I’ll give you a code for DRM-free Ebook completely free.
Learn Excel from MrExcel Podcast Episode 1884: What is Causing this Leading Space?
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Here is a perplexing question posed by Marsha and her co-worker, Jerry from my Fort Wayne seminar.
Marsha had some data where they had used all item.
And check this out.
This one is completely Flush Left, but this one for some reason is indented one character.
And, you know, we were trying to think about what character that could be.
But here’s the really bizarre thing, when you double click to put it in Edit mode, that character goes away.
What could possibly be causing that?
And we look for =LEFT(A2,1) and then the =CODE(B2) and it’s just a regular T, there’s nothing special.
It’s not some sort of magic T that always gets indented and so that was very perplexing.
Is it left justified?
Yes, it is left justified.
Have they turned on the indent?
No.
If we go to alignment, the indent is 0.
There’s nothing, nothing there at least that I could figure that would be causing that.
Oh, but here, check this out.
I’m going to zoom out a little bit.
So, here’s this one that’s Flush left, and you see that we are in General mode.
Here’s the one that’s been indented, and it is in Accounting mode.
So the answer is to go back from Accounting General.
But let’s dive in to this a little bit more.
Here’s another one that’s Accounting mode.
When we go to Ctrl+1 to the Number tab and it’s in Accounting.
Let’s see what the actual Custom number format is there.
And what they’re doing that _( is saying, “Hey, we’re going to leave enough space to fit an open parenthesis so that way, these positive numbers will line up with the negative numbers that might appear in other cells, underscore and the characters.” So that’s kind of wild.
Check this out.
So, this is interesting.
Let’s go to New Sheet, I’ll put in some just ABC and we’ll go to the first one, Ctrl+1 and go to the Custom Number format and instead of General, we will say that we want to insert the space of a capital _W@ and you see that that takes that text which is ABC without any leading space and it adds enough space for a W. If we do the same thing here and we would say that we want to use _i@, a very narrow character, it will indent less.
What a bizarre, bizarre little trick.
Hey, I want to thank Marsha and Jerry who are in my Fort Wayne seminar for that question and actually as well.
And I want to thank you for stopping by, we’ll see you next time for another netcast of MrExcel.
Hey, all right, thanks for hanging around, it’s International Day Against DRM.
All the books we sell at Holy Mackerel Books are DRM-free.
You’re allowed to move them from one device to another device.
We don’t want to put any hassle for you.
So, in honor of today, May 6th, then for the next couple of days, let’s go mrexcel.com, left-hand side go to MrExcel Store, search for VLOOKUP and here it is, VLOOKUP Awesome Quick, see we’ve lowered the price to $0.99 but it’s even better than that.
Add it to your cart and then check out and use the coupon code DRMFREE, Apply Coupon code and that book will be completely for you.
You want to make sure that you check out.
Enter all of your information here because you need that to sign back in to get the download.
I’m going to sign in and for your payment method, just choose Credit Card, click Continue and that will make sure that your order is approved.
Once you’re there, go to My Account and click on download and you’ll have two versions free for download, a PDF available on any computer or if you have an iPad, check out the ePub, great version with some videos embedded.
And again, thanks to all of my customers at MrExcel.com.
Our Holy Mackerel titles have been DRM-free since the beginning.
You know, it’s funny, a lot of publishers are offering 50% off their DRM-free Ebooks today, which I guess, as I’m saying, “Hey, please give us money because we support this DRM-free initiative.
The books that I published are DRM-free because I believe you, the customers, should be allowed to move your Ebook from any devise to another without hassles.
After today’s podcast, stick around for the outtake, I’ll give you a code for DRM-free Ebook completely free.
Learn Excel from MrExcel Podcast Episode 1884: What is Causing this Leading Space?
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Here is a perplexing question posed by Marsha and her co-worker, Jerry from my Fort Wayne seminar.
Marsha had some data where they had used all item.
And check this out.
This one is completely Flush Left, but this one for some reason is indented one character.
And, you know, we were trying to think about what character that could be.
But here’s the really bizarre thing, when you double click to put it in Edit mode, that character goes away.
What could possibly be causing that?
And we look for =LEFT(A2,1) and then the =CODE(B2) and it’s just a regular T, there’s nothing special.
It’s not some sort of magic T that always gets indented and so that was very perplexing.
Is it left justified?
Yes, it is left justified.
Have they turned on the indent?
No.
If we go to alignment, the indent is 0.
There’s nothing, nothing there at least that I could figure that would be causing that.
Oh, but here, check this out.
I’m going to zoom out a little bit.
So, here’s this one that’s Flush left, and you see that we are in General mode.
Here’s the one that’s been indented, and it is in Accounting mode.
So the answer is to go back from Accounting General.
But let’s dive in to this a little bit more.
Here’s another one that’s Accounting mode.
When we go to Ctrl+1 to the Number tab and it’s in Accounting.
Let’s see what the actual Custom number format is there.
And what they’re doing that _( is saying, “Hey, we’re going to leave enough space to fit an open parenthesis so that way, these positive numbers will line up with the negative numbers that might appear in other cells, underscore and the characters.” So that’s kind of wild.
Check this out.
So, this is interesting.
Let’s go to New Sheet, I’ll put in some just ABC and we’ll go to the first one, Ctrl+1 and go to the Custom Number format and instead of General, we will say that we want to insert the space of a capital _W@ and you see that that takes that text which is ABC without any leading space and it adds enough space for a W. If we do the same thing here and we would say that we want to use _i@, a very narrow character, it will indent less.
What a bizarre, bizarre little trick.
Hey, I want to thank Marsha and Jerry who are in my Fort Wayne seminar for that question and actually as well.
And I want to thank you for stopping by, we’ll see you next time for another netcast of MrExcel.
Hey, all right, thanks for hanging around, it’s International Day Against DRM.
All the books we sell at Holy Mackerel Books are DRM-free.
You’re allowed to move them from one device to another device.
We don’t want to put any hassle for you.
So, in honor of today, May 6th, then for the next couple of days, let’s go mrexcel.com, left-hand side go to MrExcel Store, search for VLOOKUP and here it is, VLOOKUP Awesome Quick, see we’ve lowered the price to $0.99 but it’s even better than that.
Add it to your cart and then check out and use the coupon code DRMFREE, Apply Coupon code and that book will be completely for you.
You want to make sure that you check out.
Enter all of your information here because you need that to sign back in to get the download.
I’m going to sign in and for your payment method, just choose Credit Card, click Continue and that will make sure that your order is approved.
Once you’re there, go to My Account and click on download and you’ll have two versions free for download, a PDF available on any computer or if you have an iPad, check out the ePub, great version with some videos embedded.
And again, thanks to all of my customers at MrExcel.com.
Our Holy Mackerel titles have been DRM-free since the beginning.
