...

In Excel, identify data layers correctly and convert them to a standardized table

 

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

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.

Picture1png

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.

Source:https://www.reddit.com/r/excel/comments/1d8djn0/namedatetime_in_single_column/


 
 •  109 View  •