How to find exact number in cell with lots of text and numbers

[vh]

I have a table with 2 columns. Column A has a cash amount and column B has a description of the source of the cash. For example:

   A         B
1  $4        User108 May 2017
2  $6        User 201 April 2017
3  $9        May 2017 User 302
4  $0        205 May 2017

In cell D1 I wrote a formula to detect the row for user 201 in column B. For reasons I won’t go into for the sake of time, I decided not to use an array and, instead, repeat the formula several times down column D, in case user 201 were to appear more than once. The formula goes like this:

=IF(ISNUMBER(SEARCH(201,B1)),ROW(B1),"Not found")

The problem is, the formula gives a row number result in all of the cells from row 1 to row 4. In other words, the formula is detecting the number 201 in ALL the cells even though user 201 appears only in row 2.

The reason why the formula is detecting 201 in all cells is because it thinks the number 2017 satisfies the condition for the number 201, as 201 are the first 3 digits of 2017. I tried using regular expressions to isolate 201 only, but so far I have not been able to detect ONLY the cell for user 201 and to ignore the cells with 2017.

So what can I do to detect only 201 and ignore 2017?

And no, I can’t break up the B column into user and date. That’s how I got the spreadsheet and it would take me forever to break up the column. Besides, as you can see in B3, sometimes I get the date first and user second, which would make it even more difficult to break up. Also, the information is always entered with errors, like B1 with “User108” without a space to separate the word “user” and the number.

I tried using EXACT too, but that also fails because EXACT compares against the entire content of the cell.

I guess the solution I’m looking for is how to find 201 without the formula thinking it found it in 2017, but with enough flexibility to find it anywhere else even if there’s no space between the number and a word.

[mikekaganski]

You may use regex:

=IF(ISNUMBER(SEARCH("(^|[^[:digit:]])201([^[:digit:]]|$)",B1)),ROW(B1),"Not found")

and make sure that regular expressions are used:

Tools-Options-LibreOffice Calc-Calculate-General Calculations-Enable regular expressions in formulas

[vh]

It worked! (Sorry, originally I responded it didn’t work because I copied part of the formula incorrectly.) Thanks! Your formula worked under all possible scenarios for the number. Question, though: would it work using a cell number as a referece? That is, E4 instead of 201? I’m going to give it a shot and report back.

UPDATE: Didn’t work with cell reference.

[Lupp]

[^[:digit:]]* needed (zero or more non-digits).
This applies if it’s assured that the 201 to serach for is the first number included.

[mikekaganski]

[^[:digit:]]* matches anything, because 2017 may be treated like 201 plus zero non-digits plus 7

@vh: To use cell references, you should construct the regex using something like
"(^|[^[:digit:]])" & E4 &"([^[:digit:]]|$)"

[vh]

Lupp: I tried the formula with 2017 first and 201 second and it still works. It also works with 201user user201 and other combinations. But if another digit it attached, like 2010, it will ignore it, which is exactly what I was hoping to achieve.

[vh]

Mike: I modified the formula for cell reference with your recommendation and it worked. Thanks!

[Lupp]

A not so widely known but rather clear way to get a search as needed here is to use a negative lookbehind assertion in front of the positive series of digits to search for, and a negative lookahead assertion behind it like in
=SEARCH("(?<![0-9])201(?![0-9])";$B1) .
The negative assertions here also accept the ‘StartOfEverything’ and the ‘EndOfEvrything’ represented by ^ and $ respectively in search expressions. Whatever matched the assertions is considered zero-length with respect to the position returned. You will get a number (position) for a match and a #VALUE! error if no match is found. The tests by ISNUMBER() or NOT(ISERROR()) will work as expected.

The best compendium about RegEx I know is http://www.regular-expressions.info/.

[robleyd]

If there is always a space between 201 and the following text, try

SEARCH("201 ",B1)

(note the trailing space) for the search part of your formula.

If this answer helped you, please accept it by clicking the check mark to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.

[vh]

Unfortunately the number sometimes has a space, sometimes is a the end, and sometimes is at the beginning. And sometimes it even has text immediately after. But it’s a good idea, though. It might narrow down the chances of finding the number. Mike Kaganski’s answer worked under all possible scenarios except with cell reference, though.