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

Следующая формула не требует дополнительных ячеек и не должна вводиться как формула массива:

  = 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), ВЛЕВО (A1, LEN (A1) -1) & "") +1))  

• Этот ответ работает с несколько ведущих нулей и кажется наиболее полным и простым из упомянутых здесь ответов. — Питер 17 авг., 15:15

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

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

  = ПОДСТАВИТЬ (ОБРЕЗАТЬ (ПОДСТАВИТЬ (A1, "0", "")), "", "0")  

• Почему только в том случае, если нули, которые не являются ведущими, появляются только по одному ? А как насчет = CLEAN (SUBSTITUTE (TRIM (SUBSTITUTE (A1 & CHAR (9), "0", "")), "", "0")) (надеясь CLEAN удалит вкладки; в противном случае используйте другое значение в CHAR ) — Арджан, 17 сентября 2009 г., 19:52
• @Arjan, я тестировал TRIM, и он заменил встроенные несколько пробелов на единое пространство, но сейчас я собираюсь изучить вашу информацию и посмотреть, что я смогу найти. (Я использую 2003 год). — Лэнс Робертс, 17 сен., 20:15
• Функция CLEAN сохраняет встроенные пробелы, но также сохраняет все пробелы, кроме первого ведущего. — Лэнс Робертс, 17 сентября 2009 г., 20:21
• Я добавил CLEAN, чтобы удалить CHAR (9), который я добавил; использование CLEAN для значения TRIMmed не должно иметь начальных пробелов …? — Арджан 17 сен., 20:23
• Да, но когда CHAR (9) был добавлен (к каждому нулю), он также добавлялся к ведущим нулям. Я вижу, что вы использовали его только для сохранения конечных нулей, и я включу его в ответ. — Лэнс Робертс, 17 сентября 2009 г., 20:41

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

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

000000000

000000001

000000010

000000100

000001000

000010000

000100000

001000000

010000000

100000000

Давайте скажи табл e находится в ячейках с A1 по A10

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

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

= RIGHT (B1, MATCH (B1, $ A $ 1: $ A $ 10,1) -1)

• Умно! Что касается третьего параметра в функции MATCH : 1 = найти наибольшее значение, меньшее или равное lookup_value — Арджан, 18 сен. 2009 в 7:09

Для данных в 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 символов.

• Вау ! Некоторые мелкие примечания: Ctrl-Shift-Enter (Cmd-Shift-Return на Mac) создает формулу массива. Вставьте формулу как обычно, но вместо Enter нажмите Ctrl-Shift-Enter, чтобы сохранить ее. Затем он будет отображаться между фигурными скобками. В OpenOffice.org на Mac я не мог перетащить формулу для следующих строк (так как тогда это дало бы некоторую формулу супер-массива). Но копирование и вставка действительно работает, поэтому я уверен, что есть какой-то ярлык. А OpenOffice.org не знает FINDB; использование FIND, похоже, тоже работает. — Арджан 20 сен., 21:39
• Элегантное решение! — Уилсон 15 нояб., 2011 в 10:44