Extract cell content into separate columns

[estephenkim]

I've got a data set of 6,133 rows with cells of dates in the following format:

Cell A1:
Status A-2022-02-03;
Status B-2022-03-11;
Status C-2021-09-10

This is an example of the content in a given cell and I want to separate the statuses and their respective dates into separate columns (by using as delimiters all semicolons and only the first dash of each status). As an illustration, I'd like to extract the cell content to a single row as in the following:

Cell B1: Status A

Cell C1: 2022-02-03

Cell D1: Status B

Cell E1: 2022-03-11

Cell F1: Status C

Cell G1: 2021-09-10

I've tried using Text-to-Columns and some of the Excel text functions, but I could use some help on this. I'd like to identify a procedure to reformat the statuses and dates from 6,133 rows of data using an easy to apply, non-VBA procedure that maintains data integrity and minimizes errors. Any suggestions would be welcome!

Stephen

 

[estephenkim]

Seeking something like the following (click image to expand):

 

[Rick Rothstein]

I've got a data set of 6,133 rows with cells of dates in the following format:

Cell A1:
Status A-2022-02-03;
Status B-2022-03-11;
Status C-2021-09-10

This is an example of the content in a given cell and I want to separate the statuses and their respective dates into separate columns (by using as delimiters all semicolons and only the first dash of each status). As an illustration, I'd like to extract the cell content to a single row as in the following:

Cell B1: Status A

Cell C1: 2022-02-03

Cell D1: Status B

Cell E1: 2022-03-11

Cell F1: Status C

Cell G1: 2021-09-10

I've tried using Text-to-Columns and some of the Excel text functions, but I could use some help on this. I'd like to identify a procedure to reformat the statuses and dates from 6,133 rows of data using an easy to apply, non-VBA procedure that maintains data integrity and minimizes errors. Any suggestions would be welcome!

Stephen

Give this formula a try...

=TEXTSPLIT(TEXTJOIN("/",,SUBSTITUTE(TEXTSPLIT(A1,";"),"-","/",1)),"/")

 

[estephenkim]

That formula worked well, Rick! Thanks so much!