Description
In this video I'll show you how to split cells in Microsoft Excel.
Timestamps:
Introduction: 0:00
Split Cells with Text to Columns: 0:06
Split Cells with TEXTSPLIT: 1:34
Split a Merged Cell: 2:14
Conclusion: 2:34
Check out my website for other great tutorials about all kinds of technology: https://maxdalton.how/
Support this channel by becoming a member: https://www.youtube.com/channel/UCBbDWMccTJCL0WmbMHNLZIw/join
#excel #microsoftexcel #exceltutorial #techtips #tutorial
Video Transcript:
Step 1. Open your Excel spreadsheet. For this example, I'm using a mail order sheet for a comic book shop, where the Ship To column has the city, state, and ZIP code together in one cell, separated by commas.
Step 2. Click and drag to select the cells you want to split, from cell D2 to cell D21.
Step 3. Click the "Data" tab at the top of the screen, and then click "Text to Columns" in the Data Tools group. The Convert Text to Columns Wizard opens.
Step 4. Make sure "Delimited" is selected, and then click "Next."
Step 5. Uncheck the "Tab" box, check the "Comma" box, and then click "Next." The preview at the bottom shows the city, state, and ZIP code in separate columns.
Step 6. Click in the Destination box, and then click cell E2 in your spreadsheet, so your original data in column D stays the same.
Step 7. Click "Finish." The city, state, and ZIP code now appear in columns E, F, and G. If the city names are cut off, widen column E so you can see them. Since there's a space after each comma in the original data, you may see a leading space in front of the state, which you can clean up with the TRIM function.
If you have Microsoft 365, the TEXTSPLIT function splits the text and handles that extra space for you, and it updates automatically if the original data changes.
Step 1. Click in cell E2, and then type the formula you see on the screen.
=TEXTSPLIT(D2,", ")
Step 2. Press "Enter" on your keyboard. The city, state, and ZIP code spill into cells E2, F2, and G2.
Step 3. Click cell E2, and then double-click the fill handle in the bottom right corner of the cell to copy the formula down the rest of the rows.
If you're trying to split a cell that was merged, click the merged cell, click the arrow next to "Merge and Center" on the Home tab, and then click "Unmerge Cells." Keep in mind Excel can't split a single regular cell into two cells, so splitting the contents into separate columns is the way to go.
