Identifying text vs. a number in a cell question

Topic

New Reply

I have a number of cell formulas that depend upon seeing numbers in other cells. On occasion I’ll have a reason to type a word into one of those other cells instead of a number and it makes any associated macros report an error while running.

I’d like to add a qualifier to the front of the formulas so that if either text, or no number, is present then the formula is ignored.

Something just like:

=IF(A1=””,””)

but instead of just having a set parenthesis indicating a result if there is an empty cell there would be a qualifier that either specifies if text is present then no cell input or if a number is present then the formula continues.

I suspect this is rather straight forward I just have never run into it before.

Thanks,
BH

Reply | Quote

Replies

Is the issue within a macro or on the sheet itself? ISNUMBER(cell) will return true or false depending on the cells format.

Reply | Quote

Is the issue within a macro or on the sheet itself? ISNUMBER(cell) will return true or false depending on the cells format.

The issue is within the sheet……..not the macro.

I use ISNUMBER quite a lot but for specific text. Is there a way to use it for “any text”?

Thanks,
BH

Reply | Quote

I’m not clear to me what you mean by “any text.”

This is what I understand from your post: =IF(isnumber(cell), SOME_FORMULA,””) where if the cell does not contain a number, the result will be a blank. Change the “” to whatever you want to be there if the cell does not contain a number.

Maybe post a sample file?!

Reply | Quote

I’m not clear to me what you mean by “any text.”

This is what I understand from your post: =IF(isnumber(cell), SOME_FORMULA,””) where if the cell does not contain a number, the result will be a blank. Change the “” to whatever you want to be there if the cell does not contain a number.

Maybe post a sample file?!

Thanks for bearing with me on this.

Unfortunately a sample file isn’t going to help here.

I have complex sheets with a lot of cross references between cells. Various cells react different ways depending upon what is in other cells.

All these cells depend upon number references. If one cell has 25 entered for example, and the one next to it has 35 entered, then several other cells will return specific number results based upon those entries. If the numbers go up or down in those first two cells then the formulas in the other cells with make different calculations and give different results.

The occasional problem is that every now and then I’ll need to enter text in one of those first 2 cells so the person using the print out of all this will get some textual information. This is when an error is returned in some of those other cells…………..because the formulas in those cells are reacting to numbers…..not text.

The text that could be typed in occasionally could be any word or words so I can’t just specify a single word or group of words that will result in the cell with the text being ignored.

I hope I’ve explained this a little more clearly now.

Thanks,
BH

Reply | Quote

BH,

You mean something like this:

45255-check-for-numbers

Formula: [noparse]=IF(AND(ISNUMBER(C1),ISNUMBER(D1),ISNUMBER(E1)),C1+D1+E1,0)[/noparse]

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

BH,

You mean something like this:

45255-check-for-numbers

Formula: [noparse]=IF(AND(ISNUMBER(C1),ISNUMBER(D1),ISNUMBER(E1)),C1+D1+E1,0)[/noparse]

HTH :cheers:

Well, that got me thinking a little more. Maybe an example would help.

Here is the formula from from cell O27:

=IF(ISNUMBER(SEARCH(“NON”,C14)),0,IF(OR(ISNUMBER(SEARCH(“jamb”,J16)), ISNUMBER(SEARCH(“strip”,J16)), C14=”STRAIGHTS”,C16=”MDF”,C16=”PVC”),0,IF(C14=”SERPENTINE”,0,IF(C12=””,0,IF(C14=”ELLIPTICAL”,J12+3,IF(C14=”OVAL”,J12+3,ROUND(AD20*16,0)/16))))))

You’ll see J12+3 referenced near the end of this formula. That is referencing the width of a part. Normally I will enter 3, 3 1/2, 4 etc as the width of the part but every now and then I want to enter something like “any width” in J12 for information on the printout. In that case I’d want cell O27 to remain blank or 0, but instead it returns an errror since J12 does not contain a number. And since other cells act in relation to what is in O27 I end up with errors in numerous places around the worksheet.

Helpful?
BH

Reply | Quote

BH,

A simpler solution might be to just do this [noparse]=IFError(YourRegularFormulaHere,ErrorValueHere)[/noparse]

45256-check-for-numbers

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG’s is simple. “IFERROR” was a nice function added to recent releases.

Also, suppose you replaced the two instances of “J12+3” with if(isnumber(J12),J12+3,0)

Reply | Quote

RG’s is simple. “IFERROR” was a nice function added to recent releases.

Also, suppose you replaced the two instances of “J12+3” with if(isnumber(J12),J12+3,0)

Thanks everyone. I’ll give both suggestions a try.
BH

Reply | Quote

Hi BH

re:Identifying text vs. a number in a cell question
..perhaps you just need the =ISTEXT( function ???

zeddy

Reply | Quote

Hi BH

re:Identifying text vs. a number in a cell question
..perhaps you just need the =ISTEXT( function ???

zeddy

That would be awesome if I could get it to work. I tried this at the beginning of a cell formula to no avail. Obviously I’m doing something wrong as it didn’t return a blank cell. That is the same format I use on an ISNUMBER(SEARCH) function with a search phrase added so it was of course my first thought.

=IF(ISTEXT (J12),””,IF(ISNUMBER(SEARCH(“NON”,C14)),0,IF(OR(ISNUMBER(SEARCH(“jamb”,J16)), ISNUMBER(SEARCH(“strip”,J16)), C14=”STRAIGHTS”,C16=”MDF”,C16=”PVC”),0,IF(C14=”SERPENTINE”,0,IF(C12=””,0,IF(C14=”ELLIPTICAL”,J12+3,IF(C14=”OVAL”,J12+3,ROUND(AD20*16,0)/16)))))))

BH

Reply | Quote

Hi BH

copy this and try it in cell [O27]

Code:

=IF(NOT(ISNUMBER(J12)),"",IF(ISNUMBER(SEARCH("NON",C14)),0,IF(OR(ISNUMBER(SEARCH("jamb",J16)), ISNUMBER(SEARCH("strip",J16)), C14="STRAIGHTS",C16="MDF",C16="PVC"),0,IF(C14="SERPENTINE",0,IF(C12="",0,IF(C14="ELLIPTICAL",J12+3,IF(C14="OVAL",J12+3,ROUND(AD20*16,0)/16)))))))

..let us know if this works?

zeddy

Reply | Quote

Hi BH

copy this and try it in cell [O27]

Code:

=IF(NOT(ISNUMBER(J12)),"",IF(ISNUMBER(SEARCH("NON",C14)),0,IF(OR(ISNUMBER(SEARCH("jamb",J16)), ISNUMBER(SEARCH("strip",J16)), C14="STRAIGHTS",C16="MDF",C16="PVC"),0,IF(C14="SERPENTINE",0,IF(C12="",0,IF(C14="ELLIPTICAL",J12+3,IF(C14="OVAL",J12+3,ROUND(AD20*16,0)/16)))))))

..let us know if this works?

zeddy

Thanks, I’ll give it a try in the morning.
BH

Reply | Quote

bh,

In Post 12, try removing the space between ISTEXT and (J12)

TH,
Maud

Reply | Quote

bh,

In Post 12, try removing the space between ISTEXT and (J12)

TH,
Maud

THANKS !!
That would appear to have solved the problem.
BH

Reply | Quote

Hi BH

You should be aware that you could get different results if you use

=IF(ISTEXT(J12),””,..
rather than
=IF(NOT(ISNUMBER(J12)),””,..

There are other ‘spacing issues’ in your post#12 formula.
For example, look closely at the post#12 and you will see
IF(C14=”SER PENTINE”,
and
,I F(C14=”OVAL”,
..but these may be just an issue with the post rather than what you actually have in your sheet.

When you post formulas on this forum, you can ‘wrap’ the formula with Code tags to preserve exact copies of the formulas.

Now, the main reason I suggested the
=IF(NOT(ISNUMBER(J12)),””,
..is that if the cell [J12] is empty, this will give a blank result in the formula cell [O27]
But if you use the
=IF(ISTEXT(J12),””,..
..then an empty [J12] cell with a cell [C14] = “OVAL” will give a result of 3 in cell [O27]

These are the functions you can use..
=N(addr)
=ISTEXT(addr)
=ISNUMBER(addr)
=ISNONTEXT(addr)
=NOT(ISNUMBER(addr))
etc etc etc

The first one, =N(J12) will return whatever numeric value is in cell [J12], or a zero value if the cell is empty or contains text.
I use that one often.

zeddy

Reply | Quote

Zeddy,

Interesting! This caused me to wonder so…

45276-TruthTable

The results for a blank cell (A3) are interesting. IsNumber returns False and Not(IsNumber) returns True which would seem to indicate that it is not a number yet when divided by 2 it returns 0 not a #VALUE! error as the Text values do in rows 5-7.

It would appear that we have what I’ll refer to as a Schrödinger’s Blank!
Schrödinger’s cat

So use the table above and be careful you get the results you want.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

bhdavis,

Here are a couple of additional alternatives you might consider.

————————————————————-
Using your formula, combine all the conditions that evaluate to zero with an OR statement as well as those that evaluate to J12+3: (highlighted in blue)

From

Code:

=IF(ISTEXT(J12),””,IF(ISNUMBER(SEARCH(“NON”,C14)),0,IF(OR(ISNUMBER(SEARCH(“jamb”,J16)), ISNUMBER(SEARCH(“strip”,J16)), C14=”STRAIGHTS”,C16=”MDF”,C16=”PVC”),0,IF(C14=”SERPENTINE”,0,IF(C12=””,0,IF(C14=”ELLIPTICAL”,J12+3,IF(C14=”OVAL”,J12+3,ROUND(AD20*16,0)/16)))))))

To

Code:

=IF(ISTEXT(J12),””,IF(OR([COLOR=”#0000CD”]ISNUMBER(SEARCH(“NON”,C14)),ISNUMBER(SEARCH(“jamb”,J16)),ISNUMBER(SEARCH(“strip”,J16)),C14=”STRAIGHTS”,C16=”MDF”,C16=”PVC”,C14=”SERPENTINE”,C12=””[/COLOR]),0,IF(OR([COLOR=”#0000CD”]C14=”ELLIPTICAL”,C14=”OVAL”[/COLOR]),J12+3,ROUND(AD20*16,0)/16)))

The modified formula is shorter, with less nested IF statements, and easier to maintain

————————————————————–
The second alternative is a user defined function (UDF):

In a standard module:

Code:

Public Function Qualify()
Application.Volatile
[COLOR=”#008000″]’———————————
‘SET VARIABLES[/COLOR]
C12 = Range(“C12”)
C14 = Range(“C14”)
C16 = Range(“C16”)
J12 = Range(“J12”)
J16 = Range(“J16”)
AD20 = Range(“AD20”)
Non = InStr(1, C14, “NON”, 1)
Jamb = InStr(1, J16, “jamb”, 1)
Strip = InStr(1, J16, “strip”, 1)
[COLOR=”#008000″]’———————————
‘EVALUATE QUALIFY[/COLOR]
If WorksheetFunction.IsText(J12) Then
    Qualify = “”
ElseIf Non > 0 Or Jamb > 0 Or Strip > 0 Or _
    UCase(C14) = “STRAIGHTS” Or UCase(C16) = “MDF” Or _
    UCase(C16) = “PVC” Or UCase(C14) = “SERPENTINE” Or C12 = “” Then
    Qualify = 0
ElseIf UCase(C14) = “ELLIPTICAL” Or UCase(C14) = “OVAL” Then
    Qualify = J12 + 3
Else: Qualify = Round(AD20 * 16, 0) / 16
End If
End Function

In the cell that you want the result, enter =Qualify()
—————————————————————–
I have tested your formula with the modified formula and the UDF side-by-side. They all produce the same results for all scenarios

HTH,
Maud

Reply | Quote