MID function + not null?

[tmurph802]

Hi All,

I'm processing camera data on a citizen science platform where each image has to be classified three times before being retired. In this instance, I want to know which are retired, and which are not. Those that are not retired are listed as null in a string, and those that are have a long string associated with them, including the date and time of retirement. Below are two examples of what I'm looking at:

Not Retired = {"28529900":{"retired":null,"Filename":"Camera2_20181024_182250.jpg"}}
Retired = {"28533011":{"retired":{"id":27561903,"workflow_id":8150,"classifications_count":3,"created_at":"2018-12-10T21:22:07.253Z","updated_at":"2019-01-17T20:16:36.524Z","retired_at":"2019-01-17T20:16:36.511Z","subject_id":28533011,"retirement_reason":"classification_count"},"Filename":"Camera4_20180930_194749.jpg"}}

I tried the MID function to extract the first four characters after "retired" (in red in the string above), which gave me null and {"id. If the image is retired, I want to see the time it was pulled (in orange in the above string). My thought is "if not null, then the slice of string with retired time". Is there an easier, or more logical way to do that? I also tried LOOKUP and VLOOKUP but couldn't smooth it out to avoid errors.

Any tips or ideas would be greatly appreciated!

 

[Eric W]

Welcome to the Board.

How about something like:

=IFERROR(MID(A1,SEARCH("retired_at",A1)+13,24),"not retired")

 

[tmurph802]

Perfect! I omitted the time so turned the 24 into 10, and will group them for pivot tables. Thank you!

 

[Eric W]

Glad to help!

 

[tmurph802]

As it turns out, grouping those dates in a pivot table gives me a "cannot group selection error", even when the "not retired" was changed to a " ". Any workarounds that come to mind?

 

[Eric W]

I don't know a lot about pivot tables, but it could be that you're having a hard time grouping the dates since the formula returns a text value. You can convert it to a "real" Excel date like this:

=IFERROR(MID(A1,SEARCH("retired_at",A1)+13,24)+0,"not retired")

then format the cell as a date.