英文:
Extract up to 3 numbers from a cell in Excel
问题
I understand that you want to extract numbers from a text column in Excel. Here's a translation of your request:
我理解您想要从Excel中的文本列中提取数字。以下是您的要求的翻译：
"我有一个从A2开始的列，每一行包含一个以以下格式表示的名称和1到3个数字：
"name : number number number"
例如：
Mark：some 32 text 91 here
John32：5/6 more text
Michael：even 32 more 21 text 3
Kyle69：57 something
Philip：i 2 dont 43 know
4Chris2：3 21 7
我想要的是：
32 91
5 6
32 21 3
57
2 43
3 21 7
问题是，数字之间和周围（前后）可能有文本，数字可以是一位或两位数字（1-99）。
数字可以提取到一个单元格中，或者分别提取到1到3个不同的单元格中。如果缺少数字（数字2和/或数字3），则在其位置放置“ - ”会有额外的加分。
我使用的Excel版本是2016，没有LET()、TOCOL()、NA()、FILTER()等公式，也许还有其他一些，因此解决方案越基本越好。
提前感谢您提供的任何帮助！"
编辑：如果有人感兴趣，这是我设法解决的混乱问题，但仅在单元格中的“ : ”之后有确切2个数字时才有效，如果这两个数字之一超出1-90范围，则返回“ No. 1 ”或“ No. 2 ”。为什么是90而不是99？我只是这样感觉，获得大于90的数字是罕见的，应该检查以便更好地引起我的注意。:D
（以下是Excel公式的内容，我将其原样保留，不翻译）
英文:
I have column starting at A2 where each row contains a name and 1 to 3 numbers in the following format:
"name : number number number"
For example:
Mark : some 32 text 91 here
John32 : 5/6 more text
Michael : even 32 more 21 text 3
Kyle69 : 57 something
Philip : i 2 dont 43 know
4Chris2 : 3 21 7
What I want is:
32 91
5 6
32 21 3
57
2 43
3 21 7
The catch is, there may or not be text in-between and around(before and/or after) the numbers and the numbers can be one or two digits(1-99)
The numbers can be extracted into one cell or respectfully 1 to 3 different cells. Extra points if it puts a " - " in the place of missing numbers (number 2 and/or number 3)
The version of Excel I'm using is 2016 and does not have formulas such as LET(), TOCOL(), NA(), FILTER(), and maybe some others so the more basic the solution is, the better.
Please and thank your for any help provided in advance!
Edit: In anyone interested, this is the mess I got to work but it only works if there are exactly 2 numbers after the " : " in the cell and returns "No. 1" or "No. 2" if either of the two numbers is outside 1-90. Why 90 and not 99? I just felt like it and getting numbers >90 is rare and should be checked so an error draws my attention better. 
=IF(ISBLANK(A2);"-";IF(AND(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+2;1)*1));"No. 1";IF(AND(IF(VALUE(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)));IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);2;1)))>0; TRUE; FALSE);IF(VALUE(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)));IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);2;1)))<=90; TRUE; FALSE));IF(AND(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);2;1)))+1;1)*1);ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);2;1)))+2;1)*1));"No. 2";IF(AND(IF(VALUE(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);2;1)));IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);2;1)))+1;1)*1);2;1)))>0; TRUE; FALSE);IF(VALUE(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);2;1)));IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);2;1)))+1;1)*1);2;1)))<=90; TRUE; FALSE));MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)));IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);2;1))&" "&MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);2;1)));IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+IF(ISNUMBER(MID(A2;MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9};FIND(":";A2;1)))+1;1)*1);2;1)))+1;1)*1);2;1));"No. 2"));"No. 1")))
答案1
得分: 2
动态数组始于Excel 2016，因此此公式的结果应该会“溢出”到相邻单元格：
- 创建一个XML，方法如下：
- 用空格替换非空格分隔符。
- 替换空格，并在前面/后面添加XML分隔符。
- 使用适当的xPath使用
FILTERXML提取仅数值节点。
=转置(
FILTERXML(
"<t><s>" & SUBSTITUTE(SUBSTITUTE(A1, "/", " "), " ", "</s><s>") & "</s></t>",
"//s[number(.)=number(.)]"
)
)
如@MayukhBhattacharya在评论中建议的，如果MS文档错误，而2016中没有动态数组支持，可以在B1中输入此公式，然后横向拖动三列再向下拖动：
=IFERROR(FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE($A1,"/"," ")," ","</s><s>") & "</s></t>","//s[number(.)=number(.)]["&COLUMNS($A:A)&"]"), "")
英文:
Dynamic arrays started in Excel 2016, so the results of this formula should SPILL to the adjacent cells:
- Create an XML by
- Replacing non-space delimiters with a space.
- Replacing the spaces, and pre-pending/appending with xml delimiters
- Use
FILTERXMLwith an appropriate xPath to extract only the numeric nodes.
=TRANSPOSE(
FILTERXML(
"<t><s>" & SUBSTITUTE(SUBSTITUTE(A1, "/", " "), " ", "</s><s>") & "</s></t>",
"//s[number(.)=number(.)]"
)
)
As suggested by @MayukhBhattacharya in the comments, if it is the case that MS documentation is incorrect and you do not have dynamic arrays in 2016 with SPILLing, you can enter this formula in B1, and drag across three columns then down:
=IFERROR(FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE($A1,"/"," ")," ","</s><s>") & "</s></t>","//s[number(.)=number(.)]["&COLUMNS($A:A)&"]"),"")
答案2
得分: 1
=TRIM(CONCAT(IFERROR(--MID(A1,ROW(INDEX(A:A,FIND(":",A1)+2):INDEX(A:A,LEN(A1))),1)," ")))
这段代码从第一个找到的冒号后的第2个字符开始获取字符串中的所有字符，直到最后一个字符。每个非数字字符将被替换为一个空格字符，TRIM函数会删除前导/尾随/重复的空格字符，留下以单个空格字符分隔的数字。
在旧版的Excel中，需要使用ctrl+shift+enter输入此公式。
英文:
=TRIM(CONCAT(IFERROR(--MID(A1,ROW(INDEX(A:A,FIND(":",A1)+2):INDEX(A:A,LEN(A1))),1)," ")))
This get all characters in the string from the 2nd characters after the first found : to the last character. Each non-numeric character will be replaced with a space character and TRIM removes leading/trailing/duplicate space characters, leaving the numbers separated by a single space character.
In older Excel versions this requires being entered with ctrl+shift+enter
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论