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

---

FAQ

Why does my Airtable lookup return a comma-separated list?

Lookup fields pull values from linked records. When a record links to multiple items, the lookup concatenates all values with commas. This is by design. To work with individual values, you need to split the string using formulas or a script.

Can I split lookup values without scripting?

Yes, but it gets verbose quickly. Use the LEFT(), RIGHT(), FIND(), and CONCATENATE() formula pattern shown above for up to about four items. Beyond that, a script with split(",") is more practical and easier to maintain.

Is there a way to avoid splitting lookups entirely?

Yes. Restructure your data so each value is its own linked record. Instead of storing "Red, Blue, Green" as a single text field, create an Items table and link each value as a separate record. Lookups and rollups then work natively without any string parsing.

---

Collect structured data instead of splitting it later

If your lookup mess starts with messy form submissions, fix the input. Filla lets you build Airtable forms with linked record fields, so respondents select or create related records directly. No comma-separated cleanup needed.

• Linked record fields let users pick from existing records
• Each selection creates a proper Airtable link, not a text string
• Conditional logic, file uploads, and validation included
• Start free with 5 forms and unlimited submissions

Create your first form free →

---