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.