+ Reply to Thread
Results 1 to 37 of 37

LEN formula excludes line feed?

  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Question LEN formula excludes line feed?

    I am using LEN function to determine the character count of a cell, but it appears that line feed are excluded! I need to include all the characters that include spaces, linefeed, newline character in len formuala. Please advice.
    Last edited by Pavan Renjal; 01-06-2013 at 02:22 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: LEN formula excludes line feed?

    What linefeed character are you referring to?

    If I enter data in a cell on multiple lines using ALT+ENTER the linefeed/newline characters are counted when I use LEN.
    If posting code please use code tags, see here.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: LEN formula excludes line feed?

    What are you using/doing to get a line feed? Alt-Enter gives you CHAR(10).

    =1&CHAR(10)&CHAR(13)&2 with "Wrap text" turned on displays 1 and 2 on separate lines. The character count (LEN) is four (4).

    The formula: =CODE(MID(_Text,2,1))&CODE(MID(_Text,3,1)) displays 1013 (as text) due to the MID function.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    I need to restrict a cell to 1000 characters. If it includes newline/linefeed I am not sure what is excluded then . In Notepad ++ the count appears more(which for me is correct count). Is there any other characters that LEN function doesnt take into consideration?

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: LEN formula excludes line feed?

    How are you trying to limit the cell to 1000 characters?

  6. #6
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    @Norie: I think restricting will be another part of it(probably use data validation), right now i just want the correct count to be displayed when i use LEN function.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: LEN formula excludes line feed?

    Can you attach a workbook that exhibits LEN giving incorrect results?

    PS Why so many characters in one cell?

  8. #8
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    I am using Alt+Enter i.e CHAR(10). So it takes as CHAR(10) as 1 byte, it means its counted in LEN. I am not sure what is getting excluded.

    Quote Originally Posted by TMShucks View Post
    What are you using/doing to get a line feed? Alt-Enter gives you CHAR(10).

    =1&CHAR(10)&CHAR(13)&2 with "Wrap text" turned on displays 1 and 2 on separate lines. The character count (LEN) is four (4).

    The formula: =CODE(MID(_Text,2,1))&CODE(MID(_Text,3,1)) displays 1013 (as text) due to the MID function.


    Regards, TMS

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: LEN formula excludes line feed?

    Can you give examples?

  10. #10
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    Hi Norie, i have attached the sample workbook below. I have included LEN function in A1 cell, the count is 1596, in Notepad++ the length is 1602 (which is the count i need):

    LEN_function.xlsx

    Quote Originally Posted by Norie View Post
    Can you attach a workbook that exhibits LEN giving incorrect results?

    PS Why so many characters in one cell?

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: LEN formula excludes line feed?

    When I copy the text from A2 into Notepad++ quotes are added at the beginning and end.

    Why do you need a specific count of 1602?

  12. #12
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    Hi Norie! If you copy from inside of the cell you can avoid quotes, and the count will be 1602. Notepad gives me the count i need, earlier i used to validate with Notepad ++ to get the character count to load the data into production(there is a restriction of 1000 characters). I wanted initial validation to happen in excel itself, but the len function is not giving me the count i need.

    Quote Originally Posted by Norie View Post
    When I copy the text from A2 into Notepad++ quotes are added at the beginning and end.

    Why do you need a specific count of 1602?

  13. #13
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    To be more specific this is the first level of validation i will be performing in excel using the conditional formatting to highlight cells in a column range which have exceeded 1000 characters.

    Quote Originally Posted by Pavan Renjal View Post
    Hi Norie! If you copy from inside of the cell you can avoid quotes, and the count will be 1602. Notepad gives me the count i need, earlier i used to validate with Notepad ++ to get the character count to load the data into production(there is a restriction of 1000 characters). I wanted initial validation to happen in excel itself, but the len function is not giving me the count i need.

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: LEN formula excludes line feed?

    When I copy and paste directly from the cell the count is 1604 in Notepad++

    When I copy into Word the count is 1596.

    Why do you need the character count to be 1602?

  15. #15
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    I copied the data from Notepad to excel first, if copied from cell directly back to notepad the quotes get added. As i said before, you can avoid this by copying from inside of the cell. Can you give me an insight of why there is a difference in the length count?

    Quote Originally Posted by Norie View Post
    When I copy and paste directly from the cell the count is 1604 in Notepad++

    When I copy into Word the count is 1596.

    Why do you need the character count to be 1602?

  16. #16
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: LEN formula excludes line feed?

    Notepad++ counts the carriage returns, the quotes, and the trailing line feed. That's the 10-character difference.
    Entia non sunt multiplicanda sine necessitate

  17. #17
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    Hi shg, thanks for the response! Now how can i include all these carriage return, trailing line feed in my LEN function? Is there away to achieve this in a formula?

    Quote Originally Posted by shg View Post
    Notepad++ counts the carriage returns, the quotes, and the trailing line feed. That's the 10-character difference.

  18. #18
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    I just hope If i can achieve this somehow, this will eliminate a major part of my problem!

    Quote Originally Posted by Pavan Renjal View Post
    Hi shg, thanks for the response! Now how can i include all these carriage return, trailing line feed in my LEN function? Is there away to achieve this in a formula?

  19. #19
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: LEN formula excludes line feed?

    No way I know of, sorry, but why is it important?

  20. #20
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    Thats because i am using this as a sort of validation in excel sheet, and use this formula in conditional formatting to highlight the cells crossing a character count limit There is a character length restriction in the production environment where i will be loading this data.

    Quote Originally Posted by shg View Post
    No way I know of, sorry, but why is it important?

  21. #21
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: LEN formula excludes line feed?

    I think they are already included.

    If I use: =RIGHT(A2,1), the character displayed is a full stop (.)
    If I use: =MID(A2,1596,1), the character displayed is a full stop (.)

    If I use: =COUNTIF(G2:G1600,CODE(10)), I get a count of 9.

    I also used: =CODE(MID($A$2,ROW(A1),1)) dragged down to extract each character and then filtered the values

    See the attached example for the analysis.

    Regards, TMS

  22. #22
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: LEN formula excludes line feed?

    In MS Word, the statistics are:

    Pages: 1
    Paragraphs: 1
    Lines: 19
    Words: 250
    Characters: 1344
    Characters (with spaces): 1596

    It's possible that the New Line is converted to Carriage Return/Line feed in Notepad++ but that's just a guess.

    Regards, TMS

  23. #23
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    @TMShucks thanks for all your effort in taking time and analysing this! Cheers man! In that case, it may also work vice versa i.e Carriage Return/Line Feed in Notepad++ are getting converted to New Line in excel.

    Quote Originally Posted by TMShucks View Post
    In MS Word, the statistics are:

    Pages: 1
    Paragraphs: 1
    Lines: 19
    Words: 250
    Characters: 1344
    Characters (with spaces): 1596

    It's possible that the New Line is converted to Carriage Return/Line feed in Notepad++ but that's just a guess.

    Regards, TMS

  24. #24
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: LEN formula excludes line feed?

    You're welcome. Thanks for the rep.


    I don't use Notepad++ so I can't offer an opinion. Another thought is that you get quotes added at the beginning and end because there are commas in the text and Notepad++ is trying to maintain the integrity of the cell contents.

    Interestingly, I only see 6 carriage returns (char(10)) in both Excel and Word yet when I did the COUNTIF in Excel it showed 9. Confusing.


    Regards, TMS

  25. #25
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    I think i figured it out. You were probably looking for this =COUNTIF(G3:G1598,10) instead of =COUNTIF(G3:G1598,CODE(10)).

    Quote Originally Posted by TMShucks View Post
    You're welcome. Thanks for the rep.


    I don't use Notepad++ so I can't offer an opinion. Another thought is that you get quotes added at the beginning and end because there are commas in the text and Notepad++ is trying to maintain the integrity of the cell contents.

    Interestingly, I only see 6 carriage returns (char(10)) in both Excel and Word yet when I did the COUNTIF in Excel it showed 9. Confusing.


    Regards, TMS

  26. #26
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    Code(10) gives 49 and it shows occurance of 1s in A2 which is 9.

    Quote Originally Posted by Pavan Renjal View Post
    I think i figured it out. You were probably looking for this =COUNTIF(G3:G1598,10) instead of =COUNTIF(G3:G1598,CODE(10)).

  27. #27
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: LEN formula excludes line feed?

    Dohhhhh ... thanks for the feedback. I obviously spent too much time looking at CHAR and CODE!


    regards, TMS

  28. #28
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    hahaha! but this was pretty neat! it helped me to analyze things in a better way!


    Quote Originally Posted by TMShucks View Post
    Dohhhhh ... thanks for the feedback. I obviously spent too much time looking at CHAR and CODE!


    regards, TMS

  29. #29
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: LEN formula excludes line feed?

    Glad to help. It was an interesting exercise.

    Regards, TMS

  30. #30
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    One quick observation i have made: I just have to add the total number of CHAR(10)s in text + excel character count. This will give me the total count i need. Just trying to figure out how i can retrieve the total number of char(10)s in text, then i can do a sum on both.

    Quote Originally Posted by TMShucks View Post
    Glad to help. It was an interesting exercise.

    Regards, TMS
    Last edited by Pavan Renjal; 01-06-2013 at 07:03 PM.

  31. #31
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: LEN formula excludes line feed?

    if you dont' mind a VBA UDF you can use this to get the count of char(10) :
    Please Login or Register  to view this content.
    To insert into your workbook, open VBA editor (Alt+F11),
    Insert -> Module
    Copy the Above code, then Paste into the new module,
    Save, Then Close Editor;

    your Formula would Look like this :
    A1: =LEN(A2)+CountOccurOf(CHAR(10),A2)

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  32. #32
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Thumbs up Re: LEN formula excludes line feed?

    Awesome stuff!!! Just what i needed! Works like a charm! Thanks a ton bro! Phew!

    Quote Originally Posted by dredwolf View Post
    if you dont' mind a VBA UDF you can use this to get the count of char(10) :
    Please Login or Register  to view this content.
    To insert into your workbook, open VBA editor (Alt+F11),
    Insert -> Module
    Copy the Above code, then Paste into the new module,
    Save, Then Close Editor;

    your Formula would Look like this :
    A1: =LEN(A2)+CountOccurOf(CHAR(10),A2)

    Hope this helps

  33. #33
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    Thanks a ton TMShucks and dredwolf! Thanks for everything! Brilliant stuff! Cheers!

  34. #34
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: LEN formula excludes line feed?

    To count the no of CHAR(10) in a string:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or in code.
    Please Login or Register  to view this content.

  35. #35
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: LEN formula excludes line feed?

    yup i like norie's


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  36. #36
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    Thanks a lot Norie! I liked the approach too!! Cheers!

    Quote Originally Posted by Norie View Post
    To count the no of CHAR(10) in a string:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or in code.
    Please Login or Register  to view this content.

  37. #37
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: LEN formula excludes line feed?

    Thanks vlady! Cheers!

    Quote Originally Posted by vlady View Post
    yup i like norie's


    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1