Turn imported strings into reliable columns without overwriting the source. Choose Text to Columns or TEXTSPLIT, preserve empty fields, and control whether Excel splits every delimiter or only the last one.
I have these imported values in Excel cells A2:A3: CUST-1042|North|Active CUST-1180|West|Paused I need three output columns named Customer ID, Region, and Status. Show me two safe methods: 1. Text to Columns for a one-time conversion; 2. a TEXTSPLIT formula for a dynamic result in Microsoft 365. For Text to Columns, tell me how to avoid overwriting adjacent data and how to preserve the original column. For TEXTSPLIT, give the exact formula entered in B2 and explain the spill range. State the exact expected outputs for both rows. Do not split the hyphen inside the customer ID.
Test 1 of 3 — approved PASS and preserved as tested.
I need to split imported tags in Excel, but the source uses both commas and semicolons: A2: Red,Blue;Green A3: Red;;Green A4: Red, Blue;Green Give me a Microsoft 365 formula that: 1. treats comma and semicolon as alternative delimiters; 2. preserves the empty field in A3 so its position is not lost; 3. removes only incidental spaces around individual values; 4. does not merge or reorder the tags; 5. explains what happens when the formula spills into occupied cells. Use TEXTSPLIT and provide the exact formula for B2. State the expected three output cells for each input row. If a formula option would suppress empty fields, explicitly explain why it is unsafe for this dataset.
Test 2 of 3 — approved PASS and preserved as tested.
These imported descriptions are in A2:A4:
INV-2026-001 - Acme - Urgent
INV-2026-002 - Beta
INV-2026-003
I need exactly two output columns:
- Base description: everything before the last occurrence of space-hyphen-space (" - ")
- Final label: everything after the last occurrence
The hyphens inside invoice IDs must remain unchanged. If the delimiter does not exist, keep the full original text as Base description and return a blank Final label.
Give exact Microsoft 365 formulas for B2 and C2 using TEXTBEFORE and TEXTAFTER with the appropriate instance number and fallback handling. State the expected output for all three rows. Preserve column A unchanged and mention how I should spot-check imported values with inconsistent spacing.Test 3 of 3 — approved PASS and preserved as tested.
| Source condition | Risk |
|---|---|
| CUST-1042|North|Active | One imported field; adjacent cells may already contain data. |
| Output | Expected result |
|---|---|
| Three fields | CUST-1042 · North · Active using | only |
| Source condition | Risk |
|---|---|
| Red;;Green | Two delimiters with a meaningful empty middle position. |
| Output | Expected result |
|---|---|
| Preserved position | Red · blank · Green with ignore_empty = FALSE |
| Source condition | Risk |
|---|---|
| INV-2026-001 - Acme - Urgent | Every hyphen is not a valid delimiter. |
| Output | Expected result |
|---|---|
| Last occurrence only | INV-2026-001 - Acme · Urgent |
Keep column A unchanged and choose an empty destination range before splitting.
Decide whether the task uses one delimiter, alternative delimiters, or only the first/last occurrence.
Use Text to Columns for a controlled one-time conversion or TEXTSPLIT for dynamic Microsoft 365 output.
Set TEXTSPLIT's ignore_empty argument to FALSE when empty fields carry meaning.
Check the number and order of output columns, #SPILL! errors, IDs containing punctuation, and rows with missing delimiters.