Формули Excel для загального очищення даних

Формули очищення даних Excel

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

Видаліть нечислові символи

Системи часто вимагають вставлення телефонних номерів у певну 11-значну формулу із кодом країни та без знаків пунктуації. Однак люди часто вводять ці дані з тире та крапками. Ось чудова формула для видалення всіх нецифрових символів в Excel. Формула розглядає дані в комірці А2:

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

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

Оцініть кілька полів за допомогою АБО

Ми часто видаляємо неповні записи з імпорту. Користувачі не усвідомлюють, що не завжди потрібно писати складні ієрархічні формули і що замість цього ви можете написати оператор АБО. У наведеному нижче прикладі я хочу перевірити 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)))

Перевірте дійсну електронну адресу

Досить проста формула, яка шукає як @, так і. на електронну адресу:

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

Звичайно, вони не призначені для того, щоб бути всеохоплюючими ... лише деякі швидкі формули, які допоможуть вам отримати швидкий старт! Які ще формули ви використовуєте? Додайте їх у коментарі, і я буду зараховувати вас під час оновлення цієї статті.

Що ви думаєте?

Цей сайт використовує Akismet для зменшення спаму. Дізнайтеся, як обробляються ваші дані коментарів.