In Excel, Identify Data Layers Correctly and Convert Them to a Standardized Table

Problem description & analysis:

Data in the column below has three layers: the 1st layer is a string, the 2nd layer is a date, and the 3rd layer contains multiple time values:

     A

1   NAME1

2   2024-06-03

3   04:06:12

4   04:09:23

5   08:09:23

6   12:09:23

7   17:02:23

8   2024-06-02

9   04:06:12

10  04:09:23

11  08:09:23

12  NAME2

13  2024-06-03

14  04:06:12

15  04:09:23

16  2024-06-02

17  12:09:23

18  17:02:23

We need to identify the three layers of data correctly and convert them to a standardized table:

      D         E          F

1   NAME1   2024-06-03  04:06:12

2   NAME1   2024-06-03  04:09:23

3   NAME1   2024-06-03  08:09:23

4   NAME1   2024-06-03  12:09:23

5   NAME1   2024-06-03  17:02:23

6   NAME1   2024-06-02  04:06:12

7   NAME1   2024-06-02  04:09:23

8   NAME1   2024-06-02  08:09:23

9   NAME2   2024-06-03  04:06:12

10  NAME2   2024-06-03  04:09:23

11  NAME2   2024-06-02  12:09:23

12  NAME2   2024-06-02  17:02:23

Solution:

Use SPL XLL to type in the following formula:

=spl("=E@1(?).(if(ifstring(~):s=~, if(ifdate(E(~))):d=~; [s,d,~])).select(ifa(~))",A1:A18)

SPL returns an integer for the date data. You need to format it into an easy-to-read form through Excel’s "format cells" option (or through SPL’s E() function). Use the same way to handle the time data.

As shown in the picture below:

Explanation:

E()function converts a value to the Excel date/time data; E@1 converts a multilayer sequence to a single-layer one. ~ represents the current member; if() function judges whether it is a string and whether it is a date from left to right and executes the expressions, and then executes the default expression. ifa() judges whether the variable is a sequence.