• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Remove all XX:XX:XX from cell text

JCTalk

Member
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.
 
Using Power Query, this can be accomplished quickly.
Load your data to PQ
Split the column of data at the first space
Split the second column you just created at the dash
Remove the Time value column
Merge the two columns remaining
Load back to your worksheet

Attached is file demonstrating same.
 

Attachments

  • Book5.xlsx
    17.8 KB · Views: 6
If 'findArray' is the two item row
{05-02-2019, "Jiminy Cricket"}
and 'inText' is a relative reference to your list of strings to be searched, then SEARCH gives an array comprising two results and
= AND( ISNUMBER( SEARCH(findArray, inText) ) )
gives TRUE or FALSE depending upon whether both elements are present.
 
To count data with criteria without timestamp in between.

upload_2019-2-14_9-3-1.png

If the data have the same date/time format, try..................

Put the criteria "05-02-2019 - Jiminy Cricket (Cricket Brigade)" in cell C1

In C2, enter formula :

=SUMPRODUCT(0+(REPLACE(A1:A5,11,9,"")=C1))

Regards
Bosco
 
Last edited:
I missed the fact that it is only the total count that is requested. For that to work 'inText' has to be the whole list. Then use MMULT which is unusual in that it is capable of aggregating a 2D array by row or by column.

Rather than going for the entire calculation as one step I define an intermediate step 'containsArray?' as a named formula that evaluates to give a Boolean
= MMULT( 1 - ISNUMBER( SEARCH(findArray, inText) ), {1;1} ) = 0
(this implements an or condition; 'if either test fails')

From there it remains only coerce the Booleans to integers [0,1] and add
= SUM( N( containsArray? ) )

On a machine that is not running Excel 365 insider that would need to be entered with CSE …
… or you could use SUMPRODUCT to hide the array nature of the calculation,
… or you could include the N() in the definition of 'containsArray?' so that the final calculation
= SUM( containsArray? )
no longer requires CSE.

The other option is to use a helper range with the previous formula. If one displays an icon set in place of the [0,1] it almost looks intentional!
 

Attachments

  • Multiple tests.xlsx
    16.2 KB · Views: 2
JCTalk
Wasn't You the original case
Remove all XX:XX:XX from cell text
Alternatively is there a better way of doing the count so it ignores everything between the date and the name.
and that was all?
 
Back
Top