Formatting cells for size?

[Tyler Durden]

I know that you can set a text length in a cell and not allow more than that amount of characters. But can you set a certain size of your cell and not allow any more text that way? For instance, if I set a cell to allow 200 characters and the user of the file spaces out their text, they may have fewer than 200 characters but some of their text may be hidden after they exit the cell. It will show up in the formula bar while typing but disappears after exit. The worksheet I have is protected. Any help?

Thanks,

[Brian from Maui]

Tyler,

Data validation counts spaces.

[Jack in the UK]

im sure you fine text in D V is counted as any test or characters, ie symbols and spaces COUNT to:

[Tyler Durden]

When I say spaces I'm talking about a full carriage return (ALT + ENTER) within a cell. If a user spaces their text that way there is a chance (because it has happened) that they enter a larger portion of text, but not too many characters. The cell expands as they are typing, but reduces to regular size after they exit. Therefore hiding certain parts of their text. Am I making sense with this?

[Jack in the UK]

=LEN(A2)

this will count the number of text in a cell inc spaces...

sorry did want to add this, got disterbed by my 3 boys...

now you can work from there

[Brian from Maui]

Tyler,

How wide is your columns?
A text length of 200 is pretty wide

[Tyler Durden]

It's a form that includes merged cells and wrapped text. This cell is actually a combination of 10 columns wide by 20 rows tall. It is one of the few sheets in the workbook that uses so much text.

[Brian from Maui]

Insert a comment in the cell,

Do not use ALT + Enter, or set your alignment to left align

[Jack in the UK]

im sure *merge* stop this

so that the problem.

[Brian]

METHOD #1:
The following will strip the "Alt-Enter" (enter this as you would normally between the quotes

=REPLACE(G7,SEARCH("
",G7),1," ")

or

=REPLACE(G7,SEARCH(CHAR(10),G7),1," ")

However, if there is no Alt-enter, then search will return N/A so you need to add If statement to accomodate this. And you can't loop, or count the number, so you need to hard code enough repetitions to cover the likely events.

METHOD #2
Easier to use a loop in a VBA macro to clean it. (two subs below are similar to what you need)

METHOD #3 - tentative
If the user enters in one cell, then you could use the trim function to display in another cell. This will strip extra spaces.

DisplayCell = trim(entryCell)

It is a little bizarre though. When
EntryCell = 1alt-enter2
Trim() = 12
but when paste special> value it still displays as 12, but then if F2 to edit the pasted location and then return to save it, the alt-enter is still there and displays as 2 lines!

MORE ON METHOD #2:
You can use VBA to strip any Alt-Enter characters, trim and then truncate at 200 characters. This macro could be run on the Worksheet_change event.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A10")) Is Nothing Then
trim cell "A10"
Else
do something else
End If
End Sub

Function CountElements(Txt, Separator) As String
' Counts the number of elements that are separated by a specified separator character in a text string

Dim Txt1, LastCharacter As String
Dim ElementCount As Integer, i As Integer

' Initialize
ElementCount = 0
Txt1 = Txt

' Check that the cell is not blank
If Txt1 = "" Then
CountElements = ""
Exit Function
End If

' Check if there is at least one element
If Txt1 = Separator Then
CountElements = 0
Exit Function
End If

' If the last character is not a separator, then add a separator to the end of the string
LastCharacter = Right(Txt1, 1)
If LastCharacter <> Separator Then Txt1 = Txt1 & Separator

' Extract each element
For i = 1 To Len(Txt1)
If Mid(Txt1, i, 1) = Separator Then
ElementCount = ElementCount + 1
End If
Next i
CountElements = ElementCount
End Function


Function ExtractElement(Txt, n, Separator) As String
' Returns the nth element of a text string, where the elements
' are separated by a specified separator character

Dim Txt1 As String, TempElement As String
Dim ElementCount As Integer, i As Integer

Txt1 = Txt
' If space separator, remove excess spaces
If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)

' Add a separator to the end of the string
If Right(Txt1, Len(Txt1)) <> Separator Then Txt1 = Txt1 & Separator

' Initialize
ElementCount = 0
TempElement = ""

' Extract each element
For i = 1 To Len(Txt1)
If Mid(Txt1, i, 1) = Separator Then
ElementCount = ElementCount + 1
If ElementCount = n Then
' Found it, so exit
ExtractElement = TempElement
Exit Function
Else
TempElement = ""
End If
Else
TempElement = TempElement & Mid(Txt1, i, 1)
End If
Next i
ExtractElement = ""
End Function