Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Как разделить данные из одной ячейки на столбцы при отсутствии разделителя?, Причем набор данных не всегда одинаков
 
#1
15.10.2021 16:30:44
Всем привет!
В одном столбце Excel есть все или некоторые из нижеуказанных данных (в произвольном порядке):
1) № телефона (7 или 10 цифр со скобками и пробелами) - если есть в ячейке, всегда идет первым,
2) ФИО (или только имя, или ИО, или ИФ),
3) email,
4) произвольный комментарий (текст, который не подпадает под пп. 1 - 3).
Как все эти данные разнести на 4 столбца (особенно если учесть, что 1) и 2) может быть несколько)?

Пример - в приложенном файле. В исходном - таких строк >1000, так что вручную не разнести. Предполагаю, может быть полезным считать, что
  • признаком (1) являются 4 и более цифры подряд,
  • признаком (2) - слова с заглавных букв непосредственно после № телефона,
  • признаком (3) - наличие @.
Изменено: Филат - 15.10.2021 19:25:51
 
 
 
#2
15.10.2021 22:01:16
может вам это поможет https://www.planetaexcel.ru/techniques/7/4844/
 
 
 
#3
16.10.2021 00:22:48
Без автозаполнения CTRL+E , макросов, и CTRL+H (советую изучить), на формулах, наверно так
 
 
 
#4
16.10.2021 12:51:18
Ham13, великолепно! Большое спасибо!  :)
Только разъясните, пожалуйста, формулы в столбцах D и F. С остальными вроде разобрался.

snatg, тоже полезно. Спасибо.
 
 
 
#5
16.10.2021 15:26:40
по D
Символ 192 -  Буква А
Символ 193 - Буква Б
(пропишите в любой ячейке =СИМВОЛ(192)     )

т.е. СТРОКА(192:255) просто возвращает числа от 192 до 255 на них ссылается функция Символ
МИН+ПОИСК ищет в ячейке порядковый номер первой встречающей буквы (кириллицы)
Например:   (351) 561 3515, Артём Петр. С 15/03    первая буква "А" находиться на 17 месте в ячейке =МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА($A$192:$A$255));C2);""))              вернет 17    , вычитаем 3 = 14 , т.е. телефон занимает первые 14 символов
ЛЕВСИМВ - берет из ячейки первые 14 символов

У меня в файле ошибка вместо СИМВОЛ(СТРОКА(192:255) нужно  СИМВОЛ(СТРОКА($A$192:$A$255), чтобы при протягивании  формулы вниз, набор символов не менялся

E - функция на VBA  
.Pattern = "[0-9,:]"
Исключает из ячейки  все цифры и двоеточие.
 
 
 
#6
16.10.2021 15:43:28
Цитата
Ham13 написал:
по D  Символ 192 -  Буква АСимвол 193 - Буква Б (пропишите в любой ячейке =СИМВОЛ(192)     )
Понятно.
Цитата
Ham13 написал:
У меня в файле ошибка вместо СИМВОЛ(СТРОКА(192:255) нужно  СИМВОЛ(СТРОКА($A$192:$A$255), чтобы при протягивании  формулы вниз, набор символов не менялся
О, тогда столбцы F, G, H возвращают ошибку.  :(

Кстати, с CTRL+H (Найти и заменить) знаком, регулярно пользуюсь. Только не знаю, чем она может быть полезна в данном случае.
Изменено: Филат - 16.10.2021 16:11:03
 
 
 
#7
16.10.2021 16:31:59
приложил, столбцы работают...
 
 
 
#8
16.10.2021 16:51:14
Еще раз спасибо.  :)  
 
 
 
#9
18.10.2021 12:35:57
Цитата
Ham13 написал:
приложил, столбцы работают...
Проверил работу на реальном файле.
Сначала хотел скопировать в него формулы, но сразу появилось #ИМЯ. Надо какие-то надстройки в своем Экселе включать, чтобы формулы заработали?
Потом скопировал столбец из реального файла в Пример - email и № телефона (без добавочного) извлекает отлично, а вот с именами возникают проблемы: то всё правильно извлечёт, то имя или фамилию перенесёт в последний столбец, то и вовсе всё ФИО оказывается в последнем столбце. Причину понять не могу.
Также обнаружился еще один вариант записи № телефона: (168) 651 5456, д. 3518 или (168) 651 5456, доб. 3518. Как добавочный № оставить в столбце с телефоном?
Спасибо.

Файл обновил.
 
 
 
#10
20.10.2021 12:29:12
Кто поможет исправить ошибки с извлечением ФИО?
 
 
 
Страницы: 1
Наверх