Specify certain length(characters) when defining a column

[ktuprah]

Is there anyway to specify a certain character length when defining a column
in excel? The worksheet in excel has to be exported into notepad. The
notepad data has to have a predetermined amount of characters in each
column.

 

[JLGWhiz]

Set your Excel font to the same as the Notepad font. Otherwise there is no
way to measure the length.

 

[Rick Rothstein]

You cannot enforce a maximum number of characters while the user is editing
the text in the cell, but you can check it afterwards, warn the user it is
too long an entry and then truncated it down to the maximum number of
characters you want to permit in that column. Here is example of how to
enforce a 10 character maximum length in Column C...

Private Sub Worksheet_Change(ByVal Target As Range)
Const MaxLen As Long = 10
If Target.Column = 3 And Len(Target.Value) > MaxLen Then
MsgBox "Entry too long; it will be trucated to '" & _
Left(Target.Value, MaxLen) & "'."
Application.EnableEvents = False
Target.Value = Left(Target.Value, MaxLen)
Application.EnableEvents = True
End If
End Sub

To install this code, right click the tab at the bottom of the worksheet,
select View Code from the popup menu that appears and then copy/paste the
above code into the code window that appeared. Now, go back to your
worksheet and try to enter more than 10 characters into a cell in Column C.

 

[p45cal]

Data Validation in cells can check text length for you.

This sounds like you're preparing fixed-width field files, so you ma
also want to pad out shorter strings to the right length. If you go fo
a vba (macro) solution, take a look at RSET and LSE

 

[Rick Rothstein]

True, Data Validation can be used to check text length, but **only** if
typed into the cell being validated... users can Paste longer text into the
cell without triggering the validation alert... the event code I posted will
react under both situations.