Recommended Free Tools
Excel can split the contents of a cell into other cells or rows, but it cannot divide the worksheet cell itself. As Microsoft puts it, “In Excel, a cell is the smallest unit of data.” Choose Text to Columns for a quick one-time split, TEXTSPLIT or text formulas for formula-driven results, Flash Fill for a recognizable pattern, or Power Query for repeatable cleanup. Before splitting, make sure the output area is clear or choose a different destination: results can overwrite data to the right.
1. Use Text to Columns for a one-time split
Text to Columns is a good fit for a list with a consistent separator, such as commas, semicolons, or spaces. It turns the selected text into adjacent columns.
- Select the source cells.
- Go to Data > Text to Columns.
- Choose Delimited, then continue.
- Select the delimiter that separates the values. Review the preview to confirm the split is happening where you expect.
- Set a destination if you do not want the results to begin beside the source, then finish.
Check that the destination cells are empty before finishing. If the output would replace existing data, choose another destination or move that data first. Microsoft’s guide explains the operation and the risk of overwriting adjacent cells: Split a cell in Excel and Text to Columns wizard.
2. Use TEXTSPLIT for formula-driven results
Use TEXTSPLIT when you want a formula to return the separated values rather than convert the source in place. The result spills into neighboring cells, so keep those cells clear.
#1 Best Overall
- Over 215 Microsoft Windows Excel Shortcuts
- Two-Sided Durable Laminiated Sheet
- Designed for Excel on a Windows Computer
For example, if A1 contains red, blue, green, enter =TEXTSPLIT(A1, ", ") to split at each comma followed by a space. The function can also split down rows by supplying a row delimiter as its third argument. Its syntax is =TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with]).
Consecutive delimiters produce empty results by default; set the ignore_empty argument to TRUE when you want those skipped. Microsoft documents TEXTSPLIT for Microsoft 365 and Excel 2024, including Mac. Availability can depend on your Excel edition and release. See Microsoft’s TEXTSPLIT function documentation.
Rank #2
- Instant Copilot. Unlock new possibilities with the dedicated Copilot key, which gives you instant access to experiences that can enhance your productivity¹.
- Enhance your experience With the new microphone mute key and snipping key
- Full keyboard experience. Features a full mechanical keyset, backlit keys, and a large trackpad for precise navigation and control. Optimal key spacing allows fast, fluid typing.
- Slim and compact Performs like a traditional, full-size keyboard.
- Clicks in place instantly Use in combination with the Surface Pro (11th Edition), Pro 9 and Pro 8* kickstand for a perfect laptop experience anywhere.
3. Use Flash Fill when the pattern is recognizable
Flash Fill can extract or rearrange text when the entries follow a pattern, even if they do not all use the same delimiter. For example, you can enter the desired part of a name beside the original, then let Excel infer the pattern from another example.
- Type the desired result for the first source row in an adjacent column.
- Move to the next row and begin entering the next result. Review Excel’s suggested preview.
- Accept the suggestion only if it correctly handles the examples and the rest of your data.
Microsoft also documents Data > Flash Fill and Ctrl+E on Windows. Flash Fill infers a pattern from your examples, so review the completed values rather than assuming every row was interpreted correctly. See Microsoft’s Flash Fill guide.
Rank #3
- EXCEL SHORTCUTS. ZERO SEARCHING. – Our bestselling reference mat puts an extensive collection of commonly used commands, formulas and helpful tricks directly beneath your fingertips so you can find answers fast, work smarter and stay in the flow.
- YOUR DESK. SMARTER. – Clearly organized sections for navigation, selection, formatting, data and functions make it easy to find the right Excel command exactly when you need it.
- LEARN, WORK & RESET – Built-in desk-exercise diagrams give you 10 quick ways to stretch, recharge and return to work feeling sharper.
- ROOM TO WORK & CREATE – The extended 31.5 x 11.8-inch Pixiecube desk mat fits a laptop or keyboard and mouse, while the soft 2 mm surface adds comfort and protects your desktop.
- BUILT FOR REAL-WORLD WORKDAYS – A rugged stitched edge helps prevent fraying, and the water-resistant, stain-resistant surface protects against scratches, spills and everyday wear—because smarter desks should work harder.
4. Use Power Query for recurring cleanup
Power Query is useful when you repeatedly import or clean a dataset and want to apply the same split as part of a transformation.
- Open the data in Power Query.
- On the Home tab, choose Split Column > By Delimiter.
- Choose the separator and whether to split at the left-most occurrence, right-most occurrence, or each occurrence.
- Use the advanced options to specify whether the results should be output as columns or rows, then apply the transformation.
For the available split choices, see Microsoft’s Power Query split-column documentation.
Rank #4
- Efficient Media Controls: The Wired Keyboard 600, designed by Microsoft, features a Media Center with four hot keys for easy control of play/pause, volume up, volume down, and mute functions.
- Quiet and Responsive Keys: Enjoy a comfortable typing experience with quiet, thin-profile keys that are both responsive and efficient.
- Convenient Shortcuts: Quickly access common tasks with dedicated shortcut keys, including a calculator hot key and a Windows start screen key.
- Spill-Resistant Design: Work confidently with a spill-resistant design that protects your keyboard from accidental messes.
- Plug-and-Play Simplicity: No software needed—just connect the keyboard to your PC and start using it right away, with a full number pad for efficient data entry.
5. Use text functions for targeted extraction
LEFT, MID, RIGHT, SEARCH, and LEN can extract text based on character positions or a delimiter. They are useful when you need a specific component or want to control the extraction with a formula, but the formula must reflect the actual structure of the input.
For example, extracting everything before the first space may work for a consistently formatted two-part name, but it will not reliably identify a first name in data that includes middle names, initials, prefixes, suffixes, or compound surnames. Account for those variations rather than treating one delimiter-based formula as a universal name parser. Microsoft’s text-function guidance demonstrates extracting parts of names and discusses input variations.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Best Value
- 💻 ✔️ EVERY ESSENTIAL SHORTCUT - With the SYNERLOGIC Reference Keyboard Shortcut Sticker, you have the most important shortcuts conveniently placed right in front of you. Easily learn new shortcuts and always be able to quickly lookup commands without the need to “Google” it.
- 💻✔️ Work FASTER and SMARTER - Quick tips at your fingertips! This tool makes it easy to learn how to use your computer much faster and makes your workflow increase exponentially. It’s perfect for any age or skill level, students or seniors, at home, or in the office.
- 💻 ✔️ New adhesive – stronger hold. It may leave a light residue when removed, but this wipes off easily with a soft cloth and warm, soapy water. Fewer air bubbles – for the smoothest finish, don’t peel off the entire backing at once. Instead, fold back a small section, line it up, and press gradually as you peel more. The “peel-and-stick-all-at-once” method only works for thin decals, not for stickers like ours.
- 💻 ✔️ Compatible and fits any brand laptop or desktop running Windows 10 or 11 Operating System.
- 💻 ✔️ Original Design and Production by Synerlogic Electronics, San Diego, CA, Boca Raton, FL and Bay City, MI, United States 2020. All rights reserved, any commercial reproduction without permission is punishable by all applicable laws.
6. Split into rows or change the result’s orientation
Choose rows or columns based on how the separated values need to be used next. With TEXTSPLIT, supply a row delimiter to return results down rows instead of across columns. Power Query also offers row output in its advanced split options.
If a split has already produced results in columns and you need them arranged vertically, copy the results and use Paste Special > Transpose. Microsoft describes this option in its split-cell guidance.
Quick Recap
Which Excel split method should you choose?
| Method | Best fit | What to know |
|---|---|---|
| Text to Columns | A one-time split with a consistent delimiter | Preview the result and protect data in the destination cells. |
| TEXTSPLIT | A formula result that spills into columns or rows | Microsoft documents it for Microsoft 365 and Excel 2024, including Mac. |
| Flash Fill | A recognizable pattern that is not consistently delimited | Review inferred results; the method depends on the examples and data consistency. |
| Power Query | Repeatable cleanup of imported or recurring datasets | Choose left-most, right-most, or each delimiter occurrence, and columns or rows. |
| Text functions | Targeted extraction controlled by formulas | Formulas rely on predictable input rules and may need to handle exceptions. |
| Transpose | Changing already split column results into rows, or vice versa | Copy the split results, then use Paste Special > Transpose. |
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
