Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/унифицировать телефонные номера - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » унифицировать телефонные номера (Формулы/Formulas)
унифицировать телефонные номера
rico_crazy Дата: Понедельник, 15.11.2021, 13:31 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Привет друзья.
Есть гугл таблица: https://docs.google.com/spreads....sd=true

Столбец D: Здесь мы записываем номера телефонов. Проблема в том что нас несколько, и каждый записывает номера так как ему удобно, плюс ко всему этому несколько номеров записывается в одну ячейку, то есть номер
+38 (096) 55 623 32 может выглядеть по разному:
096 55-623-32
965562332 и т.д.

Столбец Е: Здесь мы решили проблему с помощью формулы:
Код
=IF(len(D2)>1, Arrayformula(JOIN(char(10),"+38"&Right(Text(Regexreplace(SPLIT(D2,CHAR(10))&"","\D+",""),REPT(0,12)),10))),"")

и привели все номера в прежней ячейке к единому виду: +380965562332.

Так вот, теперь у сути проблемы, нужно сделать что то, что бы формула выше применялась сразу к ячейке D куда вносятся номера.
То есть, аналог "формат ячейки". Проблема в том, что я не могу применить формат ячейки, ибо не известно сколько номеров телефона в одной ячейке, их может быть один, а может быть и пять (МАКСИМУМ).


Сообщение отредактировал Serge_007 - Понедельник, 15.11.2021, 13:35
 
Ответить
СообщениеПривет друзья.
Есть гугл таблица: https://docs.google.com/spreads....sd=true

Столбец D: Здесь мы записываем номера телефонов. Проблема в том что нас несколько, и каждый записывает номера так как ему удобно, плюс ко всему этому несколько номеров записывается в одну ячейку, то есть номер
+38 (096) 55 623 32 может выглядеть по разному:
096 55-623-32
965562332 и т.д.

Столбец Е: Здесь мы решили проблему с помощью формулы:
Код
=IF(len(D2)>1, Arrayformula(JOIN(char(10),"+38"&Right(Text(Regexreplace(SPLIT(D2,CHAR(10))&"","\D+",""),REPT(0,12)),10))),"")

и привели все номера в прежней ячейке к единому виду: +380965562332.

Так вот, теперь у сути проблемы, нужно сделать что то, что бы формула выше применялась сразу к ячейке D куда вносятся номера.
То есть, аналог "формат ячейки". Проблема в том, что я не могу применить формат ячейки, ибо не известно сколько номеров телефона в одной ячейке, их может быть один, а может быть и пять (МАКСИМУМ).
Автор - rico_crazy
Дата добавления - 15.11.2021 в 13:31
doober Дата: Понедельник, 15.11.2021, 14:49 | Сообщение № 2
Группа: Друзья
Ранг: Ветеран
Сообщений: 983
Репутация: 340 ±
Замечаний: 0% ±

Excel 2010
Приветствую.
1.Научить всех правильно вносить номера.
2.Написать функцию в скрипте на событие редактирования ячейки и поставить ее на триггер

 
Ответить
СообщениеПриветствую.
1.Научить всех правильно вносить номера.
2.Написать функцию в скрипте на событие редактирования ячейки и поставить ее на триггер
Автор - doober
Дата добавления - 15.11.2021 в 14:49
прохожий2019 Дата: Понедельник, 15.11.2021, 15:04 | Сообщение № 3
Группа: Проверенные
Ранг: Старожил
Сообщений: 1301
Репутация: 327 ±
Замечаний: 0% ±

365 Beta Channel
Цитата doober, 15.11.2021 в 14:49, в сообщении № 2 ()
Написать функцию
конкретно формульный алгоритм выглядит так:
[vba]
Код
function telephone(txt) {
    return txt.split('\n').map(function(x){
                    return '+38' + x.replace(/\D+/g,'').padStart(13,"0").substring(3,13);
                    }).join('\n')
}
[/vba] но я тот ещё скриптовик, наверняка можно изящнее


Сообщение отредактировал прохожий2019 - Понедельник, 15.11.2021, 15:07
 
Ответить
Сообщение
Цитата doober, 15.11.2021 в 14:49, в сообщении № 2 ()
Написать функцию
конкретно формульный алгоритм выглядит так:
[vba]
Код
function telephone(txt) {
    return txt.split('\n').map(function(x){
                    return '+38' + x.replace(/\D+/g,'').padStart(13,"0").substring(3,13);
                    }).join('\n')
}
[/vba] но я тот ещё скриптовик, наверняка можно изящнее
Автор - прохожий2019
Дата добавления - 15.11.2021 в 15:04
rico_crazy Дата: Понедельник, 15.11.2021, 16:07 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Цитата doober, 15.11.2021 в 14:49, в сообщении № 2 ()
1.Научить всех правильно вносить номера.

Да это было бы проще) Но сотрудники приходят и уходят, да и записывать нужно быстро, поэтому записывают кто как кому удобнее.
Чисто теоретически можно прочесть каждую строку, как бы она не была оформлена.
Но перфекционист во мне любит когда все разложено по полочкам, когда нужное подсвечивается нужным цветом, когда все выглядит красиво, и понятно для новичка который впервые зашёл на таблицу, и сразу без проблем в ней разобрался.

Цитата doober, 15.11.2021 в 14:49, в сообщении № 2 ()
Написать функцию в скрипте на событие редактирования ячейки и поставить ее на триггер

Как это делается? Можно пример? Или мб есть у вас какие то инструкции, книги по этому?
 
Ответить
Сообщение
Цитата doober, 15.11.2021 в 14:49, в сообщении № 2 ()
1.Научить всех правильно вносить номера.

Да это было бы проще) Но сотрудники приходят и уходят, да и записывать нужно быстро, поэтому записывают кто как кому удобнее.
Чисто теоретически можно прочесть каждую строку, как бы она не была оформлена.
Но перфекционист во мне любит когда все разложено по полочкам, когда нужное подсвечивается нужным цветом, когда все выглядит красиво, и понятно для новичка который впервые зашёл на таблицу, и сразу без проблем в ней разобрался.

Цитата doober, 15.11.2021 в 14:49, в сообщении № 2 ()
Написать функцию в скрипте на событие редактирования ячейки и поставить ее на триггер

Как это делается? Можно пример? Или мб есть у вас какие то инструкции, книги по этому?
Автор - rico_crazy
Дата добавления - 15.11.2021 в 16:07
прохожий2019 Дата: Понедельник, 15.11.2021, 17:55 | Сообщение № 5
Группа: Проверенные
Ранг: Старожил
Сообщений: 1301
Репутация: 327 ±
Замечаний: 0% ±

365 Beta Channel
Цитата rico_crazy, 15.11.2021 в 16:07, в сообщении № 4 ()
Можно пример?
пример сообщением выше, только в ваш файл ничего добавить нельзя - расширения заблокированы
 
Ответить
Сообщение
Цитата rico_crazy, 15.11.2021 в 16:07, в сообщении № 4 ()
Можно пример?
пример сообщением выше, только в ваш файл ничего добавить нельзя - расширения заблокированы
Автор - прохожий2019
Дата добавления - 15.11.2021 в 17:55
rico_crazy Дата: Понедельник, 15.11.2021, 21:27 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Цитата прохожий2019, 15.11.2021 в 17:55, в сообщении № 5 ()
пример сообщением выше, только в ваш файл ничего добавить нельзя - расширения заблокированы

Да, видел. Я уже облазил youtube что бы найти решение. Но никак не могу понять, почему расширения заблокированы :(
 
Ответить
Сообщение
Цитата прохожий2019, 15.11.2021 в 17:55, в сообщении № 5 ()
пример сообщением выше, только в ваш файл ничего добавить нельзя - расширения заблокированы

Да, видел. Я уже облазил youtube что бы найти решение. Но никак не могу понять, почему расширения заблокированы :(
Автор - rico_crazy
Дата добавления - 15.11.2021 в 21:27
прохожий2019 Дата: Вторник, 16.11.2021, 07:24 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1301
Репутация: 327 ±
Замечаний: 0% ±

365 Beta Channel
Цитата rico_crazy, 15.11.2021 в 21:27, в сообщении № 6 ()
Я уже облазил youtube что бы найти решение
странное место для поиска... При том, что ларчик просто открывается -
К сообщению приложен файл: 4253788.png (90.7 Kb)


Сообщение отредактировал прохожий2019 - Вторник, 16.11.2021, 07:24
 
Ответить
Сообщение
Цитата rico_crazy, 15.11.2021 в 21:27, в сообщении № 6 ()
Я уже облазил youtube что бы найти решение
странное место для поиска... При том, что ларчик просто открывается -
Автор - прохожий2019
Дата добавления - 16.11.2021 в 07:24
Kashimirush Дата: Вторник, 16.11.2021, 07:26 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
rico_crazy, У вас файл xlsx, а не гугл таблица, конвертируйте файл в гугл таблицу и тогда сможете в нём скрипты применять. Файл - преобразовать в гугл ...

Работа, работа, перейди на Федота...
 
Ответить
Сообщениеrico_crazy, У вас файл xlsx, а не гугл таблица, конвертируйте файл в гугл таблицу и тогда сможете в нём скрипты применять. Файл - преобразовать в гугл ...
Автор - Kashimirush
Дата добавления - 16.11.2021 в 07:26
Kashimirush Дата: Вторник, 16.11.2021, 07:43 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
Цитата прохожий2019, 15.11.2021 в 15:04, в сообщении № 3 ()
конкретно формульный алгоритм выглядит так:


Осмелюсь взяться и модернизировать ваш скрипт, с триггером при редактировании таблицы:

[vba]
Код

function onEdit(e) {

  let column = e.range.getColumn();
  let sheetName = e.range.getSheet().getName();
  let row = e.range.getRow();

  //Проверяем где были изменения, нам важно номер колонки D - 4, Строки ниже заголовка (row > 1) и имя листа:

  if (column == 4
    && sheetName == 'База номеров'
    && row > 1) {

    let txt = e.value;

    let newTxt = txt.split('\n').map(x => {
      return '+38' + x.replace(/\D+/g, '').padStart(13, "0").substring(3, 13);
    }).join('\n')

    e.range.setValue(newTxt);

  }

}
[/vba]

Такого рода скрипт, будет менять номера телефонов сразу после ввода данных в колонке D.
НО, есть огромное но - т.к. регулярное выражение отсекает всё лишнее, то и все примечания, указанные вместе с номерами телефонов, бесследно исчезнут.

Работа, работа, перейди на Федота...

Сообщение отредактировал Kashimirush - Вторник, 16.11.2021, 12:13
 
Ответить
Сообщение
Цитата прохожий2019, 15.11.2021 в 15:04, в сообщении № 3 ()
конкретно формульный алгоритм выглядит так:


Осмелюсь взяться и модернизировать ваш скрипт, с триггером при редактировании таблицы:

[vba]
Код

function onEdit(e) {

  let column = e.range.getColumn();
  let sheetName = e.range.getSheet().getName();
  let row = e.range.getRow();

  //Проверяем где были изменения, нам важно номер колонки D - 4, Строки ниже заголовка (row > 1) и имя листа:

  if (column == 4
    && sheetName == 'База номеров'
    && row > 1) {

    let txt = e.value;

    let newTxt = txt.split('\n').map(x => {
      return '+38' + x.replace(/\D+/g, '').padStart(13, "0").substring(3, 13);
    }).join('\n')

    e.range.setValue(newTxt);

  }

}
[/vba]

Такого рода скрипт, будет менять номера телефонов сразу после ввода данных в колонке D.
НО, есть огромное но - т.к. регулярное выражение отсекает всё лишнее, то и все примечания, указанные вместе с номерами телефонов, бесследно исчезнут.
Автор - Kashimirush
Дата добавления - 16.11.2021 в 07:43
Kashimirush Дата: Вторник, 16.11.2021, 09:15 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
И ещё rico_crazy, название вашей темы не отражает сути вопроса, обзовите согласно правил форума..

Работа, работа, перейди на Федота...
 
Ответить
СообщениеИ ещё rico_crazy, название вашей темы не отражает сути вопроса, обзовите согласно правил форума..
Автор - Kashimirush
Дата добавления - 16.11.2021 в 09:15
rico_crazy Дата: Вторник, 16.11.2021, 12:03 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Перевёл таблицу в гугл таблицу, зашёл в расширения, и "Скрипт приложений Google Apps". И это либо я тупой, либо что то не так с кодом :D

Но мне почему то кажется что дело в моей тупости


Сообщение отредактировал rico_crazy - Вторник, 16.11.2021, 12:04
 
Ответить
СообщениеПеревёл таблицу в гугл таблицу, зашёл в расширения, и "Скрипт приложений Google Apps". И это либо я тупой, либо что то не так с кодом :D

Но мне почему то кажется что дело в моей тупости
Автор - rico_crazy
Дата добавления - 16.11.2021 в 12:03
Kashimirush Дата: Вторник, 16.11.2021, 12:08 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
rico_crazy, Данный скрипт записан под зарезервированным именем onEdit - это простой триггер гугл таблиц, он работает только при внесении изменений в таблицу.
Вручную запускать его не нужно, сохраните проект и вернитесь в таблицу, попробуйте ввести новый номер телефона "неправильно" и посмотрите , что получится

Работа, работа, перейди на Федота...

Сообщение отредактировал Kashimirush - Вторник, 16.11.2021, 12:10
 
Ответить
Сообщениеrico_crazy, Данный скрипт записан под зарезервированным именем onEdit - это простой триггер гугл таблиц, он работает только при внесении изменений в таблицу.
Вручную запускать его не нужно, сохраните проект и вернитесь в таблицу, попробуйте ввести новый номер телефона "неправильно" и посмотрите , что получится
Автор - Kashimirush
Дата добавления - 16.11.2021 в 12:08
rico_crazy Дата: Вторник, 16.11.2021, 12:19 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Цитата Kashimirush, 16.11.2021 в 12:08, в сообщении № 12 ()
Данный скрипт записан под зарезервированным именем onEdit - это простой триггер гугл таблиц, он работает только при внесении изменений в таблицу.
Вручную запускать его не нужно, сохраните проект и вернитесь в таблицу, попробуйте ввести новый номер телефона "неправильно" и посмотрите , что получится

Ооо, спс, заработало. Вопрос еще один, тригер изменяет только те номера которые только записаны. Старые номера он не меняет, только нужно редактировать хотя бы одну цифру, что бы он заработал.
Я уже пытался весь столбец номеров скопировать и вставить, но ничего не происходит.
Это нужно каждый номер прокликать?
 
Ответить
Сообщение
Цитата Kashimirush, 16.11.2021 в 12:08, в сообщении № 12 ()
Данный скрипт записан под зарезервированным именем onEdit - это простой триггер гугл таблиц, он работает только при внесении изменений в таблицу.
Вручную запускать его не нужно, сохраните проект и вернитесь в таблицу, попробуйте ввести новый номер телефона "неправильно" и посмотрите , что получится

Ооо, спс, заработало. Вопрос еще один, тригер изменяет только те номера которые только записаны. Старые номера он не меняет, только нужно редактировать хотя бы одну цифру, что бы он заработал.
Я уже пытался весь столбец номеров скопировать и вставить, но ничего не происходит.
Это нужно каждый номер прокликать?
Автор - rico_crazy
Дата добавления - 16.11.2021 в 12:19
Kashimirush Дата: Вторник, 16.11.2021, 12:35 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
rico_crazy,
Цитата rico_crazy, 16.11.2021 в 12:19, в сообщении № 13 ()
Это нужно каждый номер прокликать?


Да.
У вас есть формула с правильными номерами, скопируйте вставьте только значения.

Работа, работа, перейди на Федота...

Сообщение отредактировал Kashimirush - Вторник, 16.11.2021, 12:36
 
Ответить
Сообщениеrico_crazy,
Цитата rico_crazy, 16.11.2021 в 12:19, в сообщении № 13 ()
Это нужно каждый номер прокликать?


Да.
У вас есть формула с правильными номерами, скопируйте вставьте только значения.
Автор - Kashimirush
Дата добавления - 16.11.2021 в 12:35
Kashimirush Дата: Вторник, 16.11.2021, 12:37 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 41 ±
Замечаний: 0% ±

Excel 2010
Цитата Kashimirush, 16.11.2021 в 07:43, в сообщении № 9 ()
НО, есть огромное но - т.к. регулярное выражение отсекает всё лишнее, то и все примечания, указанные вместе с номерами телефонов, бесследно исчезнут.


И не забудьте про ограничения с введением скрипта!!!

Работа, работа, перейди на Федота...
 
Ответить
Сообщение
Цитата Kashimirush, 16.11.2021 в 07:43, в сообщении № 9 ()
НО, есть огромное но - т.к. регулярное выражение отсекает всё лишнее, то и все примечания, указанные вместе с номерами телефонов, бесследно исчезнут.


И не забудьте про ограничения с введением скрипта!!!
Автор - Kashimirush
Дата добавления - 16.11.2021 в 12:37
rico_crazy Дата: Вторник, 16.11.2021, 12:43 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 0 ±
Замечаний: 20% ±

Цитата Kashimirush, 16.11.2021 в 12:37, в сообщении № 15 ()
И не забудьте про ограничения с введением скрипта!!!

Да, вкурсе, мне как раз и нужно что бы там кроме номеров ничего не было, ведь для этого есть отдельная колонка "Коментарии")
Большое спасибо за помощь. Тему можно закрывать(Я не умею). Проблему решили)
 
Ответить
Сообщение
Цитата Kashimirush, 16.11.2021 в 12:37, в сообщении № 15 ()
И не забудьте про ограничения с введением скрипта!!!

Да, вкурсе, мне как раз и нужно что бы там кроме номеров ничего не было, ведь для этого есть отдельная колонка "Коментарии")
Большое спасибо за помощь. Тему можно закрывать(Я не умею). Проблему решили)
Автор - rico_crazy
Дата добавления - 16.11.2021 в 12:43
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » унифицировать телефонные номера (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!