1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by JCTalk, Feb 13, 2019.

  1. JCTalk

    JCTalk Member

    Messages:
    100
    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.
  2. AlanSidman

    AlanSidman Active Member

    Messages:
    445
    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.

    Attached Files:

  3. vletm

    vletm Excel Ninja

    Messages:
    4,793
    JCTalk
    If B-column has those text then ...
    =LEFT(B2,10) & MID(B2,20,99)
    and copy down
  4. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    647
    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.
  5. azumi

    azumi Active Member

    Messages:
    226
    Hope this help

    Attached Files:

  6. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,147
    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: Feb 14, 2019 at 10:14 AM
  7. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    647
    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!

    Attached Files:

  8. vletm

    vletm Excel Ninja

    Messages:
    4,793
    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?
  9. AlanSidman

    AlanSidman Active Member

    Messages:
    445
    Several solutions but no acknowledgement from the OP. So sad :(
    Peter Bartholomew likes this.

Share This Page