Hi Guys,
I'm putting together something that relies on being able to count occurrences of specific text. The only problem is, the data contains a nasty text timestamp between what I'm trying to count.
Example data
In each cell, there are basically loads of appended log notes like this..
03-02-2019 18:00:44 - Jiminy Cricket (Cricket Brigade)
04-02-2019 19:18:26 - Jiminy Cricket (Cricket Brigade)
04-02-2019 11:33:54 - Mister Geppetto (Cricket Brigade)
05-02-2019 15:12:03 - Jiminy Cricket (Cricket Brigade)
I'm looking for e.g. 05-02-2019 - Jiminy Cricket (Cricket Brigade) though so I'm looking for a way to remove those text timestamps. Preferably in one find & replace go. But as the times are different I'm finding it tricky.
Alternatively is there a better way of doing the count so it ignores everything between the date and the name.
I'm currently using this which would work fine if it weren't for that timestamp.
=SUMPRODUCT((LEN(Range)-LEN(SUBSTITUTE(Range,"05-02-2019 - Jiminy Cricket (Cricket Brigade)","")))/LEN("05-02-2019 - Jiminy Cricket (Cricket Brigade)"))
Any ideas on how I can achieve this?
Thank you.
I'm putting together something that relies on being able to count occurrences of specific text. The only problem is, the data contains a nasty text timestamp between what I'm trying to count.
Example data
In each cell, there are basically loads of appended log notes like this..
03-02-2019 18:00:44 - Jiminy Cricket (Cricket Brigade)
04-02-2019 19:18:26 - Jiminy Cricket (Cricket Brigade)
04-02-2019 11:33:54 - Mister Geppetto (Cricket Brigade)
05-02-2019 15:12:03 - Jiminy Cricket (Cricket Brigade)
I'm looking for e.g. 05-02-2019 - Jiminy Cricket (Cricket Brigade) though so I'm looking for a way to remove those text timestamps. Preferably in one find & replace go. But as the times are different I'm finding it tricky.
Alternatively is there a better way of doing the count so it ignores everything between the date and the name.
I'm currently using this which would work fine if it weren't for that timestamp.
=SUMPRODUCT((LEN(Range)-LEN(SUBSTITUTE(Range,"05-02-2019 - Jiminy Cricket (Cricket Brigade)","")))/LEN("05-02-2019 - Jiminy Cricket (Cricket Brigade)"))
Any ideas on how I can achieve this?
Thank you.