Count Word-wrap Line Feeds

  • I've seen people ask for ways to count the number of line wraps in a cell before, but all the answers (which apparently worked) mentioned counting the number of CHAR(10) characters, and this doesn't help me. I need to count the number of automatic line feeds that Excel makes when word-wrap is enabled. Is that possible?

  • Re: Count Word-wrap Line Feeds


    There is no 'automatic' line feed (or, at least none you can determine). Excel simply wraps the text.


    You can test this by adding some text that wraps in a cell (don't forget to format the cell to Word Wrap). Make sure the cell is selected and copy the following to the immediate window in the VBA editor

    Code
    1. for itemp = 1 to len(activecell.Text): debug.Print asc(mid(activecell.Text, itemp, 1)), mid(activecell.Text, itemp, 1):next


    Press Return and Excel will print the ASCII value for each character in the cell along with the character itself. No additional characters will be printed. A sample of the output I get is:


    108 l
    111 o
    110 n
    103 g
    32
    116 t
    101 e
    120 x
    116 t


    Excel wraps the cell immediately before the first 't' - the ASCII value 32 before that is a space, which displays on the previous line.

  • Re: Count Word-wrap Line Feeds


    This works for me. In my case, there is just one cell in the row that is being wrapped, all the cells are using the same font (Arail 10). Assuming that cell is selected and wrapped already:

    Code
    1. Function rowcount() As Integer
    2. Dim wrappedsize As Single, unwrappedsize As Single
    3. Rows(Selection.Row).AutoFit
    4. wrappedsize = Selection.Height
    5. Selection.WrapText = False
    6. unwrappedsize = Selection.Height
    7. Selection.WrapText = True
    8. rowcount = wrappedsize / unwrappedsize
    9. End Function


    Stepping through from the following:

    Code
    1. Sub Testrowcount()
    2. Dim rCount As Integer
    3. rCount = rowcount
    4. MsgBox rCount
    5. End Sub


    I see wrappedsize is 12.75, unwrappedsize is 51, answer is 4. Exactly.


    This has not been tested with other fonts or font sizes.


    Sometimes -- but rarely (I first saw it just now, testing with very long text that wrapped to over 4 lines) -- setting the cell to wrap creates an initial blank line that is counted and that I can't remove, except by setting the row height manually. I have not isolated the conditions that cause this, and would welcome advice on that.

  • Re: Count Word-wrap Line Feeds


    Greetings - I have lurked here many times, but have finally signed up!


    This subject is bugging me, as I am trying to find a way of correctly sizing the height of merged cells that have very variable amounts of text in them.


    It seems impossible to count the number of line returns that excel automatically uses, and my attempts to estimate the number of lines as a function of the number of characters in a cell frequently leaves me with either hidden text or white space.


    Is there any way of calculating the width of individual letters, to enable the line return point to be accurately predicted?

  • Re: Count Word-wrap Line Feeds


    Welcome to Ozgrid, Tom.


    The convention here is that you do not post questions in threads started by other members.


    Please start your own thread, give it an accurate and concise title and explain your issue fully.


    If you think this thread can help clarify your issue, you can include a link to it by copying the URL from the address bar of your browser and pasting into your message.

  • Re: Count Word-wrap Line Feeds



    Hey guys,


    I had a hard time figuring how to manipulate merged cells and sizing them, and counting the amount of line feeds in them.


    Neverlift gave a part of the answer, and actually i had to resort to a very simple trick using his hints.


    Fact is that since Autofit, height, etc... do not work with merged cells, I just copied the content and font into a non merged cells.


  • Hi, I wrote this function to calcutae it based upon the linefeeds and that's not working (stated eaerlier by evryone)


    Code
    1. Public Function count_TextLines(myText As Variant) As Integer
    2. ' Counts the lines by comparing the contents by the themselves after removing
    3. ' the line brakes which is character 10.
    4. ' This gives back the number of breaks so we add 1 to get the number of lines,
    5. ' since the last line doesn't have a line break.
    6. count_TextLines = IIf(Len(Trim(myText)) = 0, 0, (Len(myText) - Len(Replace(myText, Chr(10), "")) + 2))
    7. End Function


    Maybe integrating this with merged cells columnwidths added up and the fontsize ...

    I'm goinf to try that, triggered by nruvba ; nice trick will update

    Hans
    "IT" Always crosses your path :)

  • Keebellah


    Welcome to the Forum


    Please start your own post. Posting in another member's Thread is known as hijacking and is not allowed here. By all means add a link to a Thread that may be related to your question.

  • royUK

    Closed the thread.