Find/Count Text

  • Thread starter Thread starter Adam Harris
  • Start date Start date
A

Adam Harris

Through judicious use of Find and Len, and many
calculating cells, it is possible to isolate recurring
instances of a text string in a cell. This is laborious
and sloppy.

Is there a simple solution, or a one cell formula, that
would count the number of instances of a certain text
string in a cell?

Thank you.
 
Hi Adam
if A1 stores your string and B1 the substring try
=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))/LEN(B1)

Frank
 
Hi Frank,

Thanks for that - but it needs a bit of qualification (I'm
not yet an expert!)

My way to date is:

A1 would contain the original text, B1 would be LEN, C1
would be FIND(text), D1 would be LEFT(B1-C1+1). The whole
performance would be run again, using D1 as the new A1,
and repeated for (say) ten calculations. Judicious use of
IF/THEN would separate the NULLS and repeats, where some
cells contain the text string fewer times than other cells.

As I say, sloppy and laborious.

What you propose is...I don't rightly understand. I would
be grateful for some insight to what it would do.

Thanks.
 
Hi Adam
I'll try to give you some explanations:
The formula
=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))/LEN(B1)
consists of the following parts (coming from the inner-most part):
1. SUBSTITUTE(A1,B1,""):
This replaces all occurences of your string B1 in A1 with "". e.g. if
A1="testthistest" and B1="test" the above formula will evaluate to
="this" (all 'test' substrings are 'deleted')
2. LEN(SUBSTITUTE(...))
calculate the lenght of the string withoout containing your substring.
In the above example this will evaluate to =LEN("this") = 4
3. LEN(A1)-LEN(SUBSTITUTE(...))
subtracting the lenght of the 'trimmed' substring from the original
string. For the above example this will evaluate to
=LEN("testthistest")-LEN("this")=12-4 = 8
So this will return the number of characters replaced by the SUBSTITUTE
4. Now just divide this result by the lenght of the substring:
=8/LEN("this") = 8/4 = 2

easy, isn't it :-)

HTH
Frank
 
Simplicity or pure genius - it's hard to decide, but it
really looks like the problem has been approached from a
molecular level, and then pieced together again.

Thank you.
 
Frank,

That formula does exactly what it says on the tin.
However, I obviously did not ask the question correctly,
and now I have two ways of looking at it. The problem is
having one or many escalation action logs in one cell. The
action may have been escalated and dated once, or many
times (but less than 10). I want/need to find that last
date/time:

A1 - 01/02/04 Escalated once
A2 - 01/02/04 Escalated once, 02/02/04 Escalated Twice
A3 - 01/02/04 Escalated once, 02/02/04 Escalated Twice,
03/02/04 Escalated a Third Time
A4 - 01/02/04 Escalated once

My original 'formula' would work if there was a way
to 'reverse' the contents of a cell:

A1 - adamisgood123
B1 - 321doogsimada

Doing it this way would mean that the last date/time,
however many there were, would be the first to be counted
(in reverse) and this could then be reversed again...

Alternatively, based on your solution, is there a way to
run a LEFT/RIGHT/LEN combination from the count/position
arrived at in your formula.

As I say, I'm not too good at this, but very willing to
learn and take advice.

Regards,

Adam.

p.s. You are welcome to reply to my e-mail address so as
not to clutter up the message boards.
 
Hi Adam
that is something completly different :-)
O.K. so you want to get the last date in a text. I make the following
assumptions for the formula:
- There are not other numbers besides your date numbers. Everything
else is text
- All dates are formated in the way you showed in your example. Thats
is MM/DD/YY
- A1 stores your text

Try the following:
1. Create a defined name (goto 'Insert - Name'). Use the name 'seq' and
assign the following formula:
=ROW(INDIRECT("1:1024"))

2. Now enter the following array formula (to be entered with
CTRL+SHIFT+ENTER):
=MID(A1,MAX(IF(ISNUMBER(--MID(A1,seq,1)),seq,0))-7,8))
gives the date as text
or if you want to have a real date try
=DATEVALUE(MID(A1,MAX(IF(ISNUMBER(--MID(A1,seq,1)),seq,0))-7,8)))
also entered as array formula

Frank
 
Sorry Frank,

Any chance you could hold my hand through this one, as
before?

Thanks,

Adam
 
Hi Adam
does this solution work?? If yes and I'll have a little bit time later
this evening I'll provide a shot explanation

Frank
 
Frank,

Unsure if it works as unsure what I am actually meant to
do. There is no hurry for an explanation, and your
patience is appreciated.

Thanks again,

Adam
 
Hi Adam
if you like, send me your file and I'll apply this formula for you. I
think then it's easier to unerstand

Frank
 
Back
Top