SPLIT function
PRO
The SPLIT
function in Jspreadsheet Formulas Pro is a handy tool that helps you divide a block of text into multiple parts, based on a specific character, known as a delimiter. For example, if you have a list of names written as 'John, Sara, Mike', and you'd like each name in a separate cell, you can use the SPLIT
function with a comma as your delimiter. This will result in three separate cells: 'John', 'Sara', and 'Mike'. It's a great way to organize and separate data for easier analysis.
Documentation
Splits a text string into a table of substrings based on a delimiter.
Category
Text
Syntax
SPLIT(text, delimiter)
Parameter | Description |
---|---|
text |
The text string to be split. |
delimiter |
The character or characters that separate the substrings in the text string. |
Behavior
The SPLIT
function is used to divide text around a specified character(s) and put each fragment into a separate cell in the row. Here's how it handles different types of data:
- Empty cells: If the cell is empty,
SPLIT
will return an empty cell. - Text:
SPLIT
will divide the text at each instance of the specified character(s). - Booleans: If Boolean values (TRUE/FALSE) are included,
SPLIT
will treat them as text and split accordingly. - Errors: If the specified character(s) to split text is not found in the cell,
SPLIT
will return the original text. - Numbers:
SPLIT
will treat numbers as text and split accordingly.
Common Errors
Error | Description |
---|---|
#VALUE! | This error occurs when the function cannot find the specified character(s) to split the cell content. |
#REF! | This error occurs when the function has no room to return its results. This usually happens when there is content in the cells where the splitting results would go. |
Best practices
- Always ensure there is enough empty space in the row for the
SPLIT
function to display all the results. If there's content in the cells where the split results would go, it may overwrite the existing data.- Use unique character(s) for splitting that do not appear in the text except as separators.
- Be aware that
SPLIT
is case-sensitive. For example, it will treat "a" and "A" as different characters.- If you're splitting text that includes numbers, ensure the numbers are formatted as text to avoid unexpected results.
Usage
A few examples using the SPLIT function.
SPLIT('apple,banana,orange', ',') → ['apple', 'banana', 'orange']
SPLIT('Hello world', ' ') → ['Hello', 'world']
SPLIT('2025-08-25', '-') → ['2025', '08', '25']
SPLIT('TRUE,FALSE', ',') → ['1', '0']
SPLIT('one|two|three|four', '|') → ['one', 'two', 'three', 'four']
Interactive Spreadsheet Demo
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<div id="spreadsheet"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NThjZGJlNTVmOTcxOTBhMjNjM2M1MTJmNmJhYjBmNjc0ZjdlYTUxOGIzMTVjZThlZDJjMTI2ZGQyZjUzZDYwYTI4N2Y2MDE5ZDRhZDliMjUxY2IxOWRjMTk3ZGMzMmQyZDE5MDVkNTE0MWM0NWFhM2FhZmYxYTAxODNiOTg5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"apple,banana,orange",
"=SPLIT(A1,\",\")"
],
[
"John Doe;Jane Smith;Bob Wilson",
"=SPLIT(A2,\";\")"
],
[
"red|blue|green|yellow",
"=SPLIT(A3,\"|\")"
],
[
"Monday Tuesday Wednesday",
"=SPLIT(A4,\" \")"
]
]
}]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set license
jspreadsheet.setLicense('NThjZGJlNTVmOTcxOTBhMjNjM2M1MTJmNmJhYjBmNjc0ZjdlYTUxOGIzMTVjZThlZDJjMTI2ZGQyZjUzZDYwYTI4N2Y2MDE5ZDRhZDliMjUxY2IxOWRjMTk3ZGMzMmQyZDE5MDVkNTE0MWM0NWFhM2FhZmYxYTAxODNiOTg5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"apple,banana,orange",
"=SPLIT(A1,\",\")"
],
[
"John Doe;Jane Smith;Bob Wilson",
"=SPLIT(A2,\";\")"
],
[
"red|blue|green|yellow",
"=SPLIT(A3,\"|\")"
],
[
"Monday Tuesday Wednesday",
"=SPLIT(A4,\" \")"
]
];
// Render component
return (
<Spreadsheet ref={spreadsheet}>
<Worksheet data={data} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet">
<Worksheet :data="data" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('NThjZGJlNTVmOTcxOTBhMjNjM2M1MTJmNmJhYjBmNjc0ZjdlYTUxOGIzMTVjZThlZDJjMTI2ZGQyZjUzZDYwYTI4N2Y2MDE5ZDRhZDliMjUxY2IxOWRjMTk3ZGMzMmQyZDE5MDVkNTE0MWM0NWFhM2FhZmYxYTAxODNiOTg5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"apple,banana,orange",
"=SPLIT(A1,\",\")"
],
[
"John Doe;Jane Smith;Bob Wilson",
"=SPLIT(A2,\";\")"
],
[
"red|blue|green|yellow",
"=SPLIT(A3,\"|\")"
],
[
"Monday Tuesday Wednesday",
"=SPLIT(A4,\" \")"
]
]
return {
data
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NThjZGJlNTVmOTcxOTBhMjNjM2M1MTJmNmJhYjBmNjc0ZjdlYTUxOGIzMTVjZThlZDJjMTI2ZGQyZjUzZDYwYTI4N2Y2MDE5ZDRhZDliMjUxY2IxOWRjMTk3ZGMzMmQyZDE5MDVkNTE0MWM0NWFhM2FhZmYxYTAxODNiOTg5MTIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
[
"apple,banana,orange",
"=SPLIT(A1,\",\")"
],
[
"John Doe;Jane Smith;Bob Wilson",
"=SPLIT(A2,\";\")"
],
[
"red|blue|green|yellow",
"=SPLIT(A3,\"|\")"
],
[
"Monday Tuesday Wednesday",
"=SPLIT(A4,\" \")"
]
]
}]
});
}
}