Determine if a range has text

R

Regis

How can I put "Has text" into cell A1 if a range of cells (say B1:B5)
contain any text?
 
D

Dave Peterson

If you want to determine if any of those cells has anything in it (text,
numbers, formulas), you can use:

=counta(b1:b5)>0
or
=if(counta(b1:b5)>0,"something there","it's all empty")
 
J

JP

How about

=IF(SUM(IF(ISTEXT(B1:B5),1,0)),"Has text","NO TEXT")

Ctrl-Shift-Enter as an array formula
 
D

Dave Peterson

Alternatively, to count the cells with text:

=SUMPRODUCT(--(ISTEXT(B1:B5)))
or
=if(SUMPRODUCT(--(ISTEXT(B1:B5)))>0,"has text","no text")
(without the array formula)

or
=IF(OR(ISTEXT(B1:B5)),"has text","no text")
as an array formula.
 
R

Regis

=IF(SUM(IF(ISTEXT(B1:B5),1,0)),"Has text","NO TEXT")
Click to expand...

-----
I believe ISTEXT works on only the first cell (B1), right? There are issues
with COUNT and COUNTA where both text and numbers are in B1:B5.

What I'm after is preventing text being put into the cells. Only numbers
should be in B1:B5. If text is put in A1 should light up.
 
M

MartinW

Hi Regis,

Maybe this,

Type Has Text in A1, then set the font color to white to hide the text.
Then set conditional formatting in A1 with Formula is =OR(ISTEXT(B1:B5))
Click the format button and set the font color to black and OK out.

HTH
Martin
 
D

Dave Peterson

Make sure you array enter that formula (ctrl-shift-enter).

And if you highlight istext(b1:b5) in the formula bar, then hit F9, you'll see
an array of true/falses.

Hit escape to discard the last change (or ctrl-z) if you want the formula back.
 
Top