Determine if a range has text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Regis - 31 Oct 2007 02:00 GMT

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

Reply to this Message

Dave Peterson - 31 Oct 2007 02:24 GMT

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

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

Signature

Dave Peterson

Reply to this Message

JP - 31 Oct 2007 02:40 GMT

How about

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

Ctrl-Shift-Enter as an array formula

> If you want to determine if any of those cells has anything in it (text,
> numbers, formulas), you can use:
[quoted text clipped - 9 lines]
>
> Dave Peterson

Reply to this Message

Dave Peterson - 31 Oct 2007 04:07 GMT

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.

> How about
>
[quoted text clipped - 15 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Regis - 31 Oct 2007 04:32 GMT

JP <jpena@sterlingtesting.com> wrote in news:1193794831.444658.229980
@d55g2000hsg.googlegroups.com:

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

Reply to this Message

MartinW - 31 Oct 2007 08:45 GMT

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

> JP <jpena@sterlingtesting.com> wrote in news:1193794831.444658.229980
> @d55g2000hsg.googlegroups.com:
[quoted text clipped - 8 lines]
> 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.

Reply to this Message

Dave Peterson - 31 Oct 2007 13:24 GMT

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.

> JP <jpena@sterlingtesting.com> wrote in news:1193794831.444658.229980
> @d55g2000hsg.googlegroups.com:
[quoted text clipped - 7 lines]
> 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.

Signature

Dave Peterson

Reply to this Message

Jim - 01 Nov 2007 00:38 GMT

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

Reply to this Message

Rate this thread: