Need a Sheets formula to extract four parts (Date, Start Time, End Time, Note) from one cell and place them into cells E–H.
Example:
let sourceText = "dDate: 03/15/2021, sTime: 8AM, eTime: 4PM, note: Meeting";
// Expected output: ["03/15/2021", "8AM", "4PM", "Meeting"]
I encountered a similar challenge and found that using a combination of SUBSTITUTE and SPLIT worked reliably. I first replaced the separating commas with a unique delimiter and then applied SPLIT to create an array of the segments. Once you have the segments, further adjustments like TRIM and text extraction functions can remove the initial labels from each part. This process, while requiring a few extra steps compared to a regular expression approach, offers flexibility when the text structure is guaranteed. It ended up being a simple solution once you set up the delimiter replacement correctly.
hey, u could try chaining regexextract with trim. e.g: =trim(regexextract(A1, ‘Date:\s*([^,]+)’)) for each segment. works well if the input format stays the same. gives neat outputs without extra steps, but might need tweaks if labels vary.
hey, try regexextract on your cell. for ex: =regexextract(A1, “Date: ([^,]+)”) and then adjust for each part. its flexible enough. good luck!
I found an effective solution by using a mix of REGEXREPLACE to clean up the labels and then applying SPLIT to separate the individual parts. What I did was first remove the text labels like ‘Date:’, ‘sTime:’, etc. from the original cell so that only the values remain, separated by commas. Once that was done, I used the SPLIT function to divide the text based on the comma delimiter. This method is particularly helpful when the text format stays consistent. I’ve found it to be a more straightforward approach for my sheets.