If cell contains any text
The picture above shows different values in column B and a formula in column C that tries to identifies the value in column B.
Formula in cell C3:
The formula above checks if a cell contains a text value based on whether Excel correctly identified and formatted the cell as a text value or not.
This works often quite well, however, sometimes numbers are formatted as text.
This can happen if you import data from a database, copy and paste values from the web or a formula that returns numbers that Excel handles as a text string, among other things.
For example, cell B10 has a number formatted as a text value and the ISTEXT function incorrectly identifies the number as a text value.
The following formula will correctly identify numbers even if Excel identifies the number as a text value.
Formula in cell C3:
Explaining formula in cell C10
The formula in cell C10 is
B10*1 returns a number if B10 contains a number and an error for anything else.
13*1 = 13
ISNUMBER(B10*1) returns TRUE if the argument is a number and FALSE for all else.
ISNUMBER(B10*1) returns TRUE.
The NOT function returns TRUE if FALSE and FALSE if TRUE.
NOT(ISNUMBER(B10*1))
becomes
NOT(TRUE) and returns FALSE.
The ISTEXT function returns TRUE for all text values.
ISTEXT(B10) returns TRUE.
Multiplying the two functions is the same as AND logic. I could use the AND function, however, the * (asterisk) is smaller.
ISTEXT(B10)*NOT(ISNUMBER(B10*1))
becomes
TRUE*FALSE and returns 0. 1*0 = 0
The IF function then returns the third argument.
IF(ISTEXT(B10)*NOT(ISNUMBER(B10*1)),"Text","Not text")
becomes
IF(0,"Text","Not text")
and returns "Not text" in cell C10.
Get Excel *.xlsx file
If cell contains any text.xlsx
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and […]
Extract unique distinct values if value contains string
The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]
Sum cells containing numbers and text based on a condition
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
How to use the ISNUMBER function
The ISNUMBER function checks if a value is a number, returns TRUE or FALSE. Formula in cell D3: =ISNUMBER(B3) Excel […]
Extract unique distinct values if adjacent cell is text
Question: I want a unique distinct list to be created from a column where an adjacent column has text cell […]
How to use the ISTEXT function
The ISTEXT function returns TRUE if value is text. Formula in cell C3: =ISTEXT(B3) Excel Function Syntax ISTEXT(value) Arguments value […]
If cell contains text from list
This article demonstrates several ways to check if a cell contains a value based on a list. The first example […]
Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are […]
IF with AND function – multiple conditions
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
