--- title: "TEXTSPLIT function in Excel: syntax and examples | PracticeExcel" description: "How to use TEXTSPLIT in Excel 365: split names, lists and codes into columns or rows with one formula that updates by itself." canonical_url: "https://practiceexcelonline.com/functions/textsplit" locale: en meta: "og:description": "How to use TEXTSPLIT in Excel 365: split names, lists and codes into columns or rows with one formula that updates by itself." "og:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~VEVYVFNQTElUIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBURVhUU1BMSVQgaW4gRXhjZWwgMzY1OiBzcGxpdCBuYW1lcywgbGlzdHMgYW5kIGNvZGVzIGludG8gY29sdW1ucyBvciByb3dzIHdpdGggb25lIGZvcm11bGEgdGhhdCB1cGRhdGVzIGJ5IGl0c2VsZi4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~VEVYVFNQTElUKHRleHQsIGNvbF9kZWxpbWl0ZXIsIFtyb3dfZGVsaW1pdGVyXSwgW2lnbm9yZV9lbXB0eV0sIFttYXRjaF9tb2RlXSwgW3BhZF93aXRoXSk%2Cp_Ii9mdW5jdGlvbnMvdGV4dHNwbGl0Ig%2Cs_t5OHE7LVi7VUskO3.png" "og:image:height": "600" "og:image:type": image/png "og:image:width": "1200" "og:title": "TEXTSPLIT function in Excel: syntax and examples | PracticeExcel" "twitter:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~VEVYVFNQTElUIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBURVhUU1BMSVQgaW4gRXhjZWwgMzY1OiBzcGxpdCBuYW1lcywgbGlzdHMgYW5kIGNvZGVzIGludG8gY29sdW1ucyBvciByb3dzIHdpdGggb25lIGZvcm11bGEgdGhhdCB1cGRhdGVzIGJ5IGl0c2VsZi4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~VEVYVFNQTElUKHRleHQsIGNvbF9kZWxpbWl0ZXIsIFtyb3dfZGVsaW1pdGVyXSwgW2lnbm9yZV9lbXB0eV0sIFttYXRjaF9tb2RlXSwgW3BhZF93aXRoXSk%2Cp_Ii9mdW5jdGlvbnMvdGV4dHNwbGl0Ig%2Cs_t5OHE7LVi7VUskO3.png" --- > Index of every page on this site: [llms.txt](https://practiceexcelonline.com/llms.txt) # **The TEXTSPLIT function** Splits text into pieces at a delimiter and spills them across columns, or down rows when you also give a row delimiter. = TEXTSPLIT( ## **Syntax** ``` TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) ```
**text **
The text to split.
**col_delimiter **
The character or text that separates columns, such as " " or ",".
**row_delimiter **(optional)
The character or text that starts a new row.
**ignore_empty **(optional)
TRUE to skip empty pieces when two delimiters are next to each other. Left out, FALSE.
**match_mode **(optional)
1 to ignore upper and lower case in the delimiter. Left out, 0, which matches case.
**pad_with **(optional)
What to put in cells a short row leaves empty. Left out,
## **Examples** C1`=TEXTSPLIT(1A2,2" ")` | | A | B | C | D | | --- | --- | --- | --- | --- | | 1 | Full name | | **Ann** | **Berg** | | 2 | Ann Berg | | | | **In plain words:** Split 1the full name in A2 into columns at 2each space. B1=TEXTSPLIT("red,green,blue",",") | | A | B | C | D | | --- | --- | --- | --- | --- | | 1 | | **red** | **green** | **blue** | B1=TEXTSPLIT("Ann:42;Ben:37",":",";") | | A | B | C | | --- | --- | --- | --- | | 1 | | **Ann** | **42** | | 2 | | **Ben** | **37** | ## **Read the guide** - [**How to merge cells in Excel, and what to use instead**](https://practiceexcelonline.com/learn/merge-cells-excel) ## **Questions**
### Why are the numbers from TEXTSPLIT not adding up? TEXTSPLIT returns text, even for pieces that look like numbers. Wrap it in VALUE, or put -- in front, to turn them into numbers.
### What can I use instead of TEXTSPLIT in older Excel or Google Sheets? Google Sheets has SPLIT, which works much the same. In older Excel, use Data > Text to Columns, or take the pieces out with LEFT, MID and FIND.
## **Related functions** - [**TEXTBEFORE**](https://practiceexcelonline.com/functions/textbefore) - [**TEXTAFTER**](https://practiceexcelonline.com/functions/textafter) - [**TEXTJOIN**](https://practiceexcelonline.com/functions/textjoin) - [**MID**](https://practiceexcelonline.com/functions/mid) ```json {"@context":"https://schema.org","@graph":[{"@id":"https://practiceexcelonline.com/#identity","@type":"Organization","name":"PracticeExcel","url":"https://practiceexcelonline.com/","logo":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/#website","@type":"WebSite","inLanguage":"en-US","name":"PracticeExcel","url":"https://practiceexcelonline.com/","alternateName":["Practice Excel Online","PracticeExcelOnline.com"],"publisher":{"@id":"https://practiceexcelonline.com/#identity"},"workTranslation":{"@id":"https://excelopgaver.dk/#website"}},{"@id":"https://practiceexcelonline.com/functions/textsplit#webpage","description":"How to use TEXTSPLIT in Excel 365: split names, lists and codes into columns or rows with one formula that updates by itself.","name":"TEXTSPLIT function in Excel: syntax and examples","url":"https://practiceexcelonline.com/functions/textsplit","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://practiceexcelonline.com/functions/textsplit#breadcrumb"},"isPartOf":{"@id":"https://practiceexcelonline.com/#website"},"mainEntity":[{"@id":"https://practiceexcelonline.com/functions/textsplit#/schema/question/1"},{"@id":"https://practiceexcelonline.com/functions/textsplit#/schema/question/2"}],"potentialAction":[{"@type":"ReadAction","target":["https://practiceexcelonline.com/functions/textsplit"]}],"primaryImageOfPage":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/functions/textsplit#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://practiceexcelonline.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://practiceexcelonline.com/functions","position":2},{"@type":"ListItem","name":"TEXTSPLIT function in Excel: syntax and examples","position":3}]},{"@id":"https://practiceexcelonline.com/functions/textsplit#article","description":"How to use TEXTSPLIT in Excel 365: split names, lists and codes into columns or rows with one formula that updates by itself.","headline":"TEXTSPLIT function in Excel: syntax and examples","inLanguage":"en-US","thumbnailUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~VEVYVFNQTElUIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBURVhUU1BMSVQgaW4gRXhjZWwgMzY1OiBzcGxpdCBuYW1lcywgbGlzdHMgYW5kIGNvZGVzIGludG8gY29sdW1ucyBvciByb3dzIHdpdGggb25lIGZvcm11bGEgdGhhdCB1cGRhdGVzIGJ5IGl0c2VsZi4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~VEVYVFNQTElUKHRleHQsIGNvbF9kZWxpbWl0ZXIsIFtyb3dfZGVsaW1pdGVyXSwgW2lnbm9yZV9lbXB0eV0sIFttYXRjaF9tb2RlXSwgW3BhZF93aXRoXSk%2Cp_Ii9mdW5jdGlvbnMvdGV4dHNwbGl0Ig%2Cs_t5OHE7LVi7VUskO3.png","@type":["Article","TechArticle"],"author":{"@id":"https://practiceexcelonline.com/#identity"},"image":{"@id":"https://practiceexcelonline.com/#/schema/image/1"},"isPartOf":{"@id":"https://practiceexcelonline.com/functions/textsplit#webpage"},"mainEntityOfPage":{"@id":"https://practiceexcelonline.com/functions/textsplit#webpage"},"publisher":{"@id":"https://practiceexcelonline.com/#identity"}},{"@id":"https://practiceexcelonline.com/functions/textsplit#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"Why are the numbers from TEXTSPLIT not adding up?","acceptedAnswer":{"@type":"Answer","text":"TEXTSPLIT returns text, even for pieces that look like numbers. Wrap it in VALUE, or put -- in front, to turn them into numbers."}},{"@id":"https://practiceexcelonline.com/functions/textsplit#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"What can I use instead of TEXTSPLIT in older Excel or Google Sheets?","acceptedAnswer":{"@type":"Answer","text":"Google Sheets has SPLIT, which works much the same. In older Excel, use Data > Text to Columns, or take the pieces out with LEFT, MID and FIND."}},{"@id":"https://practiceexcelonline.com/#logo","@type":"ImageObject","caption":"PracticeExcel","contentUrl":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png"},{"@id":"https://practiceexcelonline.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~VEVYVFNQTElUIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBURVhUU1BMSVQgaW4gRXhjZWwgMzY1OiBzcGxpdCBuYW1lcywgbGlzdHMgYW5kIGNvZGVzIGludG8gY29sdW1ucyBvciByb3dzIHdpdGggb25lIGZvcm11bGEgdGhhdCB1cGRhdGVzIGJ5IGl0c2VsZi4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~VEVYVFNQTElUKHRleHQsIGNvbF9kZWxpbWl0ZXIsIFtyb3dfZGVsaW1pdGVyXSwgW2lnbm9yZV9lbXB0eV0sIFttYXRjaF9tb2RlXSwgW3BhZF93aXRoXSk%2Cp_Ii9mdW5jdGlvbnMvdGV4dHNwbGl0Ig%2Cs_t5OHE7LVi7VUskO3.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~VEVYVFNQTElUIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBURVhUU1BMSVQgaW4gRXhjZWwgMzY1OiBzcGxpdCBuYW1lcywgbGlzdHMgYW5kIGNvZGVzIGludG8gY29sdW1ucyBvciByb3dzIHdpdGggb25lIGZvcm11bGEgdGhhdCB1cGRhdGVzIGJ5IGl0c2VsZi4%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~VEVYVFNQTElUKHRleHQsIGNvbF9kZWxpbWl0ZXIsIFtyb3dfZGVsaW1pdGVyXSwgW2lnbm9yZV9lbXB0eV0sIFttYXRjaF9tb2RlXSwgW3BhZF93aXRoXSk%2Cp_Ii9mdW5jdGlvbnMvdGV4dHNwbGl0Ig%2Cs_t5OHE7LVi7VUskO3.png"}]} ``` ## Sitemap See the full [sitemap](https://practiceexcelonline.com/sitemap.md) for all pages.