Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Formatting cells for size?

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

  2. #2
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tyler,

    Data validation counts spaces.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =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


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  6. #6
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tyler,

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

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  8. #8
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Insert a comment in the cell,

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

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    im sure *merge* stop this

    so that the problem.


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •