Excel单元格格式化：提取时间片段并规范为指定格式显示

Got it, let's break down how to get those clean time ranges from your messy strings. Depending on your Excel version and how your data is structured, here are a few solid methods:

Method 1: Using Modern Excel Functions (365/2021+) #

If you have Excel 365 or 2021, dynamic array functions make this task straightforward.

Case 1: Each entry is in its own cell #

Suppose cell A1 contains one entry like SER_T_L1 04-06-2018 8:00-04-06-2018 12:00. Use this formula in another cell to get the formatted time range:

=TEXT(TEXTBEFORE(TEXTSPLIT(A1, " ")[3], "-")*1, "hh:mm") & " - " & TEXT(TEXTSPLIT(A1, " ")[4]*1, "hh:mm")

• TEXTSPLIT(A1, " ") splits the string into parts by spaces. The 3rd part is 8:00-04-06-2018, and the 4th is 12:00.
• TEXTBEFORE(..., "-") grabs just the time before the dash in the 3rd part.
• Multiplying by 1 converts the time string to a numeric value, so TEXT(..., "hh:mm") can format it with leading zeros (like 08:00 instead of 8:00).

Case 2: All entries are in one cell #

If your entire input string is in a single cell (like your example), use this formula to get all time ranges separated by spaces:

=TEXTJOIN(" ", TRUE, BYROW(FILTER("SER_"&TEXTSPLIT(A1, " SER_"), "SER_"&TEXTSPLIT(A1, " SER_")<>""), LAMBDA(x, TEXT(TEXTBEFORE(TEXTSPLIT(x, " ")[3], "-")*1, "hh:mm") & " - " & TEXT(TEXTSPLIT(x, " ")[4]*1, "hh:mm"))))

• TEXTSPLIT(A1, " SER_") splits the big string into individual entry fragments. We prepend SER_ back to each fragment to rebuild the full entries.
• BYROW processes each entry with the same logic as Case 1, and TEXTJOIN combines all results into one string separated by spaces.

Method 2: Using Legacy Excel Functions (Pre-365) #

If you're using an older Excel version without dynamic arrays, use this longer but reliable formula for single-entry cells:

=TEXT(MID(A1, FIND(" ", A1, FIND(" ", A1)+1)+1, FIND("-", A1, FIND(" ", A1, FIND(" ", A1)+1)+1) - FIND(" ", A1, FIND(" ", A1)+1) -1)*1, "hh:mm") & " - " & TEXT(MID(A1, FIND(" ", A1, FIND(" ", A1, FIND(" ", A1)+1)+1)+1, LEN(A1)-FIND(" ", A1, FIND(" ", A1, FIND(" ", A1)+1)+1))*1, "hh:mm")

This works by:

1. Finding the positions of spaces and dashes to extract the raw time strings.
2. Converting those strings to numeric times and formatting them with leading zeros.

Method 3: Power Query (For Bulk Data) #

If you have a lot of entries to process, Power Query is a more scalable, visual option:

1. Select your data cell/column and go to the Data tab > From Table/Range (Excel will create a table if needed).
2. In the Power Query Editor:
   • Split the column by delimiter SER_ (check "Split at each occurrence of the delimiter").
   • Unpivot all columns except the first one (right-click columns > Unpivot Other Columns).
   • Filter out any empty rows from the "Value" column.
   • Split the "Value" column by space into 4 columns (Split Column > By Delimiter > Space, choose "Split into columns").
   • Split the third column (named Column3) by delimiter - into two columns (Time1 and Date2).
   • Delete the unnecessary columns (Label, Date1, Date2).
   • Format Time1 and Time2 columns to hh:mm (right-click column > Change Type > Time).
   • Add a custom column: = [Time1] & " - " & [Time2].
   • Merge all values in the custom column into one cell: select the column > Transform > Merge Columns > Choose space as delimiter, name the new column.
3. Click Close & Load to bring the cleaned data back to Excel.

Content of the question originates from Stack Exchange, question author user1548544