Лучший способ удалить начальные нули из нечисловых значений в Excel

Question

У меня есть много ячеек на листе Excel, использующих 9 символов от 0 до 9 и AZ, с некоторым числом нулей с префиксом:

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

Я хотел бы удалить ведущие нули, чтобы значения стали:

123 
1DA2 
9Q5
L210A
14A0
A5500
K002200

Как бы я сделал это в формуле Excel? Я бы предпочел избегать использования макроса VBA.

Answer 1

Вот решение, которое интенсивно использует клетки, но верно.

Поместите свои данные в столбец А.

В B1 поместите формулу:

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

Это проверяет один ведущий ноль и удаляет его.

Скопируйте эту формулу вправо на столько столбцов, сколько может быть символов в ваших данных (в данном случае 9, так что вы попадете в столбец J). Скопируйте его для каждой строки данных.

Последний столбец содержит ваши данные, лишенные лидирующих нулей.

Answer 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))

Answer 3

Это сложная проблема, связанная с функцией рабочего листа. Следующее будет работать, но только если нули, которые не являются ведущими, идут только по одному за раз, и нет никаких завершающих нулей и никаких встроенных пробелов.

Он заменяет все нули пробелами, обрезает их (все, кроме встроенных одиночных пробелов), а затем заменяет нули.

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

Answer 4

Это решение работает для входных значений, которые все ровно девять символов.

Сначала настройте фиксированную таблицу из десяти строк, которая содержит следующие числа в формате текста.

000000000

000000001

000000010

000000100

000001000

000010000

000100000

001000000

010000000

100000000

Допустим, таблица находится в ячейках от A1 до A10

скажем, ваше входное значение находится в ячейке B1, а ваша ячейка результата - C1

Используйте следующую формулу в ячейке C1

= ПРАВЫЙ (В1, ПОИСКПОЗ (В1, $ A $ 1:$ A $ 10,1)-1)

Answer 5

Для данных в A1 используйте следующую формулу:

= ВПРАВО (A1, LEN (A1)-MAX ((FINDB (REPT ("0", ROW (A $ 1:A $ 100)), A1 & "-" & REPT ("0", 100))= 1)* ROW (А $ 1:$ 100)))

ввод по Ctrl+Shift+Enter. Он работает для строк длиной до 100 символов.