Formatting cells for size?

Tyler Durden

New Member
Joined
Apr 23, 2002
Messages
17
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,
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?
 
Upvote 0
=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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top