SPLIT_IN_COLUMNS
Split text into multiple horizontal columns by a separator with automatic trimming.
=SPLIT_IN_COLUMNS(text, [separator])Gibt zurück: string[][]
Übersicht
SPLIT_IN_COLUMNS takes a text string and divides it into separate cells spread across columns, using a specified separator character. Each segment is automatically trimmed of leading and trailing whitespace, giving you clean values without extra spaces. The default separator is a comma.
Parameter
| Parameter | Typ | Erforderlich | Beschreibung |
|---|---|---|---|
text | string | Ja | The text string to split. |
separator | string | Nein (,) | The character(s) to split on. Defaults to ",". |
Beispiele
Split comma-separated tags
Split a comma-separated tag string into individual columns with automatic trimming.
=SPLIT_IN_COLUMNS("SEO, Content, Marketing")Ausgabe
| SEO | Content | Marketing |
Split pipe-delimited data
Parse pipe-delimited data into separate fields.
=SPLIT_IN_COLUMNS("John|Smith|Sales|Manager", "|")Ausgabe
| John | Smith | Sales | Manager |
Break apart a full address
Split a comma-delimited address into street, city, state, and zip components.
=SPLIT_IN_COLUMNS("123 Main St, Springfield, IL, 62701")Ausgabe
| 123 Main St | Springfield | IL | 62701 |
Parse a URL path
Split a URL path into its individual segments.
=SPLIT_IN_COLUMNS("blog/2024/seo-tips", "/")Ausgabe
| blog | 2024 | seo-tips |
Split date components
Break a date string into year, month, and day columns.
=SPLIT_IN_COLUMNS("2024-01-15", "-")Ausgabe
| 2024 | 01 | 15 |
Anwendungsfälle
CSV data parsing
Break apart comma-separated values pasted into single cells into proper column structure for analysis.
Name splitting
Split full names into first name, last name, and title columns for CRM import or mail merge.
Address component extraction
Parse combined address strings into separate street, city, state, and zip code columns for geocoding or mail sorting.
Log file parsing
Split log entries that use pipe or tab delimiters into structured columns for analysis and filtering.
Product attribute extraction
Split compound product descriptors like "Red | Large | Cotton" into separate attribute columns.
Tag normalization
Convert comma-separated tag strings into individual columns for deduplication and analysis.
Profi-Tipps
Wrap in TRANSPOSE() to split into rows instead of columns: =TRANSPOSE(SPLIT_IN_COLUMNS("a,b,c")).
Combine with INDEX to extract a specific segment: =INDEX(SPLIT_IN_COLUMNS("a,b,c"), 1, 2) returns "b".
Use GROUP_BY_CELL to reverse the operation and join columns back into a delimited string.
For splitting an entire column of delimited values, place the formula in each row or use ARRAYFORMULA where applicable.
Use " - " (with spaces) as separator when your data uses spaced delimiters to avoid extra trimming issues.
While Google Sheets has a built-in SPLIT function, SPLIT_IN_COLUMNS adds automatic trimming that SPLIT does not provide. When you split "apple, banana, cherry" with SPLIT, you get " banana" and " cherry" with leading spaces. SPLIT_IN_COLUMNS gives you clean "banana" and "cherry" values ready for use.
The function outputs values in a single horizontal row, making it ideal for breaking apart delimited data into structured columns. Common use cases include parsing CSV-style data pasted into cells, splitting full names into first and last name columns, breaking apart compound identifiers like "US-CA-90210" into country, state, and zip components, and separating multi-value fields from form submissions.
You can specify any character or string as the separator, including pipes (|), semicolons (;), hyphens (-), slashes (/), or even multi-character sequences. If the text is empty or null, the function returns a single empty cell. For the reverse operation -- joining multiple cells into a single string -- use GROUP_BY_CELL.
Häufige Fehler
Output overwrites adjacent cellsUrsache: The split result spans multiple columns and overwrites data in neighboring cells.
Lösung: Ensure enough empty columns exist to the right of the formula cell to accommodate all segments. Preview your data to estimate the maximum number of segments.
Returns the entire string unsplitUrsache: The specified separator does not exist in the text, or the wrong separator was specified.
Lösung: Verify the separator matches exactly what appears in the text. Check for invisible characters or different dash/quote types.
Empty cell in outputUrsache: The input text has consecutive separators (e.g., "a,,b") or leading/trailing separators.
Lösung: This is expected behavior. Clean the source data first if empty segments are not desired, or filter the output.
Häufig Gestellte Fragen
Yes, each segment is automatically trimmed of leading and trailing whitespace. "a, b, c" becomes ["a", "b", "c"] not ["a", " b", " c"]. This is a key advantage over the native SPLIT function.
The default separator is a comma (,). If you omit the second argument, the text will be split on commas.
Yes, the separator can be any string. For example, you can split on " - " (space-dash-space) or " | " or any other pattern.
The entire text is returned as a single cell. No error is thrown; you simply get one column containing the full original text.
SPLIT_IN_COLUMNS automatically trims whitespace from each segment, which SPLIT does not do. SPLIT can also split on each individual character in the delimiter string, while SPLIT_IN_COLUMNS treats the entire separator as a single delimiter.
Yes, consecutive separators produce empty strings between them. "a,,b" split by "," gives ["a", "", "b"]. The empty segments are trimmed but remain as empty cells.
SPLIT_IN_COLUMNS always outputs horizontally. To get vertical output, wrap the result in TRANSPOSE: =TRANSPOSE(SPLIT_IN_COLUMNS("a,b,c")).
Verwandte Funktionen
GROUP_BY_CELL
Concatenate all non-empty cells in a range into a single string with a separator.
COMBINE_ALL
Combine and flatten multiple ranges into a single column, excluding empty cells.
MY_REPLACE
Replace all occurrences of a substring in text without specifying occurrence numbers.
Beginnen Sie noch heute mit SPLIT_IN_COLUMNS
Installieren Sie Unlimited Sheets, um SPLIT_IN_COLUMNS und 41 weitere leistungsstarke Funktionen in Google Sheets zu erhalten.
