Excel ISNUMBER function with formula examples, comments page 2

Comments page 2. Total comments: 63

Onye says:
2022-07-14 at 4:35 pm

=IF(ISNUMBER(SEARCH($B16,AF$3)),AF$1,"")

What do I need to fix in this formula, it is picking up on partial matches instead of an exact match
1. Alexander Trifuntov (Ablebits Team) says:
  2022-07-15 at 7:04 am
  
  Hello!
  To determine an exact match, use the methods in this guide: How to compare two cells in Excel.
  Please try the following formula:
  
  =IF($B16=AF$3,AF$1,””)
  
  or
  
  =IF(EXACT($B16,AF$3),AF$1,””)
  
  I hope it’ll be helpful.
jitu says:
2022-05-09 at 5:46 pm

mango code no.= 01
banana code no.= 02

in a cell alpha numeric no. R25690166 in which fifth and six digit number are code of either banana or mango.
which formula should I use to find out code.
1. Alexander Trifuntov (Ablebits Team) says:
  2022-05-10 at 9:44 am
  
  Hi!
  To extract the fifth and sixth digits from text, you can use the MID function.
  
  =MID(A1,5,2)
  
  I recommend reading this guide: Excel substring functions to extract text from cell.
Ryan says:
2022-04-22 at 9:44 pm

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!
1. Alexander Trifuntov (Ablebits Team) says:
  2022-04-25 at 10:04 am
  
  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.
kitty visser says:
2022-01-05 at 7:00 am

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.
1. Alexander Trifuntov (Ablebits Team) says:
  2022-01-05 at 10:30 am
  
  Hello!
  You have a mistake in your formula.
  
  =B1&IF(OR(ISNUMBER(FIND("TEXT",A1)),ISNUMBER(FIND("TEXT1",A1))),A2,"")
sandeep kelkar says:
2021-07-06 at 11:58 am

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))))))))))))))))))))))))))))))))
1. Roy says:
  2022-02-12 at 9:43 am
  
  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.
  1. Roy says:
    2022-02-13 at 2:21 am
    
    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

Comments on: Excel ISNUMBER function with formula examples

Comments page 2. Total comments: 63