Counting char help

[Mikeice]

I currently have a cell with formula
=IF(E10="","","Characters")

So it returns the number of characters input in cell -

=IF(B11="","",IF(B11>255,"PLEASE GO BACK TO E10 and
PUT IN AN ALT ENTER",""))

What I want is either the amount of characters between the Alt Enter
or if the alt enters are inserted and the text is less than 255 betwee
those returns that the message doesn't appear.

is that possibel?

:confused

 

[KL]

Hi Mikeice,

Try this ARRAY formula (has to be confirmed by Ctrl+Shift+Enter, not just
Enter):

=IF(OR((LARGE((MID(A1&CHAR(10),ROW(INDIRECT("1:"&LEN(A1)+1)),1)=CHAR(10))*ROW(INDIRECT("1:"&LEN(A1)+1)),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1)))-LARGE((MID(A1&CHAR(10),ROW(INDIRECT("1:"&LEN(A1)+1)),1)=CHAR(10))*ROW(INDIRECT("1:"&LEN(A1)+1)),1+ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))))>255),"PLEASE
GO BACK TO "&CELL("address",A1)&" and PUT IN AN ALT ENTER","")

Unfortunately, adding another IF, which would check if A1 is empty, would
excede the 7 levels of functions nesting. So if you really need that you
could use two cells say:

(this one is array entered)
[B1]
=OR((LARGE((MID(A1&CHAR(10),ROW(INDIRECT("1:"&LEN(A1)+1)),1)=CHAR(10))*ROW(INDIRECT("1:"&LEN(A1)+1)),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1)))-LARGE((MID(A1&CHAR(10),ROW(INDIRECT("1:"&LEN(A1)+1)),1)=CHAR(10))*ROW(INDIRECT("1:"&LEN(A1)+1)),1+ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))))>255)

[C1] =IF(ISERROR(B1],"",IF(B1,"PLEASE GO BACK TO "&CELL("address",A1)&" and
PUT IN AN ALT ENTER",""))

Regards,
KL

 

[Mikeice]

THanks for that but all I get is an error #NUM
What am I doing wrong?

 

[KL]

You are probably not confirming the formula with Ctrl+Shift+Enter

KL

 

[Mikeice]

Stupid me!
I was using the tick and tehn alt enter

Thanks so much guys!


You all really ROCK!!!

thanks

 

[KL]

Hi,

Just not to keep this in the box - Hector Migel Orozco Dias, an Excel MVP,
who, I believe, mainly contributes in the Spanish NG has proposed an
enhancement to the formula which is greatly appreciated:

1) define a name [e.g. CRLF] with the formula =CHAR(10)
2) in the main formula substitute all ocurrences of the funcion CHAR by the
defined name
3) now you can nest another IF...:

=IF(A1="","",IF(OR((LARGE((MID(A1&CRLF,ROW(INDIRECT("1:"&LEN(A1)+1)),1)=CRLF)*ROW(INDIRECT("1:"&LEN(A1)+1)),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,CRLF,""))+1)))-LARGE((MID(A1&CRLF,ROW(INDIRECT("1:"&LEN(A1)+1)),1)=CRLF)*ROW(INDIRECT("1:"&LEN(A1)+1)),1+ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,CRLF,""))+1))))>255),"PLEASE
GO BACK TO "&CELL("address",A1)&" and PUT IN AN ALT ENTER",""))

Regards,
KL