I currently have a google sheets spreadsheet that pulls data from another sheet in the same workbook using the below formula in order to format the shift times:
=iferror(ARRAYFORMULA(MAP(Data!A1:H99,LAMBDA(x,IF(x="NO SHIFT",x,JOIN(" -"&Char(10),TEXT(SPLIT(x,"-"),"h:mm AM/PM")))))))
As part of the data, I have names & Shift totals in a single cell (this is how the data comes from my other source).
ex:
| Name | Times |
|---|---|
| Firstname Lastname24h/3h | 06:00 - 1600 |
| Firstname Lastname22h/3h | 06:00 - 1600 |
| Firstname Lastname20h/3h | 06:00 - 1600 |
etc.
I'd like to split the cell containing the name & hourly totals so there is a break (either a hyphen, line break, etc. so it appears like this:
| Name | Times |
|---|---|
| Firstname Lastname - 24h/3h | 06:00 - 1600 |
| Firstname Lastname - 22h/3h | 06:00 - 1600 |
| Firstname Lastname - 20h/3h | 06:00 - 1600 |
etc.
Is there a formula that can accomplish this; where / how would I integrate it into my existing formula?
Thank you!
Tried a couple of variations on TEXT(SPLIT(x,{1,2,3,4,5,6,7,8,9,0}) but because it target numbers, it affected the shift hours as well.
Assuming the table is in A:B, you can use:
=ARRAYFORMULA({REGEXREPLACE(A2:A4,"([A-Za-z])(\d)","$1 - $2"),B2:B4})
EDIT
=SORT(MAP(Data!A1:INDEX(Data!H:H,MAX(ROW(Data!A:A)*(Data!A:A<>""))),LAMBDA(x,IF(x="NO SHIFT",x,IF(COLUMN(x)=1,REGEXREPLACE(x,"([a-z])(\d)","$1 - $2"),JOIN(" -"&CHAR(10),TEXT(SPLIT(x,"-"),"h:mm AM/PM")))))),1,TRUE)