Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify certain length(characters) when defining a column
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify certain length(characters) when defining a column
Set your Excel font to the same as the Notepad font. Otherwise there is no
way to measure the length. "ktuprah" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify certain length(characters) when defining a column
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. -- Rick (MVP - Excel) "ktuprah" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify certain length(characters) when defining a column
Data Validation in cells can check text length for you. This sounds like you're preparing fixed-width field files, so you may also want to pad out shorter strings to the right length. If you go for a vba (macro) solution, take a look at RSET and LSET -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160321 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify certain length(characters) when defining a column
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. -- Rick (MVP - Excel) "p45cal" wrote in message ... Data Validation in cells can check text length for you. This sounds like you're preparing fixed-width field files, so you may also want to pad out shorter strings to the right length. If you go for a vba (macro) solution, take a look at RSET and LSET -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=160321 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specify certain length(characters) when defining a column | Excel Programming | |||
Defining a variable length area for output. | Excel Discussion (Misc queries) | |||
Defining a length | Excel Programming | |||
visual basic editor- defining length of a year | Excel Programming | |||
Defining the length of an array variably | Excel Programming |