Determine if a range has text

[Regis]

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

 

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

 

[JP]

How about

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

Ctrl-Shift-Enter as an array formula

 

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

 

[Regis]

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

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

 

[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

 

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

 

[Jim]

if you're just out to prevent text entries in specific cells, could you use
data validation, custom, with a formula like =isnumber(a1)?

jim