从文本字符串中提取数字的公式 [英] Formula to extract numbers from a text string

问题描述

如何从Excel或Google表格中的文本字符串中仅提取数字?例如:

How could I extract only the numbers from a text string in Excel or Google Sheets? For example:

A1 - a1b23eg67
A2 - 15dgrgr156

所需结果是

B1 - 12367
B2 - 15156

推荐答案

您可以使用Google表格中的捕获组来实现

You can do it with capture groups in Google Sheets

=REGEXREPLACE(A1,ʺ(\d)|.ʺ,ʺ$1ʺ)

任何与方括号内容(数字)匹配的内容都将被复制到输出中，其他任何内容都将替换为空字符串.

Anything which matches the contents of the brackets (a digit) will be copied to the output, anything else replaced by an empty string.

请参阅@Max Makhrov对此问题的答案

Please see @Max Makhrov's answer to this question

或

=regexreplace(A1,ʺ[^\d]ʺ,ʺʺ)

删除所有不是数字的东西.

to remove anything which isn't a digit.

这篇关于从文本字符串中提取数字的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！