The question: can I break a lookup list into columns?

Lookups often return a comma‑separated list of values. You might want {Item 1}, {Item 2}, {Item 3} as separate fields for downstream formulas or exports. Airtable formulas can do it, but the expressions are verbose. Here are three ways with trade‑offs.

---

Option A — Pure formulas (no code)

Start with a text field {List} that contains your comma‑separated values. If your source is a Lookup, create a helper formula {List} that equals the lookup, so downstream formulas treat it as text reliably.

Extract the first four items:

// Item 1
IF(
  FIND(",", {List}),
  LEFT({List}, FIND(",", {List}) - 1),
  {List}
)

// Item 2
LEFT(
  RIGHT({List}, LEN({List}) - LEN({Item 1}) - 2),
  FIND(",",
    RIGHT({List}, LEN({List}) - LEN({Item 1}) - 2) & ","
  ) - 1
)

// Item 3
LEFT(
  RIGHT({List}, LEN({List}) - LEN(CONCATENATE({Item 1}, {Item 2})) - 4),
  FIND(",",
    RIGHT({List}, LEN({List}) - LEN(CONCATENATE({Item 1}, {Item 2})) - 4) & ","
  ) - 1
)

// Item 4
LEFT(
  RIGHT({List}, LEN({List}) - LEN(CONCATENATE({Item 1}, {Item 2}, {Item 3})) - 6),
  FIND(",",
    RIGHT({List}, LEN({List}) - LEN(CONCATENATE({Item 1}, {Item 2}, {Item 3})) - 6) & ","
  ) - 1
)

Pattern notes:

• Each successive item subtracts 2 more characters in the LEN(...) - N terms to account for commas and spaces
• Extend {Item 5} by adding {Item 4} into the two CONCATENATE(...) calls and subtracting 8 where you previously subtracted 6, and so on

Pros: no code, works on any plan.
Cons: does not scale nicely beyond a handful of items, tedious to maintain.

---

Option B — A tiny script with split() (recommended)

If you have Scripting or Automations, you can split the list and write values to fields {Item 1}, {Item 2}, {Item 3} automatically.

Script outline (Automation action → “Run script”):

// Input config: table name and the text field to split
const table = base.getTable("Your Table")
const query = await table.selectRecordsAsync({
  fields: ["List", "Item 1", "Item 2", "Item 3"],
})

for (const record of query.records) {
  const raw = record.getCellValueAsString("List")
  const parts = raw
    ? raw
        .split(",")
        .map((s) => s.trim())
        .filter(Boolean)
    : []
  await table.updateRecordAsync(record, {
    "Item 1": parts[0] || null,
    "Item 2": parts[1] || null,
    "Item 3": parts[2] || null,
  })
}

Tips:

• Trigger on record creation or when {List} changes
• Increase the number of target fields as needed
• Use null to clear previous values when lists shrink

Pros: clear and scalable, easy to extend.
Cons: requires Pro for automation and basic scripting comfort.

---

Option C — Model the data instead of splitting

Splitting strings into columns is a symptom. The normalized alternative is to store values as linked records, not as a combined string.

Pattern:

1. Create a new table Items with primary field {Item Name}
2. Convert the source into a linked field to Items (or script the creation of child records)
3. Use a Junction table if you need attributes on each link

Benefits:

• No splitting required; each item is a record
• Aggregate cleanly with lookups and rollups
• Easier to filter, group, and deduplicate

---

Quick reference

Goal	Approach
Split a short list quickly	Use the Item 1…Item 4 formula pattern
Split reliably at any length	Use a script and split(",")
Avoid splitting entirely	Normalize into linked records (Items table)

---

Pitfalls

• Lookup vs text: wrap the lookup in a formula field to stabilize the input
• Extra commas or spaces: always trim() and filter empties in scripts
• Changing list lengths: clear old Item fields before writing new values

---

Further reading

• Community context: Separate a Look Up field (separated by commas) into separate cells

---