0

My spreadsheet has cells with lists of numbers as strings (representing items in another document) separated by commas. Here are the contents of one cell as an example:

1.1, 4.1, 5.2, 6.1, 6.2, 6.3, 7.10, 8.1, 8.2, 8.4, 11.1, 11.2, 11.3, 11.4, 11.5

I want to identify if, for example, the substring 1.3 appears in the cell.

Originally, I planned to use COUNTIF() with wildcard selectors around the substring I was searching for, like this:

COUNTIF(range, "*1.3*")

However, the value 11.3 causes this to return 1.

How can I identify 1.3 without picking up 11.3?

Some additional notes:

  • The first number in each cell is not preceded by a comma and space, so I can’t use that as part of my selector.
  • The last number in each cell is not succeeded by a comma
Improve this question

1 Answer 1

Reset to default
2

A quick and dirty solution would be to wrap both the range-value and the search-value in your delimiter:

=ISNUMBER(FIND(", 1.3, "; CONCATENATE(", "; range; ", ")))
Improve this answer
1
  • Wow, I hadn't considered that. Great idea. Unless someone else has a really clean solution, that seems like the way to go.
    – Andrew
    Commented Jan 11, 2021 at 20:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .