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

[DoctorK]

I've searched everywhere! The COUNTIF/COUNTA isn't what I need. The spreadsheet is like this:
example

[B]Column D                              Col N                           Col O[/B]
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:

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

 

[inactiveuserps07]

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

=LEN(TRIM(N36))

Or you could test for the space by putting this formula temporarily in O36:

CODE(N36)

If you get the number 32, then you've got a space instead of a null in your Column-N formula.

 

[DoctorK]

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

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

 =LEN(TRIM(N36)) 

I tried using

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.

 

[inactiveuserps07]

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