英文:
PowerQuery Extract everything before alphabet from a column
问题
我正在清洗数据并尝试找出从列中提取数据的最佳方法是什么？所有变量的长度和格式都不相同，但我想提取第一个字母和它之前的所有内容。例如：
示例数据：12345A123; 23456B000; 45678D93
输出：12345A; 23456B; 45678D
英文:
I am cleaning data and trying to figure out what the best way to extract data from a column is? None of the variables are same length or format but I want to extract the first ALPHABET and everything BEFORE it. For example:
Sample Data: 12345A123; 23456B000; 45678D93
Output: 12345A; 23456B; 45678D
答案1
得分: 1
在您提供的文本中，以下是需要翻译的部分：
Add a custom column and type in:
添加一个自定义列并输入：
Splitter.SplitTextByCharacterTransition({"A".."Z", "a".."z"}, {"0".."9"})([Column1]){0}
英文:
Add a custom column and type in:
Splitter.SplitTextByCharacterTransition({"A".."Z", "a".."z"}, {"0".."9"})([Column1]){0}
答案2
得分: 1
= Text.Range([Column1], 0, Text.PositionOfAny([Column1],{"a".."z","A".."Z"}) + 1)
英文:
How about adding column, custom column, with formula
= Text.Range([Column1],0,Text.PositionOfAny([Column1],{"a".."z","A".."Z"})+1)
答案3
得分: 1
I don't think you really did a search for this. It's in Microsoft's documentation.
I started with your list:
12345A123
23456B000
45678D93
Then I clicked on "Transform," "Split Column," then "Non-Digit to Digit." As a last step, I removed the second column the split produced.
This is the output:
12345A
23456B
45678D
And here is the M code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Column1.2"})
in
#"Removed Columns"
英文:
I don't think you really did a search for this. It's in Microsoft's documentation.
I started with your list:
12345A123
23456B000
45678D93
Then I clicked on "Transform", "Split Column", then "Non-Digit to Digit". As a last step, I removed the second column the split produced.
This is the output:
12345A
23456B
45678D
And here is the M code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Column1.2"})
in
#"Removed Columns"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论