Hi there,
I have the following data that is exported as shown below in a single cell.
1) Summary - Y 2) R1 - Y two days 3) R2 - Y 4) R3 - Y 5) R4 Attached - Y on file 6) R5 Over - Y 7) R6 Submitted - N 😎 Calc - Y 9) SH - N TBC 10) Saved - Y 11) Delivery - Electronic 12) IMI - N 13) QMI - N 14) Pres. - Y 15) Elected - Y postal
I am trying to extract the single cell into 15 individual columns. I would like the data for each number listed that is between the "-" and the "subsequent number" to be in an individual column with the string before the hyphen deleted. The strings before the hyphen eg. "1) Summary -" or "2) R1 -" will always be the same but the string following the hyphen before the subsequent number will vary in length.
How I would ideally like to transform the data above into columns as follows:
Column 1: Y
Column 2: Y two days
Column 3: Y
Column 4: Y
Column 5: Y on file
Column 6: Y
Column 7: N
Column 8: Y
Column 9: N TBC
Column 10: Y
Column 11: Electronic
Column 12: N
Column 13: N
Column 14: Y
Column 15: Y
Not sure if there is a way to initially 'group' the string in the cell into sections such as "1) Summary - Y" "2) R1 - Y two days" to make this easier?! I'm pretty new to this so struggling how to do this using text to columns or RegEx tools so any suggestions or help would be very much appreciated!
Many thanks, Lou
PS. 8 ) is showing as the sunglasses emoji!Â
