Need to look up text in a cell

  • Thread starter Thread starter Louise Taylor
  • Start date Start date
L

Louise Taylor

I need to find either a formula or a script that will find certain text in a
cell and then add a number to a counter based on that text.


What I have so far is something like:

=IF(cellA="text", (cellB+1), (""))

What I want is:

If cell A contains certain text, then add 1 to cell B, if not then do
nothing.

Thanks,

Paul
 
One way to experiment with ..

Iteration Settings
-------------------------
Click Tools > Options > Calculation tab
Check "Iteration"
Input a "1" in the "Max iterations" and "Max change" boxes
Click OK

Note: Calc mode is assumed set at default: Automatic

Now put in say, B1: =IF(TRIM(A1)="text",B1+1,B1)

1. Type "text" into A1, press Enter
2. B1 will return: 1

Clear A1, repeat step 1.
B1 will now return: 2

Now type something else other than "text" into A1
Nothing happens, B1 retains the previous value: 2
Click to expand...

And so on ..

See also JE's notes and caveats at :
http://www.mcgimpsey.com/excel/accumulator.html

Look for:
- Single cell accumulator
- Worksheet Function Accumulator (using Circular References)
 
Thanks, I play around with it.

Paul

Max said:
One way to experiment with ..

Iteration Settings
-------------------------
Click Tools > Options > Calculation tab
Check "Iteration"
Input a "1" in the "Max iterations" and "Max change" boxes
Click OK

Note: Calc mode is assumed set at default: Automatic

Now put in say, B1: =IF(TRIM(A1)="text",B1+1,B1)

1. Type "text" into A1, press Enter
2. B1 will return: 1

Clear A1, repeat step 1.
B1 will now return: 2

Now type something else other than "text" into A1

And so on ..

See also JE's notes and caveats at :
http://www.mcgimpsey.com/excel/accumulator.html

Look for:
- Single cell accumulator
- Worksheet Function Accumulator (using Circular References)

--
Rgds
Max
xl 97
Click to expand...
 
I tried this and it still was not giving me what I wanted, but in the
process of looking up the "trim" function I stumbled across other
definitions that got me to where I needed to be.

I finally ended up with this:

=IF(ISERROR(FIND("text", UPPER(A1))),(""),A10+1)

So I get, find the test in cell A1, make it all upper case so if someone
types the desired text in lower case it will still work. If the text is not
found, then the "FIND" function returns and error message. So, if the
function returns an error, do nothing, if it does not, then add 1 to a cell.
Then I can add up all the cells and get what I want!

A B
1 Bob Jones 1
2 bob Smith 1
3 Joe Somebody
4 Willie Gotcha
..
..
..
10

If I am looking for the text "Bob" in the A column, I copy the function in
the B column. I can use A10 as a blank cell reference and just add up the
numbers in column B to get my total number of "Bob"s.

I know it probably is not the most elegant way to do it, but it works for
me!
Thanks again Max.

Paul
 
Thanks for feedback, Paul
Glad that you found what you were after

Guess my interp made on your original post(s) was,
in retrospect quite off, sorry about that ..
(not the first time, probably won't be the last <g>)

... and that's why it's important to feedback
to those who respond to posts ..

Just a small comment on this formula:
=IF(ISERROR(FIND("text", UPPER(A1))),(""),A10+1)
Click to expand...

Think there's no need for the parens around
the value_if_true: "", so you could just use:
: =IF(ISERROR(FIND("text", UPPER(A1))),"",A10+1)

And one other alternative (maybe slightly neater)
you could try is:

Put in say, B1:
=SUMPRODUCT(--NOT(ISERROR(SEARCH(TRIM(C1),A1:A100))))

with the text to be found entered in C1

The above uses the non-case sensitive SEARCH,
and will return the total counts of cells in the range A1:A100
containing the text entered into C1

Just adjust the range A1:A100 to suit

but note that you can't use entire col refs (e.g.: A:A, B:B, etc)
in SUMPRODUCT
 
Yeah, I use the perens to keep my mind straight on what is going on. I'll
play around with your suggested formula and let you know how it works.

Thanks again!

Paul
 
Louise Taylor said:
Yeah, I use the perens to keep my mind straight on what is going on. I'll
play around with your suggested formula and let you know how it works.

Thanks again!

Paul

function
Click to expand...
 
Louise Taylor said:
Put in say, B1:
=SUMPRODUCT(--NOT(ISERROR(SEARCH(TRIM(C1),A1:A100))))
with the text to be found entered in C1
Click to expand...
I tweeked this a little after playing around with it and came up with:
=SUMPRODUCT(--NOT(ISERROR(SEARCH("text",A1:A100))))
Paul
Click to expand...

Glad you got it working, Paul
Thanks for the post back ..

It's really a matter of preference of course,
but I'd usually go for the "softcoded" version (as given)
as a first option <g>

(avoids having to amend the formula itself should
there be a number of different "text" to be found)
 
Back
Top