☰

Удаление цифр перед буквой без пробелов

tag-icon • tag-icon microsoft-excel-2010
Удаление цифр перед буквой без пробелов

Я хотел бы удалить цифры, включая двоеточие, из каждой строки.

Пример данных:

22:18:01(165):access-list OUTSIDE_IN extended permit deny IP

Я попробовал использовать формулу

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

но это не сработало. Он удалил все, 22:18:01(165):access-listтак как после последнего двоеточия нет пробела.

решение1

Предполагая, что все ваши строки выглядят так же, как в строке примера (в частности, в той части текста, которую вы хотите сохранить, нет двоеточий), вы можете просто использовать для этого функцию «Текст по столбцам», указав :в качестве разделителя.

Выберите столбец с вашими данными и нажмите Text to Columnsна Dataвкладку.

Выберите Delimitedна первом экране, а на втором экране только отметьте галочкой Otherи введите двоеточие:

Нажмите «Завершить», и ваши данные теперь должны быть разделены на 4 столбца на основе каждого двоеточия. Просто удалите первые 3 столбца, и у вас останетсяaccess-list OUTSIDE_IN extended permit deny IP

В качестве альтернативы, предполагая, что количество символов access-listв каждой строке одинаково, вы можете использовать=RIGHT(A2,LEN(A2)-14)

решение2

Простейшее формульное решение, которое подходит для чисел любой длины, применимо для случая фиксированного количества чисел, разделенных двоеточием, в начале данных, например, для трех чисел:

Скриншот рабочего листа

Введите следующую формулу B2и нажмите ctrl-enter/copy-paste/fill-down/auto-fill в оставшуюся часть столбца таблицы:

=RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),3)))

Объяснение:

Необязательный четвертый аргумент SUBSTITUTE()позволяет нам заменить определенное вхождение символа :, в данном случае третьего, на специальный символ, который не встречается больше нигде в тексте. CHAR(1)используется, поскольку он практически гарантированноникогдавстречаются в любой строке.

Это приводит к появлению второго одиночного разделителя, где данные, которые мы хотим сохранить, находятся справа. RIGHT()Теперь ваша формула будет работать правильно.

Обратите внимание, что это решение будет работать даже в том случае, если в тексте, который вы хотите сохранить, есть двоеточия.

Более общая формула решения, которая допускаетпеременнаяКоличество :разделителей цифр в начале данных (а также цифр любой длины) равно:

=RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,":","")))))

Объяснение:

Второй SUBSTITUTE()удаляет все двоеточия. Разница между длиной исходного текста и длиной текста с удаленными двоеточиями — это количество двоеточий.

Для выборочных данных:

  • LEN(A2)-LEN(SUBSTITUTE(A2,":",""))
    → 3.
  • Таким образом =RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,":","")))))
    → =RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,":",CHAR(1),3)))т.е. то же самое, что и первая формула (за исключением того, что для первой формулы это 3третье двоеточие, тогда как в этой этопоследнийдвоеточие)

Очевидно, что эта формула будетнетработает правильно, если в тексте есть двоеточия, которые вы хотите сохранить.

Связанный контент