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

So it returns the number of characters input in cell -

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

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

is that possibel?




--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=393600

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:"&L EN(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+RO W(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+RO W(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" wrote in
message ...

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

So it returns the number of characters input in cell -

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

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

is that possibel?




--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile:
http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=393600

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


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=393600

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

KL


"Mikeice" wrote in
message ...

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


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile:
http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=393600

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

Thanks so much guys!


You all really ROCK!!!

thanks :) :)


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=393600

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(INDIREC T("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(INDIRE CT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,CRLF,""))+1))))255),"PLEASE
GO BACK TO "&CELL("address",A1)&" and PUT IN AN ALT ENTER",""))

Regards,
KL


"Mikeice" wrote in
message ...

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

Thanks so much guys!


You all really ROCK!!!

thanks :) :)


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile:
http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=393600