Всем привет. Возникла проблема, при созданию таблицы. Была создана таблица в Excel, после чего была загружена на гугл диск, дабы дать доступ другим участникам к её редактированию.
В таблице есть два столбца: 1) Контакт 2) Номер мобильного телефона
Прикол в том что в таблице более 1000 контактов. Из-за того что данные вносили разные люди, каждый вносил номер телефона по своему. Например: Есть контакт "Цукровая фабрика". На этот контакт каждый кто имеет доступ к таблице внёс номер телефона, НО каждый вносил по своему: 1) 0983957399 2) +38 (067) 22-95-884 3) (0400) 675439
Все эти номера находятся в одной ячейке. Нужно что бы формула отсортировала эти номера по стандарту, то есть нужно что бы номера выглядели: 1) +38 (098) 39 57 399 2) +38 (067) 22 95 884 3) +38 (040) 06 75 439
2) Удаляем из ячейки все символы "(", ")", "-", "+", "’", ".", ",", """, ";", также удаляем все пробелы и переносы строк, и получаем: Ячейка: 09839573993806722958840400675439
3) Каждый номер телефона состоит минимум из 9 цифр, две из которых обозначают оператора, и есть стандартными для многих номеров. К примеру 0983957399, где 98 - цифры оператора. Все остальные после 98 набор цифр из номера телефона, может быть разным. Операторов есть 48 шт. Идея следующая: Мы берем ячейку и ищем в ней первый оператор(Которых 48шт), когда находим оператор отсчитываем от него 7 цифр, вместе с оператором 9 цифр, и выводим этот номер отдельно в другую ячейку, ну или туда где можно хранить этот номер к след. этапу. И повторяем эту процедуру 5 раз(В ячейке может быть не более 5-ти номеров). Если в ячейке осталось менее 7 цифр, это значит что недостаточно цифр для создания тел. номера, поэтому эти цифры удаляем.
4) После того как мы нашли номера, вывели их в отдельные ячейки, или хз куда, мы должны эти номера записать назад в первую ячейку, но уже в формате 1) +38 (098) 39 57 399 2) +38 (067) 22 95 884 3) +38 (040) 06 75 439
повторяю этот процес, но проблема в том что в формуле я вписываю вручную номер оператора, а их 48, что сделает формулу очень громадной, нужно сделать как то что бы формула проверяла ячейку на наличие определенных операторов
Получили +380673457677 +380688116836 +380983668811
КАК СДЕЛАТЬ ЭТО ВСЕ В ОДНУ ФОРМУЛУ. И ЕСТЬ ЛИ ВОЗМОЖНОСТЬ ИНТЕГРИРОВАТЬ КАК ТО ФОРМУЛУ ЧТО БЫ ЗАМЕНЯЛСЯ РЕЗУЛЬТАТ ПРЯМО В ПЕРВОЙ ЯЧЕЙКЕ КУДА БУДУТ ВНОСИТЬ НОМЕРА ТЕЛЕФОНОВ
Всем привет. Возникла проблема, при созданию таблицы. Была создана таблица в Excel, после чего была загружена на гугл диск, дабы дать доступ другим участникам к её редактированию.
В таблице есть два столбца: 1) Контакт 2) Номер мобильного телефона
Прикол в том что в таблице более 1000 контактов. Из-за того что данные вносили разные люди, каждый вносил номер телефона по своему. Например: Есть контакт "Цукровая фабрика". На этот контакт каждый кто имеет доступ к таблице внёс номер телефона, НО каждый вносил по своему: 1) 0983957399 2) +38 (067) 22-95-884 3) (0400) 675439
Все эти номера находятся в одной ячейке. Нужно что бы формула отсортировала эти номера по стандарту, то есть нужно что бы номера выглядели: 1) +38 (098) 39 57 399 2) +38 (067) 22 95 884 3) +38 (040) 06 75 439
2) Удаляем из ячейки все символы "(", ")", "-", "+", "’", ".", ",", """, ";", также удаляем все пробелы и переносы строк, и получаем: Ячейка: 09839573993806722958840400675439
3) Каждый номер телефона состоит минимум из 9 цифр, две из которых обозначают оператора, и есть стандартными для многих номеров. К примеру 0983957399, где 98 - цифры оператора. Все остальные после 98 набор цифр из номера телефона, может быть разным. Операторов есть 48 шт. Идея следующая: Мы берем ячейку и ищем в ней первый оператор(Которых 48шт), когда находим оператор отсчитываем от него 7 цифр, вместе с оператором 9 цифр, и выводим этот номер отдельно в другую ячейку, ну или туда где можно хранить этот номер к след. этапу. И повторяем эту процедуру 5 раз(В ячейке может быть не более 5-ти номеров). Если в ячейке осталось менее 7 цифр, это значит что недостаточно цифр для создания тел. номера, поэтому эти цифры удаляем.
4) После того как мы нашли номера, вывели их в отдельные ячейки, или хз куда, мы должны эти номера записать назад в первую ячейку, но уже в формате 1) +38 (098) 39 57 399 2) +38 (067) 22 95 884 3) +38 (040) 06 75 439
повторяю этот процес, но проблема в том что в формуле я вписываю вручную номер оператора, а их 48, что сделает формулу очень громадной, нужно сделать как то что бы формула проверяла ячейку на наличие определенных операторов
Получили +380673457677 +380688116836 +380983668811
КАК СДЕЛАТЬ ЭТО ВСЕ В ОДНУ ФОРМУЛУ. И ЕСТЬ ЛИ ВОЗМОЖНОСТЬ ИНТЕГРИРОВАТЬ КАК ТО ФОРМУЛУ ЧТО БЫ ЗАМЕНЯЛСЯ РЕЗУЛЬТАТ ПРЯМО В ПЕРВОЙ ЯЧЕЙКЕ КУДА БУДУТ ВНОСИТЬ НОМЕРА ТЕЛЕФОНОВrico_crazy
Для наглядности прилагаю скрин таблицы, проблема в том что выделено красным цветом. Во первых там только 3 оператора: 67, 68, 98. На каждый формула:
Код
=ПСТР(I4;НАЙТИ("67";I4);9)
А нужно что бы операторов было 48шт, но не обязательно что все операторы будут в этом ряде цифр. Нужно как то сделать так что бы формула проверяла есть ли там один из операторов из 48шт по очереди, если есть тогда выводить этот номер отдельно, и продолжать поиск, до тех пор пока в строке не останется меньше 7 цифр. После этого найденные номера нужно сбить в одну ячейку строго по определенному формату
Нужно что бы из этого:
получилось это:
Для наглядности прилагаю скрин таблицы, проблема в том что выделено красным цветом. Во первых там только 3 оператора: 67, 68, 98. На каждый формула:
Код
=ПСТР(I4;НАЙТИ("67";I4);9)
А нужно что бы операторов было 48шт, но не обязательно что все операторы будут в этом ряде цифр. Нужно как то сделать так что бы формула проверяла есть ли там один из операторов из 48шт по очереди, если есть тогда выводить этот номер отдельно, и продолжать поиск, до тех пор пока в строке не останется меньше 7 цифр. После этого найденные номера нужно сбить в одну ячейку строго по определенному формату
rico_crazy, Вы это серьезно? а если у Вас будет первый номер заканчиваться не на 77, а на 68, то какой у Вас тогда будет второй найденный? ps таким же будет неудачно цифры подобрал Но есть вероятность, что Ваша формула найдет код оператора в номере телефона, и тогда она неверно вырежет следующие 7 цифр.
rico_crazy, Вы это серьезно? а если у Вас будет первый номер заканчиваться не на 77, а на 68, то какой у Вас тогда будет второй найденный? ps таким же будет неудачно цифры подобрал Но есть вероятность, что Ваша формула найдет код оператора в номере телефона, и тогда она неверно вырежет следующие 7 цифр.bigorq
Сообщение отредактировал bigorq - Среда, 10.11.2021, 16:53
Вы это серьезно? а если у Вас будет первый номер заканчиваться не на 77, а на 68, то какой у Вас тогда будет второй найденный?
Ну смотри в моей голове схема простая: 1) Есть куча телефонных номеров в ОДНОЙ ячейке, разделены пробелом, или же переносом строки, или же другими символами. Номера записаны по разному, ибо каждый записывает так как ему удобно. 2) Главным элементом телефонного номера есть конечно же цифры. Поэтому с помощью формулы убираем все лишние знаки, пробелы, переносы. Итог, у нас получается длинная строка цифр в таком же порядке, в котором записали участники таблицы. 3) Нам нужно найти из этой строки номера телефона, поэтому начинаем поиск в строке операторов(Которых есть 48шт), поиск начинаем с самого начала строки. Когда первый номер найден, его нужно удалить из строки, и записать куда то отдельно, на потом. А в строке где мы уже удалили прежний номер, повторяем поиск, и делаем аналогично до тех пор, пока в строке не окажется меньше 7 цифр. Я реализовал такое с помощью формул, но проблема в том что я сделал только 3 формулы где вручную внесено 3 оператора. А их 48шт, и каждый записывать это долго, и много места занимает. Нужно как то сделать что бы одна формула искала все 48 операторов. 4) Ну и в самом конце нужно что бы формула все найденые номера собирала обратно в одну ячейку в строго заданом формате. В идеале было бы, если бы все это просчитывалось в одной и той же ячейке
Вы это серьезно? а если у Вас будет первый номер заканчиваться не на 77, а на 68, то какой у Вас тогда будет второй найденный?
Ну смотри в моей голове схема простая: 1) Есть куча телефонных номеров в ОДНОЙ ячейке, разделены пробелом, или же переносом строки, или же другими символами. Номера записаны по разному, ибо каждый записывает так как ему удобно. 2) Главным элементом телефонного номера есть конечно же цифры. Поэтому с помощью формулы убираем все лишние знаки, пробелы, переносы. Итог, у нас получается длинная строка цифр в таком же порядке, в котором записали участники таблицы. 3) Нам нужно найти из этой строки номера телефона, поэтому начинаем поиск в строке операторов(Которых есть 48шт), поиск начинаем с самого начала строки. Когда первый номер найден, его нужно удалить из строки, и записать куда то отдельно, на потом. А в строке где мы уже удалили прежний номер, повторяем поиск, и делаем аналогично до тех пор, пока в строке не окажется меньше 7 цифр. Я реализовал такое с помощью формул, но проблема в том что я сделал только 3 формулы где вручную внесено 3 оператора. А их 48шт, и каждый записывать это долго, и много места занимает. Нужно как то сделать что бы одна формула искала все 48 операторов. 4) Ну и в самом конце нужно что бы формула все найденые номера собирала обратно в одну ячейку в строго заданом формате. В идеале было бы, если бы все это просчитывалось в одной и той же ячейкеrico_crazy
про удалить, найденный номер в первом посте не нашел, в таком варианте идея наверное рабочая. Но нужен файл пример со списком операторов и вашей формулой. Сделать формулой, что бы заменяла номера в исходной ячейке не получится. Нужно было настраивать проверку данных изначально
про удалить, найденный номер в первом посте не нашел, в таком варианте идея наверное рабочая. Но нужен файл пример со списком операторов и вашей формулой. Сделать формулой, что бы заменяла номера в исходной ячейке не получится. Нужно было настраивать проверку данных изначальноbigorq
Сообщение отредактировал bigorq - Среда, 10.11.2021, 17:19
на гугл диск загрузить можно что угодно, вопрос в том, что вы собрались с этим файлом делать - через Google Tables работать, разумеется, не будет.прохожий2019
на гугл диск загрузить можно что угодно, вопрос в том, что вы собрались с этим файлом делать - через Google Tables работать, разумеется, не будет.
Вот ссылка на таблицу. Нужно что бы номера были строго в одном формате. Что бы пропадали лишние символы, так же текст тоже выкинуть https://docs.google.com/spreads....sd=true
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
Но есть вероятность, что Ваша формула найдет код оператора в номере телефона, и тогда она неверно вырежет следующие 7 цифр.
