Pergunte aqui

Perfil de vh - atividade

2017-12-06 10:16:07 +0200 Medalha recebida  Pergunta famosa (fonte)
2017-07-27 11:49:19 +0200 Medalha recebida  Pergunta notável (fonte)
2017-05-05 13:13:16 +0200 Medalha recebida  Pergunta popular (fonte)
2017-05-05 07:57:43 +0200 Resposta comentada How to find exact number in cell with lots of text and numbers

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

2017-05-05 07:56:39 +0200 Resposta comentada How to find exact number in cell with lots of text and numbers

Lupp: I tried the formula with 2017 first and 201 second and it still works. It also works with 201user user201 and othe

2017-05-05 07:39:05 +0200 Resposta comentada How to find exact number in cell with lots of text and numbers

Unfortunately the number sometimes has a space, sometimes is a the end, and sometimes is at the beginning. And sometimes

2017-05-05 07:35:13 +0200 Resposta comentada How to find exact number in cell with lots of text and numbers

It worked! (Sorry, originally I responded it didn't work because I copied part of the formula incorrectly.) Thanks! Your

2017-05-05 07:34:17 +0200 Resposta comentada How to find exact number in cell with lots of text and numbers

It worked! (Sorry, originally I responded it didn't work because I copied part of the formula incorrectly.) Thanks! Your

2017-05-05 07:32:41 +0200 Marcada como a melhor resposta How to find exact number in cell with lots of text and numbers

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.

2017-05-05 07:32:41 +0200 Medalha recebida  Acadêmico (fonte)
2017-05-05 07:32:29 +0200 Resposta comentada How to find exact number in cell with lots of text and numbers

Unfortunately the number sometimes has a space, sometimes is a the end, and sometimes is at the beginning. And sometimes

2017-05-05 07:30:11 +0200 Resposta comentada How to find exact number in cell with lots of text and numbers

It worked! (Sorry, originally I responded it didn't work because I copied part of the formula incorrectly.) Thanks!

2017-05-05 07:28:31 +0200 Resposta comentada How to find exact number in cell with lots of text and numbers

Didn't work. It gave me a "Not found". Even with the number 201 alone with nothing else it could not detect it.

2017-05-05 07:25:02 +0200 Resposta comentada How to find exact number in cell with lots of text and numbers

Unfortunately the number sometimes has a space, sometimes is a the end, and sometimes is at the beginning. And sometimes

2017-05-05 07:06:10 +0200 Medalha recebida  Pergunta famosa (fonte)
2017-05-05 05:05:17 +0200 Perguntou How to find exact number in cell with lots of text and numbers

How to find exact number in cell with lots of text and numbers I have a table with 2 columns. Column A has a cash amount

2017-03-04 19:40:05 +0200 Resposta comentada How can I find specific decimals using array formula?

The 1 is the first row with the correct answer? Can I change it to 2, 3, 4, etc?

2017-03-04 19:38:19 +0200 Resposta comentada How can I find specific decimals using array formula?

Lupp: I read your update. You're right. I might run into trouble with negative numbers. However, for this project I know for a fact that there will never be negative numbers, as I'm evaluating payments. I only get a VALUE! error if the cel being evaluated is empty, but that's fine. I can fix that. Still, for those who might need a similar solution for negatives, your formula might be the answer.

2017-03-04 03:44:51 +0200 Resposta comentada How can I find specific decimals using array formula?

Oh, and by the way; for those who don't know, TRUNC removes the decimals from a number. Thus, the following formula:

=TRUNC(500.34)

Will return 500 as a whole number. Likewise, =TRUNC(B3) , in which B3 is 500.34 will generate the same result.

So if I add the decimal I'm looking for to a TRUNC number, the result should be the number with the decimal I'm looking for. By evaluating the number with math it solves the problem of finding specific decimals without confusing them with .31, etc.

2017-03-04 03:40:22 +0200 Resposta comentada How can I find specific decimals using array formula?

Your comments were of great help, however. In order to find this solution I followed your tip of using an algebraic evaluation to find the decimals instead of using SEARCH, which is intended for text. So I do appreciate your comments. Thanks!

2017-03-04 03:37:37 +0200 Resposta comentada How can I find specific decimals using array formula?

I understand. But I needed a solution WITHOUT having to convert anything to text. And I found it (using the original columns and rows):

{=SMALL(IF(TRUNC($A$3:$A$15)+$B$3=$A$3:$A$15,ROW($A$3:$A$15),""),1)}

That did the trick. If I enter 0.1, 0.2, 0.3, etc in B3, the formula will give the row number of ONLY the cells with the specific decimal. That way if A3 has 100.30, A4 has 100.32 and A5 has 100.35, the formula will only pick A3 for the row number and it will not confuse it with .32 etc.

2017-03-04 03:31:56 +0200 Resposta comentada How can I find specific decimals using array formula?

Sounds reasonable. I found a solution using a math formula adding the specific decimal to TRUNC. See the solution in my updated response.

2017-03-04 03:29:08 +0200 Medalha recebida  Editor (fonte)
2017-03-04 02:50:34 +0200 Resposta comentada How can I find specific decimals using array formula?

I understand your logic, but it would be impossible for me change the numbers in the array to text without causing problems elsewhere in the spreadsheet. In the actual spreadsheet the numbers in column D are constantly being updated and there's no time to change them to text. That is why I need to figure out a formula to find numbers with specific decimals without having to rely on changing the numbers to text manually.

2017-03-04 02:27:49 +0200 Medalha recebida  Palpiteiro
2017-03-04 02:08:49 +0200 Resposta comentada How can I find specific decimals using array formula?

Oops! I forgot to add the curly brackets to the formula. The formula should be:

{=SMALL(IF($D$2:$D$100=$AG$2,ROW($D$2:$D$100),""),1)}

The brackets, of course, are added by hitting CTRL-SHIFT-ENTER when entering the array formula.

Again, this formula will find whole numbers, not numbers with decimals using a wildcard for the decimal. See my original question to understand what I mean by this.

2017-03-04 02:01:53 +0200 Pergunta comentada How can I find specific decimals using array formula?

If I use SEARCH for .0.2 the formula will return any number from .20 to .29. I'm trying to narrow it to just .20. I will try with FIND, but I doubt the result will be different, as FIND applies to uppercase and lowercase.

2017-03-04 01:59:31 +0200 Pergunta comentada How can I find specific decimals using array formula?

The first point is a wildcard. When you add a dot to a number or to text when doing a search, it means you are looking for any content that has that number or text. Example: if you have 34.31 in A1 and ext.31 in A2, if you search for ..31 you will get A1 and A2 as a correct answer. The . is like the asterisk. It's a wildcard.

2017-03-04 01:56:59 +0200 Respondeu uma pergunta How can I find specific decimals using array formula?

SOLVED AT LAST!

{=SMALL(IF(TRUNC($D$2:$D$100)+$B$1=$D$2:$D$100,ROW($D$2:$D$100),""),1)}

In which B1 contains the decimal to find in the array. The result of the formula is the row number of the cell containing any number with the decimal .30, but not .31, .32, .33, etc.

Prior to finding the solution for decimals I found a solution for finding whole numbers:

After much struggle I figured out a formula to find whole numbers.

{=SMALL(IF($D$2:$D$100=$AG$2,ROW($D$2:$D$100),""),1)}

In which D2:D100 is the column to be searched, and AG2 contains the value to look for. In this case the whole number 1200.

By repeating the formula as an array, and changing the 1 at the end for 2, 3, 4, etc, I find the next row number containing the number 1200. This formula will not confuse 1200 with 1200.45, 1200.11 etc.

Now I have to figure out a similar formula to find the row number of cells that contain decimal numbers such as 0.1, 0.2, 0.3, etc. For example, find the row number if a cell contains the number 1200.20 but not the number 1200.21. (UPDATE: I solved it. See edit and formula above.)

So if you are still interested in helping me out with this, I would really appreciate it. (UPDATE: If you have a different solution to the one I found I'd like to see it too.)

2017-03-04 01:00:35 +0200 Pergunta comentada How can I find specific decimals using array formula?

In short, I'm doing a SEARCH for NUMBERS. SEARCH doesn't do the trick because it's intended for text. So I need something similar to SEARCH that can be used for numbers.

2017-03-04 00:57:27 +0200 Pergunta comentada How can I find specific decimals using array formula?

By the way: .0.2 on B3 means I was looking for any number that contained the decimal 0.2 using B3 as reference for the search.

2017-03-04 00:56:34 +0200 Pergunta comentada How can I find specific decimals using array formula?

I'm trying to find the row number in a column that matches a specific number (in this case 1200) without having the search result mixed up with 1200.45, 1200.11, etc. And I'm trying to achieve this using an array formula. All I need is something that will find the correct match of 1200. If SEARCH doesn't work, then what?

2017-03-03 23:01:53 +0200 Pergunta comentada How can I find specific decimals using array formula?

Also: I need to do this without having to reformat the contents of D column as text. That is simply not possible for the data I will be using.

2017-03-03 23:01:10 +0200 Pergunta comentada How can I find specific decimals using array formula?

I know SEARCH applies to text. What can I use instead of SEARCH for look for an exact number? See my response to karolus. Basically I need an array formula to find an exact value. If in D I have 1200.45, 1200.11 and 1200, what can I use to find ONLY 1200 and not 1200.45 or 1200.11?

2017-03-03 22:59:45 +0200 Resposta comentada How can I find specific decimals using array formula?

Didn't work. What I need is a formula to find the correct number in an array. Let's say that in column D I have 1200.45, 1200.11, and 1200, and I only want to find out the row number of the cell with 1200. I need to do this using an array formula:

=SMALL(IF(ISNUMBER(SEARCH(1200,$D$2:$D$302)),ROW($D$2:$D$302),""),1)

The problem with that formula is it will return as a match ANY number with 1200 (1200.45, 1200.11, etc), not just 1200. I understand SERCH applies to text. What can I use instead?

2017-03-03 22:36:44 +0200 Medalha recebida  Pergunta notável (fonte)
2017-02-25 16:27:00 +0200 Medalha recebida  Pergunta popular (fonte)
2017-02-25 01:51:49 +0200 Perguntou How can I find specific decimals using array formula?

On column A3:A15 I have a series of numbers with decimals. 10.2, 13.21, 45.26 and so on. I wrote the following formula:

=SMALL(IF(ISNUMBER(SEARCH(B3,A3:A15)),ROW(A3:A15),""),1)

By typing a decimal in B3, such as ..21, it will give me the row number for the cell with 13.21. But if I type ..20 it will find nothing. If I switch it to .0.2 it will find the row number for the cell with 10.2. But if change the formula to this:

=SMALL(IF(ISNUMBER(SEARCH(B3,A3:A15)),ROW(A3:A15),""),2)

and I type .0.2 on B3, it will give me the row number for 13.21.

What can I type on B3 so it can inly detect numbers with .2 but not .21, .22, .23, etc?