LinkBack URL
About LinkBacks
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.
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.
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.
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
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.
Originally Posted by TMShucks
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?
How are you trying to limit the cell to 1000 characters?
@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.
Can you attach a workbook that exhibits LEN giving incorrect results?
PS Why so many characters in one cell?
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
Can you give examples?
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?
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.
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.
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?
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?
Notepad++ counts the carriage returns, the quotes, and the trailing line feed. That's the 10-character difference.
Entia non sunt multiplicanda sine necessitate
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?
I just hope If i can achieve this somehow, this will eliminate a major part of my problem!
No way I know of, sorry, but why is it important?
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
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
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
@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.
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
I think i figured it out. You were probably looking for this =COUNTIF(G3:G1598,10) instead of =COUNTIF(G3:G1598,CODE(10)).
Code(10) gives 49 and it shows occurance of 1s in A2 which is 9.
Dohhhhh ... thanks for the feedback. I obviously spent too much time looking at CHAR and CODE!
regards, TMS
hahaha! but this was pretty neat! it helped me to analyze things in a better way!
Glad to help. It was an interesting exercise.
Regards, TMS
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.
Last edited by Pavan Renjal; 01-06-2013 at 07:03 PM.
if you dont' mind a VBA UDF you can use this to get the count of char(10) :
To insert into your workbook, open VBA editor (Alt+F11),
Function CountOccurOf(SrchStr As String, StrVal As String, Optional StrtPos, Optional CaseSensitive) As Long Dim LCount, Count As Long If IsMissing(StrtPos) Then StrtPos = 1 If IsMissing(CaseSensitive) Then CaseSensitive = True CountOccurOf = 0 If StrtPos > Len(StrVal) Then Exit Function If Len(SrchStr) = 0 Or Len(StrVal) = 0 Then Exit Function Count = 0 For LCount = 0 + StrtPos To Len(StrVal) If CaseSensitive Then If Mid(StrVal, LCount, Len(SrchStr)) = SrchStr Then Count = Count + 1 Else If UCase(Mid(StrVal, LCount, Len(SrchStr))) = UCase(SrchStr) Then Count = Count + 1 End If Next LCount CountOccurOf = Count End Function
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
Re: LEN formula excludes line feed?
Awesome stuff!!! Just what i needed! Works like a charm! Thanks a ton bro! Phew!
if you dont' mind a VBA UDF you can use this to get the count of char(10) :
To insert into your workbook, open VBA editor (Alt+F11),Function CountOccurOf(SrchStr As String, StrVal As String, Optional StrtPos, Optional CaseSensitive) As Long Dim LCount, Count As Long If IsMissing(StrtPos) Then StrtPos = 1 If IsMissing(CaseSensitive) Then CaseSensitive = True CountOccurOf = 0 If StrtPos > Len(StrVal) Then Exit Function If Len(SrchStr) = 0 Or Len(StrVal) = 0 Then Exit Function Count = 0 For LCount = 0 + StrtPos To Len(StrVal) If CaseSensitive Then If Mid(StrVal, LCount, Len(SrchStr)) = SrchStr Then Count = Count + 1 Else If UCase(Mid(StrVal, LCount, Len(SrchStr))) = UCase(SrchStr) Then Count = Count + 1 End If Next LCount CountOccurOf = Count End Function
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
Thanks a ton TMShucks and dredwolf! Thanks for everything! Brilliant stuff! Cheers!
To count the no of CHAR(10) in a string:
Formula:
=LEN(A2)-LEN(SUBSTITUTE(A2, CHAR(10),""))
Or in code.
NoChar10 = Len(Range("A2"))-Len(Replace(Range("A2"), Chr(10),""))
Thanks a lot Norie! I liked the approach too!! Cheers!
To count the no of CHAR(10) in a string:
Formula:
=LEN(A2)-LEN(SUBSTITUTE(A2, CHAR(10),""))
Or in code.
NoChar10 = Len(Range("A2"))-Len(Replace(Range("A2"), Chr(10),""))
yup i like norie's
Formula:=Len(A2) + LEN(A2) -LEN(SUBSTITUTE(A2, CHAR(10), ""))
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
Thanks vlady! Cheers!
yup i like norie's
Formula:=Len(A2) + LEN(A2) -LEN(SUBSTITUTE(A2, CHAR(10), ""))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks