Recognising Number or Letters

[Steve 1962]

Hi

How do I get an IF statement to handle numbers differently to letters.

EG:

IF(A1=a number, then it does this......
IF(A1=a letter, then it does this.......

Thanks

 

[offthelip]

try this:

=IF(ISNUMBER(C1),"it is anumber",IF(ISTEXT(C1),"it is text","it is neither"))

 

[Steve 1962]

Thanks very much. Works fine.

 

[Steve 1962]

Hi

Just another -

Just need a formula that counts the values in column L (sequentially down the page) that have text, then return the answer in column K (values in column L are generated by a formula). If the cells in L are blank or have numbers, then return a blank.

Image attached.

Thanks

 

[BenMiller]

Put this array formula (confirm it with CTRL+ENTER) in K1 and drag down

=CHOOSE(ISTEXT(L1)*(L1<>"")+1,"",SUM(ISTEXT(L$1:L1)*(L$1:L1<>"")))

 

[Peter_SSs]

You could try this standard formula in K2, copied down.

=IF(COUNTIF(L2,"?*"),COUNTIF(L$2:L2,"?*"),"")

 

[Steve 1962]

Hi All

You people amaze me. You are so helpful and clever when it comes to this stuff. Keep up the great work and have a good week.

Thanks

 

[Steve 1962]

Just one further question to help me understand -

What is the significance of the "?*", in the formula ?

Thanks

 

[Peter_SSs]

You're welcome.

What is the significance of the "?*", in the formula ?

Both characters are wild cards. ? stands for a single text character. * stands for any number of characters, including none. If we left the ? out then the COUNTIF would count your formula results of "" as text cells, which you don't want. So the combination of ?* means "1 or more text characters"

So =IF(COUNTIF(L3,"?*"),COUNTIF(L$2:L3,"?*"),"") in words says

If L3 contains 1 or more text characters then count how many cells in column L from the top down to the formula cell contain 1 or more text characters else return ""

 

[Steve 1962]

Clearly explained - thanks Peter.