Count length of text characters in a cell that has a formula

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Count length of text characters in a cell that has a formula

  1. #1
    New Member
    Join Date
    Dec 2016
    Location
    Seattle
    Posts
    11
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Count length of text characters in a cell that has a formula

     
    I've searched everywhere! The COUNTIF/COUNTA isn't what I need. The spreadsheet is like this:
    example
    Code:
    Column D                              Col N                           Col O
    Name/Description              External calling name                   Char Count
    Bobby                                   Bobby                           5
    Column N has a formula to return Col D's value if Column B has 10 numbers in it:
    example:
    Code:
    =IF(LEN($B36)=10,$N$34,"")
    This works great and it returns the proper char count.

    However, whenever there isn't a name to return, Column O returns a '1' when the cell is blank.

    What I'm looking for is for it to return nothing at all and I can't seem to find why it's returning a '1'.


    Thank you for your time.

  2. #2
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    732
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count length of text characters in a cell that has a formula

    @DoctorK, I'm not sure why the formula you listed refers to row 36 in Column B, but row 34 in Column N. Typo?

    At any rate, I'm guessing you've got a blank between quotes in N, rather than a true null (i.e., quotation marks one right after the other with no space between them).

    You can test for this in O (assuming we're both talking about row 36):

    Code:
    =LEN(TRIM(N36))
    Or you could test for the space by putting this formula temporarily in O36:

    Code:
    CODE(N36)
    If you get the number 32, then you've got a space instead of a null in your Column-N formula.

  3. #3
    New Member
    Join Date
    Dec 2016
    Location
    Seattle
    Posts
    11
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count length of text characters in a cell that has a formula

    Referring to row 36 in Column B, basically states that if there is a 10 digit number in the B column, to populate the description name from another cell - in which case it's N34. Otherwise leave blank.

    All of col N from N36 on down has this formula in it.
    The O col is just the char count and states that I only want to count the chars in the cell of the formula.

    At first I tried using
    Code:
    =IF(N37<>"","",(LEN(N37)))
    but, it doesn't return the len of the cell if it is populated.

    The TRIM function doesn't omit the '1' if the cell is blank.
    Code:
     =LEN(TRIM(N36)) 


    I tried using
    Code:
    CODE(N36)

    and I get the value of 48 when there is nothing in N36

    It's not such a big deal, because it does count the number of chars correctly if there is something in the cell. I'm just puzzled as to why it counts '1' when there isn't anything in the cell but the formula.



  4. #4
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    732
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count length of text characters in a cell that has a formula

      
    If you're getting a 48 on CODE(), Excel sees an actual zero there, not a null.

    You can always use a workaround:

    =IF(CODE(N36) > 48,LEN(N36),"")

    And the reason your IF() formula above produced no results is that you reversed the TRUE and FALSE clauses. You basically said, "If that cell isn't blank, make this one blank. If it IS blank, tell me the length of that blank cell." You wanted:

    =IF(N36<>"",LEN(N36),"")

    or

    =IF(N36="","",LEN(N36))

User Tag List

Tags for this Thread

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
  •  

 

 
DMCA.com