Craig is developing a worksheet and wants to know if there is a way to specify the maximum number of characters that can be entered in any given cell. He doesn't want to use Data Validation to impose the limitation.
There is no way to do this directly in Excel without (as Craig mentions) using Data Validation. There are a few things you can try to achieve the desired effect, however. First, you can using a formula to check the length of any cell, and then display an error message, if desired. For instance, if the cells you want to check are in column C, you could use a formula such as the following:
=IF((LEN(C1)>15),"Cell is Too Long","")
Place the formula in the cell to the right of the cell being checked (such as in cell D1), and then copy it down as many cells as necessary. When an entry is made in C1, and if it is more than 15 characters, then the message is displayed.
If such a direct approach is undesirable, then you'll need to use macros to do the checking. The following is a simple example that is triggered whenever something is changed in the worksheet. Each cell in the worksheet is then checked to make sure it is not longer than 15 characters. If such a cell is discovered, then a message box is displayed and the cell is cleared.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) For Each cell In UsedRange If Len(cell.Value) > 15 Then MsgBox " Can't enter more than 15 characters" cell.Value = "" End If Next End Sub
A more robust approach is to check in the event handler to see if the change was made somewhere within a range of cells that need to be length-limited.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rCell As Range Dim iChars As Integer On Error GoTo ErrHandler 'Change these as desired iChars = 15 Set rng = Me.Range("A1:A10") If Not Intersect(Target, rng) Is Nothing Then Application.EnableEvents = False For Each rCell In Intersect(Target, rng) If Len(rCell.Value) > iChars Then rCell.Value = Left(rCell.Value, iChars) MsgBox rCell.Address & " has more than" _ & iChars & " characters." & vbCrLf _ & "It has been truncated." End If Next End If ExitHandler: Application.EnableEvents = True Set rCell = Nothing Set rng = Nothing Exit Sub ErrHandler: MsgBox Err.Description Resume ExitHandler End Sub
To use this macro, you simply need to change the value assigned to iChars (represents the maximum length allowed) and the range assigned to rng (currently set to A1:A10). Because the macro checks only for changes within the specified range, it is much faster with larger worksheets than the macro that checks all the cells used.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10003) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Setting a Length Limit on Cells.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Auto-population of your formulas can be a useful tool when you are adding data to your worksheets. It would be even more ...
Discover MoreWhen working with data created outside of Excel, you may need to check that data to make sure it contains no unwanted ...
Discover MoreWant to get rid of most of the names defined in your workbook? You can either delete them one by one or use the handy ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-03-19 09:57:20
Dave Bonin
@Jens:
Yes, you could put such a formula in conditional formatting to, say, shade the cell red if it's contents are too long. This has the advantage of not requiring a second cell to hold the formula of the first cell you are checking.
Going a step further, you can put multiple data checks into conditional formats. Maybe checking the overall text length, verifying the first three letters are upper case, no spaces in the text, etc., etc. If you need to do multiple data integrity checks it may be helpful to give the user a specific error message based on what they entered wrong. In that case, you would need an extra cell to hold the message.
2019-03-19 05:37:25
Jens Holme Bjørneboe
If it's possible to put in a formula, then would it be possible to put in Conditional formatting?
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Comments