R
Regis
How can I put "Has text" into cell A1 if a range of cells (say B1:B5)
contain any text?
contain any text?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
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")
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
=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.
=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")
-----
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
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.
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.
J
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
data validation, custom, with a formula like =isnumber(a1)?
jim