Excel can allegedly handle 4096 characters in a cell, but in todays podcast nothing past character 1130 is displaying in the cell. In Episode 631, a convoluted solution with a textbox solves the problem, but there must be a better way.
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.
Today we have a question that came in by telephone.
I'm sorry.
I didn't get the person's name, interesting problem that I have a solution for but certainly not the best solution.
Someone said you know, they downloaded a database and basically they have this one column where the text can be fairly long, and I said well how long is it.
I said use the LEN function.
The LEN function to see just how many characters were dealing with 1643.
Well now, in theory Excels go to 4096 characters, but clearly it's not working here what we see is that in this cell.
Let me color the cell force.
In this cell we are not seeing all of the text, I'm down here at the bottom if you can scroll down, it basically talks about Benedict bishop of Rochester and then Mast.
But if I would edit that cell, if I would edit that cell, I would see there's far more than that It actually goes past Benedict bishop of Rochester Master pandal subdeacon like goes on for about 10 more lines past that.
It's okay well, your row, not large enough for Macro height. Let's make it be 400 or something like that It still does not show us more text and actually what we're getting is even more space up at the top and we make the row taller.
We can make the the row a little bit shorter and would still give us just exactly the same amount of texts.
No matter what it always seems to cut off.
The first thing I tried was, I went in to Format Cells went into Alignment and change the Vertical Alignment to Top, tdoes not fix the problem it still cuts off at that exact same spot.
It's really bizarre.
Now, my solution to this was not probably the best solution and I'm sure that you'll let me know if there's something else.
I said let's edit that cell and select all of the text in the cell and then use control+C to copy those characters.
Now, I don't want to copy to the cell. I want to copy the characters from the cell.
I'll hit escape and you'll notice that down here in the drawing toolbar, we have a text box icon.
I'm going to choose text box, and I want to hold down the Alt key.
The Alt key will make sure that that text box is the exact same size as a cell and now, use control+V to paste.
And sure enough now, I get to see all of the text in the cell.
Now, some bizarre things.
We want to make sure to click on the border around the text box, right click, Format Text Box and a couple of things we can do.
First of all we can change the font, make the final a little bit smaller also on the properties say that we want to Move and size with cells and then finally if we don't want anyone to realize it's a text box, let's take the line and change it to no line. Click OK.
And now, we get a cell that appears to be the exact same size as the cell and gives us all of the text in the cell.
It's really bizarre if excel can allegedly handle 4096 characters, but it can't display them that we're stuck going out to a text box.
You want to make sure that it moves in size those cells that way if you actually sort, it will basically stay there lock to in this case cell C4.
You can actually go through and delete what's in C4, although you can't select that now with the mouse you actually have to go to D4, and then use the arrow key to go over and you can delete it.
You're left with the text box it works, It displays all the text, but it is a very convoluted way to go through and solve the problem.
If you have a better solution, let me know otherwise.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question that came in by telephone.
I'm sorry.
I didn't get the person's name, interesting problem that I have a solution for but certainly not the best solution.
Someone said you know, they downloaded a database and basically they have this one column where the text can be fairly long, and I said well how long is it.
I said use the LEN function.
The LEN function to see just how many characters were dealing with 1643.
Well now, in theory Excels go to 4096 characters, but clearly it's not working here what we see is that in this cell.
Let me color the cell force.
In this cell we are not seeing all of the text, I'm down here at the bottom if you can scroll down, it basically talks about Benedict bishop of Rochester and then Mast.
But if I would edit that cell, if I would edit that cell, I would see there's far more than that It actually goes past Benedict bishop of Rochester Master pandal subdeacon like goes on for about 10 more lines past that.
It's okay well, your row, not large enough for Macro height. Let's make it be 400 or something like that It still does not show us more text and actually what we're getting is even more space up at the top and we make the row taller.
We can make the the row a little bit shorter and would still give us just exactly the same amount of texts.
No matter what it always seems to cut off.
The first thing I tried was, I went in to Format Cells went into Alignment and change the Vertical Alignment to Top, tdoes not fix the problem it still cuts off at that exact same spot.
It's really bizarre.
Now, my solution to this was not probably the best solution and I'm sure that you'll let me know if there's something else.
I said let's edit that cell and select all of the text in the cell and then use control+C to copy those characters.
Now, I don't want to copy to the cell. I want to copy the characters from the cell.
I'll hit escape and you'll notice that down here in the drawing toolbar, we have a text box icon.
I'm going to choose text box, and I want to hold down the Alt key.
The Alt key will make sure that that text box is the exact same size as a cell and now, use control+V to paste.
And sure enough now, I get to see all of the text in the cell.
Now, some bizarre things.
We want to make sure to click on the border around the text box, right click, Format Text Box and a couple of things we can do.
First of all we can change the font, make the final a little bit smaller also on the properties say that we want to Move and size with cells and then finally if we don't want anyone to realize it's a text box, let's take the line and change it to no line. Click OK.
And now, we get a cell that appears to be the exact same size as the cell and gives us all of the text in the cell.
It's really bizarre if excel can allegedly handle 4096 characters, but it can't display them that we're stuck going out to a text box.
You want to make sure that it moves in size those cells that way if you actually sort, it will basically stay there lock to in this case cell C4.
You can actually go through and delete what's in C4, although you can't select that now with the mouse you actually have to go to D4, and then use the arrow key to go over and you can delete it.
You're left with the text box it works, It displays all the text, but it is a very convoluted way to go through and solve the problem.
If you have a better solution, let me know otherwise.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
