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

    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
    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:

    Function rowcount() As Integer
        Dim wrappedsize As Single, unwrappedsize As Single
        wrappedsize = Selection.Height
        Selection.WrapText = False
        unwrappedsize = Selection.Height
        Selection.WrapText = True
        rowcount = wrappedsize / unwrappedsize
    End Function

    Stepping through from the following:

    Sub Testrowcount()
        Dim rCount As Integer
        rCount = rowcount
        MsgBox rCount
        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)

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

    "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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!