SPLIT trong Google sheet

If you work with data that needs to be split up like this—like separating first and last names, or separating an address that's in one column into street address, city, state, and ZIP Code columns—spreadsheets will take care of it for you.

Here's how to split text in Excel and Google Sheets.

How to split text in Microsoft Excel

We'll use names for our example, to keep things simple. If the names are already in a spreadsheet, open the spreadsheet in Excel—or, if the list is somewhere else, like in a document or on a website, make a new spreadsheet in Excel, then copy and paste the text into the spreadsheet.

Tip: Before you start, make sure there are a few empty columns to the right of your text, so Excel will have room to split out your text. Otherwise, it might end up overwriting your other data.

1. Select the column with the text you want to split.

2. Click the Data tab in the top menu, and click Text to Columns—you should see it around the middle of the toolbar. That will open the split text dialog.

3. The Delimited option should be checked by default—this specifies that the text you want to separate uses spaces, tabs, or other delimiters. Click Next.

4. Now, choose what's separating your text. For our list of names, we'll check the box beside Space. If you have a list of comma-separated values, check the Comma option instead. (Check Other if your character is not listed, then type that character in the box. Say, for instance, you want the domain names from a list of email addresses. You'd check Other, type an @ symbol in the box, and Excel could then split the username and domain from email addresses.)

5. Excel will then show you a preview of how the finished data will look, and will ask you if the split text is plain text or a date—and if the latter, you can set the correct date format.

6. Excel might tell you to select where you want the new columns, in which case, just click on each designated column in your sheet.

7. Click Finish, and you're done. Your data will be split into the columns you want.

Note: If the data in the first column doesn't all follow a standard format—perhaps where some names include extra data like Bob Tester Jr. or Mr. Bob Tester—Excel will split that extra data into extra columns. You might need to do some cleanup unless your data all matches the same style.

Using Apple Numbers instead? Numbers doesn't have a built-in tool to split text, but on a Mac, you can

use this Automator script to split text. Or, if you're using Microsoft Word, you can convert text to columns—select the text, then click the Insert tab, then click Table > Convert. You can then separate text by spaces, commas, tabs, special characters, and even paragraphs.

How to split text in Google Sheets

There are even fewer steps to split text in one cell into multiple cells in Google Sheets.

1. Import your spreadsheet into Google Sheets or make a new spreadsheet and paste in the data you want to split.

2. Select the text or column, then click the Data menu and select Split text to columns...

3. Google Sheets will open a small menu beside your text where you can select to split by comma, space, semicolon, period, or custom character.

4. Select the delimiter your text uses, and Google Sheets will automatically split your text.

---

Want to automate this process so the data is pulled from one app, split, and then sent to another? Use Zapier's Split Text function to automatically split text and avoid manual data cleanup. Learn more about separating first and last names from fields in your Zaps—the automated workflows you create with Zapier.

Zapier is a no-code automation tool that lets you connect your apps into automated workflows, so that every person and every business can move forward at growth speed. Learn more about how it works.

This article was originally published in September 2017 and has since had contributions from Elena Alston.

1. Trang chủ
»
2. Ứng dụng hàm
»
3. Kết hợp hàm JOIN SPLIT TRANSPOSE loại bỏ các dòng trống trên Google Sheets

Kết hợp hàm JOIN SPLIT TRANSPOSE loại bỏ các dòng trống trên Google Sheets

• 02/08/2021
• Ứng dụng hàm

Kết hợp hàm JOIN SPLIT TRANSPOSE loại bỏ các dòng trống trên Google Sheets.

Các bài viết khác

Tổng hợp danh sách các hàm thông dụng trên Excel so sánh với Google Sheets

Trọn bộ công thức Query trong Google Sheets

Các nhóm hàm Large Small Int Round Code Char Rept trên Google Sheets

Hàm NetworkDays tính số ngày làm việc trừ ngày cuối tuần, ngày lễ trên GoogleSheets

Hàm DatedIf tính khoảng cách về năm, tháng, ngày dễ dàng trên GoogleSheets

Những ứng dụng thực tế từ hàm Sparkline trên Google Sheets

Hàm mảng nối mảng {dải ô; dải ô} trên Google Sheets

So sánh hàm Match kết hợp Index với Vlookup trên Google Sheets

Hàm ImportHTML giúp bạn nhập dữ liệu bảng từ website tức thì trên Google Sheets

Hàm UNIQUE xóa bản trùng lặp tự động cho cả mảng dữ liệu trên Google Sheets