在Google Spreadsheet中为相邻列拆分数据 [英] Splitting up data in Google Spreadsheet for adjacent columns

问题描述

我想将数据从电子表格列(工作ID#，姓氏，名字)拆分为两个相邻的列..因此，我有3个单独的列用于此数据.

I would like to split up data from a spreadsheet column (Work ID#, last name, first name) into two adjacent columns.. so that I have 3 separate columns for this data.

这里是我的电子表格的链接: https://docs.google.com/spreadsheets/d/1gsLYnrNHEMbZTML1YZG-NhtDYO_FAQaAPwGFvtqn2kg/edit?usp=sharing

Here is a link to my spreadsheet: https://docs.google.com/spreadsheets/d/1gsLYnrNHEMbZTML1YZG-NhtDYO_FAQaAPwGFvtqn2kg/edit?usp=sharing

推荐答案

我正在根据您工作表(last_name)中的B列包含您的合并数据(employee_ID last_name，first_name)进行工作.

I'm working on the basis that column B in your sheet (last_name) contains your merged data, (employee_ID last_name, first_name).

您可以使用 regexreplace 和 regexextract 三个公式来完成此操作.

You can do this with three formula, using regexreplace and regexextract.

雇员ID

将其放在单元格A1中:

Put this in cell A1:

= arrayformula({"employee_ID"; iferror(if($ B2:$ B ="，，"，value(regexextract($ B2:$ B，"^.*\ d)))，"")})

名字

将其放在单元格C1中:

Put this in cell C1:

= arrayformula({"first_name"; if($ B2:$ B ="，",, regexreplace($ B2:$ B，" ^.* \，\";，，"))})

姓氏

我在部门前面创建了一个新列D.这进入单元格D1:

I created a new column D in front of department. This goes in cell D1:

= arrayformula({``last_name''; if($ B2:$ B =''``，''``regexreplace(regexreplace($ B2:$ B，''.* \ d \"，")，"\，.*"，"))}})

摘要

每个人都有一个使用 {} 的数组.数组的第一部分是""中的列标题，例如"employee_ID" .这样，如果您要在数据集上添加过滤器视图，则可以将公式保留在第1行中.然后; 是返回值，然后是下面的其余单元格: iferror(if($ B2:$ B ="，"，value(regexextract($B2:$ B，"^.* \ d"))))，").

Each one has an array using {}. The first part of the array is the column heading in "", like "employee_ID". This is so you can keep your formula in row 1 incase you want to add a filter view on the dataset. Then a ; is a return, then the rest of the cells below: iferror(if($B2:$B="","",value(regexextract($B2:$B,"^.*\d"))),"").

regexextract($ B2:$ B，"^.* \ d")在开始的 ^.* 中查找任何内容，然后输入数字\ d .

regexextract($B2:$B,"^.*\d") looks for anything at the start ^.*, then a number \d.

value()将结果转换为数字. iferror 处理找不到数字的地方.

value() converts the result to a number. iferror handles where a number can't be found.

在first_name上，以 ^.* 开头的任何内容，后跟逗号 \，，然后将空格 \ 替换为".(即已删除).

On first_name, anything at the beginning ^.* followed by a comma \, then a space \ is replaced with "" (ie. removed).

在姓氏上，.* \ d \ 替换任何.* ，后跟数字 \ d ，后跟空格 \不带任何" (即已删除)，然后从该结果中进行另一个替换，删除逗号 \，后跟任何.* .

On last_name, .*\d\ replaces anything .* followed by a number \d followed by a space \ with nothing "" (ie. removed), then from that result another replace removes a comma \, followed by anything .*.

一个单元格中的公式

如果要一次性拆分，请在新的F，G和H列中将其放入单元格F1中:

If you want to do the split in one go, in say new columns F,G and H, put this in cell F1:

= arrayformula(iferror(trim(split({$ B，"^.* \ d"))&，"®exreplace($ B2:B，"^.* \ d \"，"))}，"，"))，"))

但是，然后将employee_ID格式化为文本.

However, employee_ID is then formatted as text.

这篇关于在Google Spreadsheet中为相邻列拆分数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！