Я хотел бы удалить числа, включая двоеточие, из каждой строки.
Пример данных:
22:18:01(165):access-list OUTSIDE_IN extended permit deny IP
Я пытался использовать формулу
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
но это не сработало. Он удалил все из 22:18:01(165):access-list так как после последнего двоеточия нет места.
Простейшее формульное решение, которое обслуживает числа любой длины, применяется в случае фиксированного числа чисел, разделенных двоеточиями, в начале данных, например, для трех чисел:
Введите следующую формулу в 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 - третье двоеточие, тогда как в этом - последнее двоеточие)Очевидно, что эта формула не будет работать правильно, если в тексте есть двоеточия, которые вы хотите сохранить.
Предполагая, что все ваши строки выглядят как строки примера (в частности, в той части текста, которую вы хотите сохранить, нет двоеточий), вы можете просто использовать для этого текст в столбцы, указав : в качестве разделителя.
Выберите столбец с вашими данными и нажмите Text to Columns на вкладке Data .
На первом экране выберите «С Delimited , а на втором - только « Other и введите двоеточие :
Нажмите финиш, и ваши данные теперь должны быть разбиты на 4 столбца на основе каждого двоеточия. Просто удалите первые 3 столбца, и у вас access-list OUTSIDE_IN extended permit deny IP
В качестве альтернативы, предполагая, что число символов перед access-list одинаково в каждой строке, вы можете использовать =RIGHT(A2,LEN(A2)-14)