在Excel | 您所在的位置:网站首页 › vba分隔符必须使用符号 › 在Excel |
将数据导入Excel时,数据可以采用多种格式,具体取决于提供数据的源应用程序。 例如,它可以包含客户或员工的名称和地址,但这一切最终都以连续文本字符串的形式出现在工作表的一列中,而不是被分离成单独的列,如名称、街道、城市。 可以使用公共分隔符拆分数据。分隔符字符通常是逗号、制表符、空格或分号。此字符分隔文本字符串中的每个数据块。 使用分隔符的一大优点是它不依赖于文本中的固定宽度。分隔符精确指示拆分文本的位置。 您可能需要分割数据,因为您可能希望使用地址的某个部分对数据进行排序,或者能够对特定组件进行筛选。如果数据在数据透视表中使用,则可能需要将名称和地址作为数据透视表中的不同字段。 本文将向您展示八种方法,通过使用分隔符来指示拆分点,将文本拆分为所需的组件部分。 表的内容 样本数据以上示例数据将用于以下所有示例。下载示例文件以获取示例数据以及基于分隔符提取数据的各种解决方案。 用于拆分文本的Excel函数有几个Excel函数可以用来分割和操作单元格内的文本。 左函数这个左边函数返回文本左侧的字符数。 语法 =左(文本,[编号]) 文本–这是您希望从中提取的文本字符串。它也可以是工作簿中的有效单元格引用。 数字[可选]–这是您希望从文本字符串中提取的字符数。该值必须大于或等于零。如果该值大于文本字符串的长度,则将返回所有字符。如果省略该值,则假定该值为1。 右函数这个正确的函数返回文本右侧的字符数。 语法 =右(文本,[数字])这些参数的工作方式与左边上述功能。 查找函数这个找到函数返回指定文本在文本字符串中的位置。这可用于定位分隔符字符。请注意,搜索区分大小写。 语法 =查找(潜文本,文本,[开始]) 潜台词-这是一个文本字符串,你想要搜索。 文本–这是要搜索的文本字符串。 开始[可选]–搜索的起始位置。 透镜函数这个伦恩函数将按文本字符串的字符数给出长度。 语法 = LEN (Text) 文本-这是您想要确定字符计数的文本字符串。 使用LEFT、RIGHT、FIND和LEN函数提取数据使用示例数据的第一行(B3),可以组合这些函数,使用分隔符将文本字符串拆分为多个部分。 =查找(“,”,B3)你使用找到函数获取第一个分隔符字符的位置。这将返回值18. = left (b3, find (",", b3) - 1)然后您可以使用左边函数提取文本字符串的第一个组成部分。 注意找到获取第一个分隔符的位置,但需要从中减去1,以便不包含分隔符字符。 这会回来的塔比·奥哈拉甘. = right (b3, len (b3) - find (",", b3))获取文本字符串的下一个组件要复杂得多。您需要使用上面的公式从文本中删除第一个组件。 该公式获取原始文本的长度,查找第一个分隔符位置,然后计算该分隔符之后的文本字符串中还剩下多少字符。 这个正确的函数然后截断第一个分隔符之前(包括第一个分隔符)的所有字符,以便在找到每个分隔符字符时,文本字符串变得越来越短。 这会回来的克罗地亚格里达丹尼斯公园056号,44273 您现在可以使用找到查找下一个分隔符和左边函数提取下一个组件,使用与上面相同的方法。 对所有分隔符重复此操作,将文本字符串拆分为多个组成部分。 FILTERXML函数作为一个动态数组如果您使用的是Excel for Microsoft 365,则可以使用FILTERXML函数拆分文本,并将输出作为动态数组。 通过将分隔符字符更改为XML标记,可以将文本字符串转换为XML字符串,从而拆分文本字符串。这样你就可以用FILTERXML函数提取数据。 XML标记是用户定义的,但在本例中,s表示子节点,t表示主节点。 =“”&替换(B2,”,“”&“”使用上述公式将XML标记插入到文本字符串中。 名称街道城市国家邮政编码这将在示例中返回上述公式。 请注意,每个定义的节点后面都有一个带反斜杠的结束节点。这些XML标记定义了文本每个部分的开始和结束,并且有效地起到了与分隔符相同的作用。 =转置(FILTERXML(“”&SUBSTITUTE(B3),“,”,“”&“”,“/s”))上面的公式将把XML标记插入到原始字符串中,然后使用这些标记将项目拆分为一个数组。 如上所示,数组将每个项溢出到一个单独的单元格中。使用转置函数会导致数组水平溢出,而不是垂直溢出。 用于拆分文本的FILTERXML函数如果您的Excel版本没有动态数组,那么仍然可以使用FILTERXML函数提取单个项。 t = FILTERXML(“ ”和替代(B3 , ",", " 年代> " ) & " 年代> ”、“/ / s”)现在您可以使用上面的方法将字符串分成几部分FILTERXML公式 这将返回第一部分塔比·奥哈拉甘. =FILTERXML(“”&SUBSTITUTE(B3),“,”,“”&“”,“//s[2]”)要返回下一节,请使用上面的公式。 这将返回文本字符串的第二部分056年丹尼斯公园. 您可以使用相同的模式返回示例文本的任何部分,只需更改[2]在相应的公式中找到。 快速填充分割文本Flash Fill允许您输入一个示例,说明您希望如何分割数据。 你可以看看这个使用闪存填充清理数据指南为更多的细节。 然后选择要分割数据的第一个单元格,然后单击“快速填充”。Excel将填充示例中的其余行。 使用示例数据,输入名称进细胞C2然后塔比·奥哈拉甘进细胞C3. Flash fill应自动填充样本数据中的剩余数据名称。如果没有,您可以选择单元格补体第四成份,然后单击闪光填充中的图标数据工具工作组数据选项卡的Excel ribbon。 类似地,您可以添加大街进细胞D2,城市进细胞E2,国家进细胞地上二层,邮政编码进细胞G2. 分别选择后续单元格(D2到G2),然后单击“闪光填充”图标。其余的文本组件将填充到这些列中。 文本到列命令分割文本此Excel功能可用于根据分隔符字符将单元格中的文本拆分为多个部分。 选择整个样本数据范围(B2:B12)。 点击数据Excel功能区中的选项卡。 点击文本到列中的图标数据工具Excel功能区组和向导将出现,以帮助您设置如何拆分文本。 选择分隔在选项按钮上。 按下一个按钮。 选择逗号作为分隔符,并取消选中任何其他分隔符。 按下一个按钮。 “数据预览”窗口将显示如何分割数据。选择放置输出的位置。 点击完成按钮。您的数据现在将显示在工作表的列中。 将数据转换为CSV文件这只适用于逗号作为分隔符,因为CSV(逗号分隔值)文件依赖逗号分隔值。 打开记事本,复制并粘贴样本数据到其中。您可以通过在Windows任务栏左侧的搜索框中输入“记事本”或在应用程序列表中找到它来打开记事本。 将数据复制到记事本后,使用文件➜另存为从菜单中。输入一个后缀为。csv的文件名,例如Split Data.csv。 然后可以在Excel中打开此文件。在“浏览器文件类型”下拉列表中选择csv文件,然后单击“确定”。您的数据将自动与每个组件一起显示在单独的列中。 VBA来分割文本VBA是Excel背后的编程语言,允许您编写自己的代码来处理数据,甚至创建自己的函数。 要访问Visual Basic编辑器(VBE),请使用Alt+F11. Sub SplitText()Dim MyArray()作为字符串,计数长度为,i作为n=2到12的变量MyArray=Split(单元格(n,2),“,”),计数=3对于MyArray单元格中的每个i(n,计数)=i计数=Count+1下一个i下一个n End Sub点击插入在菜单栏中,单击模块. 将为模块显示一个新窗格。粘贴在上面的代码中。 这段代码创建了一个名为MyArray. 然后,它遍历样本数据(第2行到第12行),并使用VBA Split函数填充MyArray. split函数使用逗号分隔符,因此文本的每个部分都成为数组的一个元素。 计数器变量设置为3,表示列C,这将是显示拆分数据的第一列。 然后,代码遍历数组中的每个元素,并用元素填充每个单元格。单元格引用基于行的n,列的Count。 的变量计数在每个循环中递增,以便数据在整个行中填充,然后向下填充。 拆分文本的电源查询Excel中的Power Query允许使用分隔符将列操纵成多个部分。 相关职位: 电源查询简介 电源查询提示和技巧 介绍电源查询M代码首先要做的是定义数据源,即您输入到Excel工作表中的示例数据。 点击数据选项卡,然后单击获取数据在获取和转换数据功能区的组。 点击从文件在第一个下拉列表中,然后单击从工作簿在第二个下拉列表中。 这将显示一个文件浏览器。找到示例数据文件(已打开的文件)并单击好啊. 将显示一个导航弹出窗口,显示工作簿中的所有工作表。单击包含示例数据的工作表,这将显示数据的预览。 在左侧窗格中展开数据树以显示现有数据的预览。 点击转换数据这将显示电源查询编辑器。 确保高亮显示包含数据的单列。点击分裂柱中的图标变换功能区的组。点击按分隔符在下拉列表中显示。 这将显示一个弹出窗口,允许您选择分隔符。默认值为逗号。 点击好啊数据将被转换成单独的列。 点击关闭并加载在关组,并将新工作表添加到工作簿中,其中包含新格式的数据表。 Power Pivot计算列以拆分文本可以使用Power Pivot通过计算列拆分文本。 点击力量主选项卡,然后单击添加到数据模型中的图标桌子组。 您的数据将被自动检测,并会弹出一个显示位置的窗口。如果这不是正确的位置,则可以在此处重新设置。 离开我的表有标题复选框取消勾选弹出,因为我们想分割头部。 点击好啊然后会显示一个预览屏幕。 右键单击数据列的标题(第1列),然后单击弹出菜单中的“插入列”。这将插入一个可输入公式的计算列。 = LEFT ([Column1], FIND (",", [Column1]) - 1)在公式栏中,插入上面的公式。 其工作方式与本文方法1中描述的函数类似。 这个公式将提供名称组件在文本字符串中。 使用与第一个计算列相同的方法插入另一个计算列。 = LEFT (RIGHT ([Column1], LEN ([Column1]) - LEN ([Column1]) - 1), FIND (",", RIGHT ([Column1], LEN ([Column1]) - LEN ([Column1]) - 1)) - 1))将上述公式插入公式栏。 这是一个复杂的公式,您可能希望使用多个计算列将其拆分为多个部分。 这将提供大街文本字符串中的组件。 可以继续修改公式,为文本字符串的所有其他组件创建计算列。 数据透视表的问题在于它既需要文本值也需要数值。由于示例数据仅为文本,因此需要添加一个数值。 单击列表中的第一个单元格添加一列列,然后在公式栏中输入公式=1。 这将在该列的所有位置添加值1。点击数据透视表中的图标家功能区的选项卡。 点击数据透视表在弹出菜单中。在第一个弹出窗口中指定透视表的位置,然后单击好啊. 如果数据透视表字段窗格不会自动显示,请右键单击透视表骨架并选择显示字段列表.点击计算列在字段列表中,并将其放置在排窗 我们的数据透视表现在将显示文本字符串的各个组件。 结论如果不知道如何将每个项提取到它自己的单元格中,那么处理逗号或其他分隔符分隔的数据可能会很麻烦。 谢天谢地,Excel有很多选项可以帮助完成这项常见任务。 你喜欢用哪一种? |
CopyRight 2018-2019 实验室设备网 版权所有 |