Ron from Dallas asks how he can limit the number of characters that will fit in a certain cell. Episode 585 shows you how.
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 netcast.
I'm Bill Jelen.
Today We have questions sent in by Ron from Dallas.
If you have a question for the podcast, please leave your question is either a voicemail or just drop me an email bill@mrexcel.com.
Ron is built a form in Excel, that he wants someone to fill out and then they're gonna print this form.
And he said hey, I forgot how to merge cells to make this particular piece of the form longer.
But, the frustrating part is that there's no way to make it stop at a certain number of characters.
So, for example, if someone fills out the form, and there's some important data that spills over the edge of the field.
Ron would like a way to stop them from entering data that is beyond the edge of the field.
Right now, if we printed this form, no one would be able to see anything that goes past the margin.
He compared it to a typewriter.
He says you know in the old days.
Basically, a typewriter would stopped typing when it got to the right edge of the the paper...
You know, there was a limit and there's a way to stop Excel from typing.
Well, I had a couple of ideas from Ron and neither of them are exactly perfect.
But, the first thing we want to do is we want to see basically, how many characters we think can fit in that field.
Now, unless you're using courier or a fixed width font it's going to be an approximation.
So, if I use the equal LENGTH function, LEN.
It'll tell me that there's about twenty two characters there.
So, what I can do is come back and use Data, and then choose Validation.
Now, in Data Validation, lots of times we use Data Validation to set up a drop-down list.
But, we can also say that we want a specific Text Length.
so, for example, will take data between a minimum of one and a maximum of twenty two characters, and we can set up an Error Alert, that says Data too Long.
This field will only accept 22 characters.
Click OK.
Now, this isn't perfect because unfortunately, it would be really nice is like on a typewriter if we had you know it's some sort of a ding, when we hit the 22.
In this case, if they ever something that's too long, It's going to tell them it'll accept 22 characters.
They can either Retry or Cancel.
So, Retry then they will have to go through and try and shorten it until it gets to the point that it is acceptable.
However, this is going to be frustrating because no one's going to know exactly where it is.
It's going to take a few iterations for them to accept the information and by that point.
They'll be screaming ready to kill the person who designed a spreadsheet.
So, Data Validation using the custom text length is one way to prevent people from creating data that is too long and tomorrow's podcast will take a look at another solution to this problem.
So, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today We have questions sent in by Ron from Dallas.
If you have a question for the podcast, please leave your question is either a voicemail or just drop me an email bill@mrexcel.com.
Ron is built a form in Excel, that he wants someone to fill out and then they're gonna print this form.
And he said hey, I forgot how to merge cells to make this particular piece of the form longer.
But, the frustrating part is that there's no way to make it stop at a certain number of characters.
So, for example, if someone fills out the form, and there's some important data that spills over the edge of the field.
Ron would like a way to stop them from entering data that is beyond the edge of the field.
Right now, if we printed this form, no one would be able to see anything that goes past the margin.
He compared it to a typewriter.
He says you know in the old days.
Basically, a typewriter would stopped typing when it got to the right edge of the the paper...
You know, there was a limit and there's a way to stop Excel from typing.
Well, I had a couple of ideas from Ron and neither of them are exactly perfect.
But, the first thing we want to do is we want to see basically, how many characters we think can fit in that field.
Now, unless you're using courier or a fixed width font it's going to be an approximation.
So, if I use the equal LENGTH function, LEN.
It'll tell me that there's about twenty two characters there.
So, what I can do is come back and use Data, and then choose Validation.
Now, in Data Validation, lots of times we use Data Validation to set up a drop-down list.
But, we can also say that we want a specific Text Length.
so, for example, will take data between a minimum of one and a maximum of twenty two characters, and we can set up an Error Alert, that says Data too Long.
This field will only accept 22 characters.
Click OK.
Now, this isn't perfect because unfortunately, it would be really nice is like on a typewriter if we had you know it's some sort of a ding, when we hit the 22.
In this case, if they ever something that's too long, It's going to tell them it'll accept 22 characters.
They can either Retry or Cancel.
So, Retry then they will have to go through and try and shorten it until it gets to the point that it is acceptable.
However, this is going to be frustrating because no one's going to know exactly where it is.
It's going to take a few iterations for them to accept the information and by that point.
They'll be screaming ready to kill the person who designed a spreadsheet.
So, Data Validation using the custom text length is one way to prevent people from creating data that is too long and tomorrow's podcast will take a look at another solution to this problem.
So, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
