В течение многих лет я использовал эту публикацию как источник практических рекомендаций и записывал ее для дальнейшего использования! Клиент передал нам файл данных клиента, и это стало настоящей катастрофой. Почти каждое поле было отформатировано неправильно, поэтому мы не смогли импортировать данные. Хотя в Excel есть несколько отличных надстроек для очистки с помощью Visual Basic, мы используем Office для Mac, который не поддерживает макросы. Вместо этого мы обращаемся к простым формулам. Я решил поделиться с вами некоторыми из них.

Удалить нечисловые символы

Системы часто требуют, чтобы номера телефонов вводились по специальной 11-значной формуле с кодом страны и без знаков препинания. Однако люди часто вводят эти данные через тире и точки. Вот отличная формула удаление всех нецифровых символов в Экселе. Формула просматривает данные в ячейке A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Вы можете скопировать полученный столбец и использовать Правка > Вставить значения чтобы перезаписать данные правильно отформатированным результатом.

Оценка нескольких полей с помощью OR

Мы часто удаляем неполные записи из импорта. Чего пользователи не осознают, так это того, что вам не всегда нужно писать сложные иерархические формулы и что вместо этого вы можете написать оператор OR. В приведенном ниже примере я хочу проверить, отсутствуют ли данные A2, B2, C2, D2 или E2. Если данных нет, я верну 0; в противном случае 1. Это позволит мне отсортировать данные и удалить неполные записи.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Обрезать и соединить поля

Если ваши данные содержат поля «Имя» и «Фамилия», а импортированный файл содержит поле «Полное имя», вы можете аккуратно объединить поля с помощью встроенной функции объединения Excel, но обязательно используйте TRIM для удаления пробелов до или после текста. Обертываем все поле TRIM, если в одном из полей нет данных:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Подтвердите действительный адрес электронной почты

Довольно простая формула, которая ищет как @, так и . в адресе электронной почты (не для стандарта RFC):

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Удалить имя и фамилию

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

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

И фамилия:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Ограничьте количество символов и добавьте…

Вам когда-нибудь хотелось почистить метаописания? Если вы хотите извлечь содержимое в Excel, а затем обрезать его для использования в поле «Метаописание» (150–160 символов), вы можете сделать это, используя эту формулу. Он аккуратно отделяет описание пробелом, а затем добавляет …:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Конечно, они не претендуют на полноту… просто несколько быстрых формул, которые помогут вам быстро начать работу! Какие еще формулы вы используете? Добавьте их в комментарии, и я воздам вам должное, обновив эту статью.