在 Excel 中删除非数字值前导零的最佳方法

在 Excel 中删除非数字值前导零的最佳方法

我在 Excel 工作表中有许多单元格,使用 0-9 和 AZ 的 9 个字符,这些字符带有一定数量的前缀零:

000000123
000001DA2
0000009Q5
0000L210A
0000014A0
0000A5500
00K002200

我想删除前导零,以便值变成:

123
1DA2
9Q5
L210A
14A0
A5500
K002200

我该如何在 Excel 公式中执行此操作?我宁愿避免使用 VBA 宏。

答案1

这是一个细胞密集型但正确的解决方案。

将您的数据放在 A 列中。

在 B1 中输入公式:

=IF(
    LEFT(A1) = "0" ,
    RIGHT(A1, LEN(A1)-1),
    A1)

这将检查单个前导零并将其删除。

将此公式复制到右侧,与数据中字符数相同的列数(在本例中为 9,因此您将转到 J 列)。将其复制到每一行数据。

最后一列包含您的数据,去掉了前导零。

答案2

以下公式既不需要额外的单元格,也不必作为数组公式输入:

=RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1)

0如果对于像或 这样的字符串需要返回单个零00,则可以使用以下公式:

=IF(A1="","",RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(LEFT(A1,LEN(A1)-1)&" ","0",""),1),LEFT(A1,LEN(A1)-1)&" ")+1))

答案3

这是使用工作表函数解决的一个难题。下面的方法可以解决问题,但前提是非前导零每次只能出现一个,并且没有尾随零,也没有嵌入空格。

它用空格替换所有零,修剪它们(除了嵌入的单个空格之外的所有空格),然后再替换回零。

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"0"," "))," ","0")

答案4

对于 A1 中的数据,使用以下公式:

=RIGHT(A1,LEN(A1)-MAX((FINDB(REPT("0",ROW(A$1:A$100)),A1 & "-" & REPT("0",100))=1)*ROW(A$1:A$100)))

通过 Ctrl+Shift+Enter 输入。它适用于最多 100 个字符的字符串。

相关内容