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

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

 000000123 000001DA2 0000009Q50000L210A0000014A00000A550000K002200 

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

 123 1DA2 9Q5L210A14A0A5500K002200 

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


Вот решение, требующее большого количества ячеек, но правильное.

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

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

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

Это проверяет наличие единственного нуля в начале и удаляет его.

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

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


8

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

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

Улучшить этот ответ
ответил 02 августа 2015 в 13:27
  • Этот ответ работает с несколько ведущих нулей и кажется наиболее полным и простым из упомянутых здесь ответов. — Питер 17 авг., 15:15
добавить комментарий |

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

   = RIGHT (A1, LEN (A1) -FIND (LEFT (ПОДСТАВИТЬ (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))  

5

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

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

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

Улучшите этот ответ

Ответ

17 сен ’09 в 19:38
  • Почему только в том случае, если нули, которые не являются ведущими, появляются только по одному ? А как насчет = 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
| показать 3 дополнительных комментария

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

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

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

5

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

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

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)

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

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

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

000000000

000000001

000000010

000000100

000001000

000010000

000100000

001000000

010000000

100000000

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

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

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

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


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 символов.

Улучшите этот ответ
ответил сентябрь 20 ’09 г t 19:11
  • Вау ! Некоторые мелкие примечания: 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
добавить комментарий |

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



4 простых шага, чтобы сохранить начальный ноль в Excel!

При работе с электронными таблицами может потребоваться столбец с ведущими нулями. Например, давайте использовать идентификационный номер, если бы я ввел начало своего идентификатора «0010», Excel автоматически удалил бы первые два нуля. Вот как сохранить начальный ноль в Excel:

  1. Щелкните правой кнопкой мыши столбец, в котором вы хотите начать ноль, выберите Формат ячеек
  2. Выберите Custom из списка категорий.
  3. В поле Type введите необходимое количество цифр. . Идентификатор SMU состоит из 8 цифр, которые мы будем использовать в нашем примере ниже.. ( Примечание. Вам не нужно добавлять кавычки к строке.)

4. Щелкните ОК. Теперь столбец будет использовать созданный вами формат!

Связанный

Оцените статью
logicle.ru
Добавить комментарий