I've been asked to try to find a formula that will look for a duplicate of a number in one cell against a cell that has a "range" of numbers. For ex:I am looking to see if this number is duplicated"7105+65" (land tract # ) and another cell has "7105+60 - 7206+10". So the number 7105+65 is duplicated in the range given. is there any way to do a formula in a blank column to highlight or capture this duplication in some way without having to enter all of the tract numbers in the range?

Is the range written in the same cell or it's in 2 near cells? If it's all in the same cell, are all the numbers made of a 4 digits number, a + and a 2 digits number? At the moment I think you should use the "separate datas in column" feature to have all numbers in different columns (7105, 60, 7206, 10) and then check if a specified number (7105, 65) is within the cells written.

Is the range written in the same cell or it's in 2 near cells? The range is within one cell If it's all in the same cell, are all the numbers made of a 4 digits number, a + and a 2 digits number?the number is a land tract # or also called a station number and they are written as 7105+65 At the moment I think you should use the "separate datas in column" feature to have all numbers in different columns (7105, 60, 7206, 10) and then check if a specified number (7105, 65) is within the cells written. - I think dividing them up like that in columns would not be feasible since multiple tract numbers end in 65, 10, 15 etc. Each of those numbers would be called out as a duplicate when in fact it's not the number. The full number I need to look must include the 4 digits with a plus sign and the two digits.I am currently manually changing the cells with a range by inserting additional rows to put in the tract numbers that are in the range into individual cells. thanks so much for your quick response - much appreciated!

Hi Denise, Other words you'd like to find if the string in the cell is part of another strings in the columns cells. The question is you compare the cell or parts of the cells, i.e. 7105+65-8888+15 and 9999+10-7105+65 shall match or that's not the case, only 7105+65 and 9999+10-7105+65 shall work?

Thanks for the quick response. I am trying to find out if one "tract" number that is one cell by itself can look in another cell to seeExample:This number is in the cell shown (A5)7105+65 ex: (column A - Row 5) And in a another cell (C12) is this range (group) of tract numbers9999+10-7105+65 ex: (column C - Row 12) So, I am trying to find a formula that will recognize that in C12 is a range (group) of numbers and would look to find if the number in A5 is within that range (group) of numbers. I know this is very confusing. I apologize for that. Thanks for your help!

Hi Denise, If simply to check A5 is within C12 or not when =IF(ISNUMBER(SEARCH(A5,C12)),"In range", "Not in range") If you have set of numbers in column A and different ranges in column C to find if every of A is within any of C will be bit more complex, but better to know how exactly your data are organized. If something like this better to have small Excel file sample.

Possible to use formula? | Microsoft Community Hub

13 Replies

Arul Tresoldi
Iron Contributor
Jun 15, 2018
Is the range written in the same cell or it's in 2 near cells?

If it's all in the same cell, are all the numbers made of a 4 digits number, a + and a 2 digits number?

At the moment I think you should use the "separate datas in column" feature to have all numbers in different columns (7105, 60, 7206, 10) and then check if a specified number (7105, 65) is within the cells written.
- Deleted
  Jun 16, 2018
  Is the range written in the same cell or it's in 2 near cells? The range is within one cell
  
  If it's all in the same cell, are all the numbers made of a 4 digits number, a + and a 2 digits number?
  the number is a land tract # or also called a station number and they are written as 7105+65
  
  At the moment I think you should use the "separate datas in column" feature to have all numbers in different columns (7105, 60, 7206, 10) and then check if a specified number (7105, 65) is within the cells written. -
  
  I think dividing them up like that in columns would not be feasible since multiple tract numbers end in 65, 10, 15 etc. Each of those numbers would be called out as a duplicate when in fact it's not the number. The full number I need to look must include the 4 digits with a plus sign and the two digits.
  I am currently manually changing the cells with a range by inserting additional rows to put in the tract numbers that are in the range into individual cells. thanks so much for your quick response - much appreciated!
  - SergeiBaklan
    Diamond Contributor
    Jun 16, 2018
    Hi Denise,
    
    Other words you'd like to find if the string in the cell is part of another strings in the columns cells. The question is you compare the cell or parts of the cells, i.e.
    
    7105+65-8888+15 and 9999+10-7105+65
    
    shall match or that's not the case, only
    
    7105+65 and 9999+10-7105+65
    
    shall work?

Forum Discussion

Possible to use formula?