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

### Download Excel *.xlsx file

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Lookup with multiple criteria and return multiple search results

Question: How do I search a list containing First name column and a last name column? I want to search […]

Filter unique distinct values if value contains specific string [Formula and Advanced Filter]

Table of contents Filter unique distinct values using a "contain" condition Filter unique distinct values using a "contain" condition (Advanced […]

Filter unique distinct values, sorted and blanks removed

Table of Contents Filter unique distinct values, sorted and blanks removed Filter duplicate values, sorted and blanks removed EEK asks: […]

Count unique distinct text values in a range

Array formula in D14: =SUM(IF(FREQUENCY(COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")), COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")))>0, 1, 0)) + CTRL + SHIFT + ENTER […]

Create a unique distinct list where a corresponding column has text cell values

Question: I want a unique list to be created from a column where an adjacent column has text cell value? […]

Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are […]

If cell contains multiple values

The array formula in cell C3 checks if text string in B3 contains all values in F2:F3. Related articles If […]

If cell equals value from list

The array formula in cell C3 checks if cell B3 is equal to any of the values in E3:E5, the […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form