The concept of the ISNUMBER function in Excel is very simple - it just checks whether a given value is a number or not. But the practical uses of the function go far beyond its basic concept, especially when combined with other functions within larger formulas. Continue reading
Comments on: Excel ISNUMBER function with formula examples
by Svetlana Cheusheva, updated on
Comments page 2. Total comments: 59
Hello!
Is it possible to use isnumber search on an entire column? Currently I am only able to obtain a true match when linking one cell to the other.
Example: I am looking for the number 123456 within a column that contains (John Smith - 0001234560).
As stated, if I link one cell to the correct cell, it works. If I link, one cell to a column - it returns false.
Thanks in advance!
Hi!
You didn't say what result you want. Here are some formulas.
=ISNUMBER(SEARCH("123456",A1:A100))
=SUM(--ISNUMBER(SEARCH("123456",A1:A100)))
If this is not what you wanted, please describe the problem in more detail.
is did posible why does it not work?
B1&IF(OR(ISNUMBER(FIND("TEXT",A1)),ISNUMBER(FIND("TEXT1",A1)),A2,"")
every cel is text i have asked this question some ware but maybe did and up in spam mail who i deleted.
Hello!
You have a mistake in your formula.
=B1&IF(OR(ISNUMBER(FIND("TEXT",A1)),ISNUMBER(FIND("TEXT1",A1))),A2,"")
I want to replace so many jitesh
pls advice
=IF(ISNUMBER(SEARCH("Drumstick PKM 1 50 gm",Q2)),2,IF(ISNUMBER(SEARCH("Bajra No",Q2)),3,IF(ISNUMBER(SEARCH("induce",Q2)),4,IF(ISNUMBER(SEARCH("pusa",Q2)),8,IF(ISNUMBER(SEARCH("ajay",Q2)),9,IF(ISNUMBER(SEARCH("Humic Acid Roota 1 kg",Q2)),10,IF(ISNUMBER(SEARCH("Humic Acid Roota 500 gm",Q2)),11,IF(ISNUMBER(SEARCH("Cucumber",Q2)),12,IF(ISNUMBER(SEARCH("930",Q2)),13,IF(ISNUMBER(SEARCH("latika",Q2)),14,IF(ISNUMBER(SEARCH("2338 250",Q2)),16,IF(ISNUMBER(SEARCH("lucky",Q2)),17,IF(ISNUMBER(SEARCH("bhim",Q2)),18,IF(ISNUMBER(SEARCH("karishma",Q2)),19,IF(ISNUMBER(SEARCH("BAIF",Q2)),21,IF(ISNUMBER(SEARCH("Arka",Q2)),22,IF(ISNUMBER(SEARCH("Alamdar 51",Q2)),23,IF(ISNUMBER(SEARCH("kajol",Q2)),25,IF(ISNUMBER(SEARCH("Bijankur",Q2)),28,IF(ISNUMBER(SEARCH("Auskelp 500 ml",Q2)),29,IF(ISNUMBER(SEARCH("Supplement Vimicon",Q2)),30,IF(ISNUMBER(SEARCH("Aspartical",Q2)),31,IF(ISNUMBER(SEARCH("Lucerne RL88",Q2)),33,IF(ISNUMBER(SEARCH("Okra 2338 100",Q2)),34,IF(ISNUMBER(SEARCH("Subabul",Q2)),35,IF(ISNUMBER(SEARCH("PKM 1 250",Q2)),36,IF(ISNUMBER(SEARCH("Shevari CMS 800",Q2)),37,IF(ISNUMBER(SEARCH("COFS 29 50",Q2)),38,IF(ISNUMBER(SEARCH("COFS 29 200",Q2)),39,IF(ISNUMBER(SEARCH("Agathi 400",Q2)),40,IF(ISNUMBER(SEARCH("Agathi 800",Q2)),41,IF(ISNUMBER(SEARCH("Shevari CMS 400",Q2)),42,0))))))))))))))))))))))))))))))))
sandeep kelkar, Try something like this.
Formula is in cell B1
{TEXTJOIN(", ", TRUE, IF(COUNTIF(A1, "*"&$C$1:$C$7&"*"), $C$1:$C$7, ""))}
A B C
1 I'm looking for Humic Acid Roota 1 kg Humic Acid Roota 1 kg Drumstick PKM 1 50 gm
2 Bajra No
3 pusa
4 ajay
5 Humic Acid Roota 1 kg
6 Humic Acid Roota 500 gm
7 Cucumber
NOTE 1 - You need to press Ctrl + Shift + Enter keys together to get the first result, and then drag the fill handle down to the cells that you want to apply this formula,
NOTE 2 - This formula is only available in Excel 2019 and above.
Good luck.
sandeep kelkar, Please note the info above did not translate properly. The data in the cells should be as follows:
Cell A1 = I’m looking for Humic Acid Roota 1 kg
Cell B1 = Contains the TEXTJOIN formula
Cell C1:C7 Contains the data table containing the following data:
Drumstick PKM 1 50 gm
Bajra No
pusa
ajay
Humic Acid Roota 1 kg
Humic Acid Roota 500gm
Cucumber
I hope this clarifies the situation.
Roy