如何将冒号分隔的数据拆分转换并提取至新列？

Hey there! Let's walk through how to split your colon-separated key-value data into the desired format, depending on the tool you're using:

If you're working in a spreadsheet tool, you can use a combination of text functions to get the result. Assume your raw data is in cell A1:

1. First, split the semicolon-separated string into individual key-value pairs, then split each pair by colon to get a 2D array of keys and values.
2. Extract the first 4 pairs, flatten the array, then join everything with spaces.

Use this formula:

=TEXTJOIN(" ", TRUE, FLATTEN(INDEX(TEXTSPLIT(TEXTSPLIT(A1, ";"), ":"), 1:4, )))

Breakdown: #

• TEXTSPLIT(A1, ";"): Splits the original string into an array of individual pairs (e.g., {"YR:136", "YR:50", ...})
• TEXTSPLIT(..., ":"): Splits each pair into key and value, creating a 2D array
• INDEX(..., 1:4, ): Grabs the first 4 rows of the 2D array (your target pairs)
• FLATTEN(): Converts the 2D array into a single list of keys and values
• TEXTJOIN(" ", TRUE, ...): Joins all elements with spaces, ignoring empty values

For a scripting approach, Python makes this straightforward with basic string manipulation:

raw_data = "YR:136;YR:50;JN:275;YM:138;IN:477;WO:150;G1:10;F2:10"

# Split into individual key-value pairs, take first 4
selected_pairs = raw_data.split(';')[:4]

# Flatten each pair into key + value, then join with spaces
final_output = ' '.join(item for pair in selected_pairs for item in pair.split(':'))

print(final_output)  # Output: YR 136 YR 50 JN 275 YM 138

Breakdown: #

• raw_data.split(';')[:4]: Splits the string by semicolons and keeps only the first 4 pairs
• The generator expression item for pair in ... for item in pair.split(':') splits each pair by colon and flattens the results into a single sequence
• ' '.join(...): Combines all elements into the space-separated string you need

If you prefer a no-code approach in Excel, Power Query works great for this:

1. Load your data into Power Query (select the cell > Data tab > From Table/Range)
2. Split the column by delimiter ;, choose "Split into Rows"
3. Split the new column by delimiter :, choose "Split into Columns" (you'll get two columns: Key and Value)
4. Keep only the first 4 rows (Home tab > Keep Rows > Keep Top Rows > enter 4)
5. Select both Key and Value columns, then go to Transform tab > Unpivot Columns
6. Finally, merge the single column of values with a space delimiter: Transform tab > Merge Columns > choose "Space" as separator
7. Load the result back to your spreadsheet

内容的提问来源于stack exchange，提问作者Abhishek Shukla