如果单元格包含一些用逗号或任何其他标记分隔的文本,并且您想将它们分成多列,则应遵循本指南。本文将帮助您在不使用任何第三方软件的情况下拆分Excel和Google 表格中的(Google Sheets)逗号分隔文本。(split comma-separated text)
假设您有一个包含某些人的全名的电子表格,并且您希望将名字和姓氏分成两列。它可以包含产品列表、您的服务和价格等任何内容,而不是名称。如果行数很少,您可以手动执行。当您需要对Microsoft Excel或Google 表格(Sheets)中的数百列执行相同操作时,就会出现问题。要摆脱耗时的工作,您可以查看此分步指南。
如何在Excel中将(Excel)文本拆分(Split Text)为列(Columns)
要在Excel(Excel)中拆分逗号分隔的文本,请按照下列步骤操作 -
- 打开电子表格并选择所有单元格。
- 转到数据选项卡。
- 单击(Click)文本(Text)到列选项(Columns)。
- 使用分隔(Delimited)选项并选择分隔符。
- 选择要显示结果的单元格。
- 保存您的文件。
首先,您需要在Microsoft Excel中打开电子表格并选择所有包含命令分隔文本的单元格。之后,转到“数据(Data )”选项卡并单击“文本到列(Text to Columns )”选项。
您将获得两个选项 - Delimited和Fixed Width。您需要选择Delimited选项并单击Next按钮。
之后,选择一个Delimiter。由于您的电子表格有逗号,您需要从列表中选择它。但是,可以选择其他内容,例如Tab、Semicolon、Space或任何自定义内容。最后,单击下一步(Next )按钮。
现在您需要选择一个单元格作为第一列。
回到指南,您需要删除默认目标值(Destination ),然后选择要显示输出的单元格。最后,单击完成(Finish )按钮。
无论您选择什么单元格或列,下一个单元格或列都将包含逗号分隔文本的最后一部分。
例如,如果——
BB 部分将保存在 C1 单元格中。如果您的源文本包含两个逗号,最后一部分将显示在 D1 单元格中。
现在,您可以立即找到结果。
如何在Google表格中拆分逗号分隔的文本
在这种情况下,Google 表格(Google Sheets)不如Microsoft Excel智能。尽管您可以拆分所有以逗号分隔的文本,但您不能保留源列。如果您对此感到满意,则应该完成这些步骤。
- 选择(Select)电子表格中的所有单元格。
- 转到Data > Split为列。
- 如果需要,更改分隔符。
首先,您需要选择所有包含逗号分隔值的单元格。之后,展开数据(Data )菜单并选择将文本拆分为列(Split text to columns)选项。
默认情况下,它会自动检测分隔符或定界符。如果您使用的是独特的东西或者它不能完美地完成工作,您可以展开分隔符(Separator)下拉列表,并根据您的要求选择其他东西。
就是这样!
How to split Text to Columns in Excel and Google Sheets
Іf a cell contains some text sepаrated by comma or anу other mark, and you want to split them into multiple columns, you should follow this guide. This аrticle will help you split comma-separated text in Excel and Google Sheets without using any third-party software.
Let’s assume that you have a spreadsheet containing the full name of some people, and you want to split the first name and last name into two columns. Instead of names, it can include anything like product list, your services, and price, or anything else. If the number of rows is small, you can do it manually. The problem occurs when you need to do the same with hundreds of columns in either Microsoft Excel or Google Sheets. To get rid of that time-consuming work, you can check out this step by step guide.
How to Split Text to Columns in Excel
To split comma-separated text in Excel, follow these steps-
- Open the spreadsheet and select all cells.
- Go to the Data tab.
- Click on the Text to Columns option.
- Use the Delimited option and select a delimiter.
- Select a cell where you want to display the result.
- Save your file.
To get started, you need to open the spreadsheet in Microsoft Excel and select all the cells that contain command separated texts. After that, go to the Data tab and click on Text to Columns option.
You will get two options – Delimited and Fixed Width. You need to choose the Delimited option and click the Next button.
After that, select a Delimiter. As your spreadsheet has a comma, you need to select it from the list. However, it is possible to choose something else like Tab, Semicolon, Space, or anything custom. At last, click on the Next button.
Now you need to choose a cell to be the first column.
Coming back to the guide, you need to remove default Destination value, and select a cell where you want to display the output. At last, click the Finish button.
Whatever cell or column you choose, the very next one will have the last part of the comma-separated text.
For instance, if-
- You have a text like this – AA, BB
- Select B1 for AA
The BB part will be saved in the C1 cell. If your source text contains two commas, the very last part will be displayed in the D1 cell.
Now, you can find the result immediately.
How to split comma separated text in Google Sheets
In this case, Google Sheets is not as smart as Microsoft Excel. Although you can split all comma-separated texts, you cannot retain the source column. If you are okay with that, you should walk through these steps.
- Select all cells in your spreadsheet.
- Go to Data > Split texts to columns.
- Change separator if you want.
At first, you need to select all the cells that contain comma-separated values. After that, expand the Data menu and select Split text to columns option.
By default, it detects the separator or delimiter automatically. If you are using something unique or it doesn’t do the job flawlessly, you can expand the Separator drop-down list, and choose something else according to your requirement.
That’s it!