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

104508
Chris Farmer

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

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

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

123  1DA2  9Q5 L210A 14A0 A5500 K002200

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

11
Если у вас есть еще несколько несоответствий в отношении данных, возможно, я смогу найти более конкретное решение. Lance Roberts 15 лет назад 0
Благодарю. Значения имеют начальные нули, так что всего их 9 символов. Кроме этого, значения представляют собой строки из цифр 0-9, смешанные с заглавной буквой AZ. Возможно, что конечный ноль существует и является частью действительного значения и не должен быть обрезан. Chris Farmer 15 лет назад 0
Чтобы быть явным, никогда не бывает встроенных пробелов. Просто 0-9 и AZ. Chris Farmer 15 лет назад 0
Отредактировано, чтобы включить эти конечные ноль примеров Chris Farmer 15 лет назад 0
@ Крис, я могу сделать это, если мы знаем максимальное количество нулей, которые когда-либо появятся встроенными в строку, или трейлинг после (хотя это будет длинная формула). Lance Roberts 15 лет назад 0
Клетки дешевые, но не обязательные ... Toc опубликовал новый блестящий ответ :-) http://superuser.com/questions/42844/best-way-to-remove-leading-zeros-from-a-non-numeric -значение-в-первенствует / 44279 # 44279 Arjan 15 лет назад 0

5 ответов на вопрос

13
Isaac Moses

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

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

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

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

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

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

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

После многих попыток это единственная версия, которая будет работать, не прибегая к VBA. skamradt 15 лет назад 0
Комментарий @Richard: Мне нравится решение Исаака. Если более одного лидирующего нуля, продолжайте копировать из ячеек, содержащих его формулу и вставляя значения, только в исходные ячейки: каждый раз будет удаляться еще один лидирующий ноль (если есть). Ivo Flipse 13 лет назад 0
6
Andreas Flueckiger

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

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

Если для строк типа 0or возвращается один ноль, можно 00использовать следующую формулу:

=IF(A1="","",RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(LEFT(A1,LEN(A1)-1)&" ","0",""),1),LEFT(A1,LEN(A1)-1)&" ")+1))
Этот ответ работает с несколькими ведущими нулями и кажется наиболее полным и простым из ответов, упомянутых здесь. Peter 9 лет назад 0
5
Lance Roberts

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

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

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"0"," "))," ","0")
Почему * только если нули, которые не являются ведущими, приходят только по одному за раз *? А как насчет `= CLEAN (ЗАМЕНА (TRIM (ЗАМЕНА (A1 & CHAR (9)," 0 "," "))," "," 0 "))` (надеясь, что `CLEAN` удалит вкладки; если нет; используйте другое значение в `CHAR`) Arjan 15 лет назад 0
@Arjan, я тестировал TRIM, и он заменил встроенные несколько пробелов одним пробелом, но сейчас я собираюсь изучить вашу информацию и посмотреть, что я могу найти. (Я использую 2003). Lance Roberts 15 лет назад 0
Функция CLEAN сохраняет встроенные пробелы, но также сохраняет все, кроме первого начального пробела. Lance Roberts 15 лет назад 0
Я добавил CLEAN, чтобы удалить CHAR (9), который я добавил; использование CLEAN для значения TRIMmed не должно иметь начальных пробелов ...? Arjan 15 лет назад 0
Да, но когда CHAR (9) был добавлен (к каждому нулю), он также добавил себя к ведущим нулям. Я вижу, что вы использовали его только для сохранения конечных нулей, и я включу его в ответ. Lance Roberts 15 лет назад 0
На самом деле, это сохраняет только один ноль в конце, если есть несколько конечных нулей, то это не работает. Lance Roberts 15 лет назад 0
Да, вам придется использовать TRIM для пробелов, к сожалению, EXCEL не имеет функции LTRIM, которая решит все проблемы. Lance Roberts 15 лет назад 0
Кстати: я не добавил CHAR (9) к * каждому * нулю, не так ли? Я только добавил суффикс, когда думал, что TRIM повлияет только на начальные и конечные пробелы. (Таким образом, пытаясь удостовериться в отсутствии конечного пространства CHAR (32), добавьте суффикс CHAR (9).) Arjan 15 лет назад 0
5
Kije

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

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

000000000

000000001

000000010

000000100

000001000

000010000

000100000

001000000

010000000

100000000

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

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

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

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

Умный! Что касается третьего параметра в функции `MATCH`: * 1 = найти наибольшее значение, которое меньше или равно lookup_value * Arjan 15 лет назад 0
4
Toc

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

** Ничего себе! ** Некоторые небольшие заметки: Ctrl-Shift-Enter (Cmd-Shift-Return на Mac) создает формулу массива. Вставьте формулу как обычно, но вместо Enter нажмите Ctrl-Shift-Enter, чтобы сохранить ее. Затем он будет отображаться между фигурными скобками. В OpenOffice.org на Mac я не мог перетащить формулу для следующих строк (так как тогда получилась бы некоторая формула супер-массива). Но копирование и вставка работает, так что я уверен, что есть и ярлык. И OpenOffice.org не знает FINDB; похоже, что использование FIND тоже работает. Arjan 15 лет назад 0
Элегантное решение! wilson 12 лет назад 0

Похожие вопросы