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

Вход

Регистрация

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

 

= Мир MS Excel/Вытащить из базы данных только номера телефонов - Мир MS Excel

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

Excel 2013
Суть такова: имеется база данных с именами, фамилиями и номерами телефонов, данные о каждом человеке написаны в отдельной строке, но И номер телефона И ФИО записаны в одну строку, а нужно вытащить из более чем 1000 строк ТОЛЬКО номера телефонов и сохранить на отдельном листе. Помогите с формулой пожалуйста. Данные нах-ся в диапазоне (D86:D963).
К сообщению приложен файл: Example.ods (15.0 Kb)


Сообщение отредактировал cartman0098 - Среда, 13.12.2017, 13:26
 
Ответить
СообщениеСуть такова: имеется база данных с именами, фамилиями и номерами телефонов, данные о каждом человеке написаны в отдельной строке, но И номер телефона И ФИО записаны в одну строку, а нужно вытащить из более чем 1000 строк ТОЛЬКО номера телефонов и сохранить на отдельном листе. Помогите с формулой пожалуйста. Данные нах-ся в диапазоне (D86:D963).
Автор - cartman0098
Дата добавления - 13.12.2017 в 13:17
buchlotnik Дата: Среда, 13.12.2017, 13:21 | Сообщение № 2
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
И вам здрасьте, файл не приложился - ограничение по объёму 100 кб - не нужно прикладывать всю базу , достаточно пары десятков строк
 
Ответить
СообщениеИ вам здрасьте, файл не приложился - ограничение по объёму 100 кб - не нужно прикладывать всю базу , достаточно пары десятков строк
Автор - buchlotnik
Дата добавления - 13.12.2017 в 13:21
cartman0098 Дата: Среда, 13.12.2017, 13:27 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Цитата buchlotnik, 13.12.2017 в 13:21, в сообщении № 2 ()
И вам здрасьте, файл не приложился - ограничение по объёму 100 кб - не нужно прикладывать всю базу , достаточно пары десятков строк

Приложил пример
 
Ответить
Сообщение
Цитата buchlotnik, 13.12.2017 в 13:21, в сообщении № 2 ()
И вам здрасьте, файл не приложился - ограничение по объёму 100 кб - не нужно прикладывать всю базу , достаточно пары десятков строк

Приложил пример
Автор - cartman0098
Дата добавления - 13.12.2017 в 13:27
cartman0098 Дата: Среда, 13.12.2017, 13:31 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Цитата buchlotnik, 13.12.2017 в 13:21, в сообщении № 2 ()
И вам здрасьте, файл не приложился - ограничение по объёму 100 кб - не нужно прикладывать всю базу , достаточно пары десятков строк

Как я понял, это делается через ПСТР+ПОИСК
 
Ответить
Сообщение
Цитата buchlotnik, 13.12.2017 в 13:21, в сообщении № 2 ()
И вам здрасьте, файл не приложился - ограничение по объёму 100 кб - не нужно прикладывать всю базу , достаточно пары десятков строк

Как я понял, это делается через ПСТР+ПОИСК
Автор - cartman0098
Дата добавления - 13.12.2017 в 13:31
buchlotnik Дата: Среда, 13.12.2017, 13:34 | Сообщение № 5
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
по вашему примеру:
Код
=ЕСЛИОШИБКА(--ПРАВСИМВ(A1;11);"")
К сообщению приложен файл: Example.xlsx (10.2 Kb)


Сообщение отредактировал buchlotnik - Среда, 13.12.2017, 13:35
 
Ответить
Сообщениепо вашему примеру:
Код
=ЕСЛИОШИБКА(--ПРАВСИМВ(A1;11);"")
Автор - buchlotnik
Дата добавления - 13.12.2017 в 13:34
sboy Дата: Среда, 13.12.2017, 13:40 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Тренирую "регулярки"
[vba]
Код
Function tlf(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d{11}"
        tlf = .Execute(txt)(0)
    End With
End Function
[/vba]
К сообщению приложен файл: tlf.xls (31.0 Kb)

Яндекс: 410016850021169
 
Ответить
СообщениеТренирую "регулярки"
[vba]
Код
Function tlf(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d{11}"
        tlf = .Execute(txt)(0)
    End With
End Function
[/vba]
Автор - sboy
Дата добавления - 13.12.2017 в 13:40
cartman0098 Дата: Среда, 13.12.2017, 13:43 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Цитата buchlotnik, 13.12.2017 в 13:34, в сообщении № 5 ()
по вашему примеру:

=ЕСЛИОШИБКА(--ПРАВСИМВ(A1;11);"")

Работает! Но есть нюанс, в некоторых строчках E-mail идет после номера телефона, а не до, как в первом примере, и в этом случае номер не вытаскивается.
К сообщению приложен файл: Example2.ods (13.8 Kb)
 
Ответить
Сообщение
Цитата buchlotnik, 13.12.2017 в 13:34, в сообщении № 5 ()
по вашему примеру:

=ЕСЛИОШИБКА(--ПРАВСИМВ(A1;11);"")

Работает! Но есть нюанс, в некоторых строчках E-mail идет после номера телефона, а не до, как в первом примере, и в этом случае номер не вытаскивается.
Автор - cartman0098
Дата добавления - 13.12.2017 в 13:43
SLAVICK Дата: Среда, 13.12.2017, 13:55 | Сообщение № 8
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
Можно так попробовать:
Формула массива - вводить CTRL+Shift+Enter
Код
=МАКС(ЕСЛИОШИБКА(ПСТР(A3;СТРОКА($J$1:$J$999);11)*ЕЧИСЛО(--ПСТР(A3;СТРОКА($J$1:$J$999);11));0))

или с проверкой на правильность:
Код
=ЕСЛИ(ДЛСТР(МАКС(ЕСЛИОШИБКА(ПСТР(A2;СТРОКА($J$1:$J$999);11)*ЕЧИСЛО(--ПСТР(A2;СТРОКА($J$1:$J$999);11));0)))<11;0;МАКС(ЕСЛИОШИБКА(ПСТР(A2;СТРОКА($J$1:$J$999);11)*ЕЧИСЛО(--ПСТР(A2;СТРОКА($J$1:$J$999);11));0)))

Иногда все проще чем кажется с первого взгляда.
 
Ответить
СообщениеМожно так попробовать:
Формула массива - вводить CTRL+Shift+Enter
Код
=МАКС(ЕСЛИОШИБКА(ПСТР(A3;СТРОКА($J$1:$J$999);11)*ЕЧИСЛО(--ПСТР(A3;СТРОКА($J$1:$J$999);11));0))

или с проверкой на правильность:
Код
=ЕСЛИ(ДЛСТР(МАКС(ЕСЛИОШИБКА(ПСТР(A2;СТРОКА($J$1:$J$999);11)*ЕЧИСЛО(--ПСТР(A2;СТРОКА($J$1:$J$999);11));0)))<11;0;МАКС(ЕСЛИОШИБКА(ПСТР(A2;СТРОКА($J$1:$J$999);11)*ЕЧИСЛО(--ПСТР(A2;СТРОКА($J$1:$J$999);11));0)))
Автор - SLAVICK
Дата добавления - 13.12.2017 в 13:55
cartman0098 Дата: Среда, 13.12.2017, 14:01 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Цитата SLAVICK, 13.12.2017 в 13:55, в сообщении № 8 ()
Можно так попробовать:
Формула массива - вводить CTRL+Shift+Enter

=МАКС(ЕСЛИОШИБКА(ПСТР(A3;СТРОКА($J$1:$J$999);11)*ЕЧИСЛО(0+ПСТР(A3;СТРОКА($J$1:$J$999);11));0))

По этой формуле он вычленяет номер, все работает, но если растягивать функцию, т.е применять к след строчкам ниже, то возникает ошибка #ИМЯ?
 
Ответить
Сообщение
Цитата SLAVICK, 13.12.2017 в 13:55, в сообщении № 8 ()
Можно так попробовать:
Формула массива - вводить CTRL+Shift+Enter

=МАКС(ЕСЛИОШИБКА(ПСТР(A3;СТРОКА($J$1:$J$999);11)*ЕЧИСЛО(0+ПСТР(A3;СТРОКА($J$1:$J$999);11));0))

По этой формуле он вычленяет номер, все работает, но если растягивать функцию, т.е применять к след строчкам ниже, то возникает ошибка #ИМЯ?
Автор - cartman0098
Дата добавления - 13.12.2017 в 14:01
SLAVICK Дата: Среда, 13.12.2017, 14:07 | Сообщение № 10
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
Цитата cartman0098, 13.12.2017 в 14:01, в сообщении № 9 ()
т.е применять к след строчкам ниже, то возникает ошибка

см. пример

Забыл спросить, у Вас в подписи Excel 2013, а файл "openOffic"а?
К сообщению приложен файл: ddd.xlsx (10.3 Kb)

Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
Цитата cartman0098, 13.12.2017 в 14:01, в сообщении № 9 ()
т.е применять к след строчкам ниже, то возникает ошибка

см. пример

Забыл спросить, у Вас в подписи Excel 2013, а файл "openOffic"а?
Автор - SLAVICK
Дата добавления - 13.12.2017 в 14:07
cartman0098 Дата: Среда, 13.12.2017, 14:12 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Цитата SLAVICK, 13.12.2017 в 14:07, в сообщении № 10 ()
см. пример
К сообщению приложен файл: ddd.xlsx(10Kb)

Хоть убейте , не понимаю, как применить эту формулу для последующих номеров, вручную изменять в формуле D1 на D2, D3 и т.д физически не смогу, жизнь короткая слишком). А Если за нижний правый угол тянуть, то как раз ошибка #ИМЯ? и если смотреть на формулу, то она вообще не меняется после растягивания
 
Ответить
Сообщение
Цитата SLAVICK, 13.12.2017 в 14:07, в сообщении № 10 ()
см. пример
К сообщению приложен файл: ddd.xlsx(10Kb)

Хоть убейте , не понимаю, как применить эту формулу для последующих номеров, вручную изменять в формуле D1 на D2, D3 и т.д физически не смогу, жизнь короткая слишком). А Если за нижний правый угол тянуть, то как раз ошибка #ИМЯ? и если смотреть на формулу, то она вообще не меняется после растягивания
Автор - cartman0098
Дата добавления - 13.12.2017 в 14:12
cartman0098 Дата: Среда, 13.12.2017, 14:14 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Цитата SLAVICK, 13.12.2017 в 14:07, в сообщении № 10 ()
см. пример

Забыл спросить, у Вас в подписи Excel 2013, а файл "openOffic"а?
К сообщению приложен файл: ddd.xlsx(10Kb)

Все, это я туплю, чeрез ctrl+shift+enter же надо, все заработало, огромное спасибо +rep)
 
Ответить
Сообщение
Цитата SLAVICK, 13.12.2017 в 14:07, в сообщении № 10 ()
см. пример

Забыл спросить, у Вас в подписи Excel 2013, а файл "openOffic"а?
К сообщению приложен файл: ddd.xlsx(10Kb)

Все, это я туплю, чeрез ctrl+shift+enter же надо, все заработало, огромное спасибо +rep)
Автор - cartman0098
Дата добавления - 13.12.2017 в 14:14
Che79 Дата: Среда, 13.12.2017, 14:22 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Вариант, учитывающий
Цитата cartman0098, 13.12.2017 в 13:43, в сообщении № 7 ()
нюанс, в некоторых строчках E-mail идет после номера телефона, а не до, как в первом примере
Код
=ЕСЛИОШИБКА(ПСТР(A1;ПОИСК("89?????????";A1);11);"")

UPD
Можно короче :D
Код
=ЕСЛИОШИБКА(--ПСТР(A2;ПОИСК("89";A2);11);"")

Хотя, не, короче не пойдет, если у кого-то 89-й год рождения, то уже ошибка

Добавлено
А вот так вроде "ест" нормально
Код
=ЕСЛИОШИБКА(--ПСТР(A1;ПОИСК(" 89";A1);12);"")
(файл_2)
К сообщению приложен файл: Example_.xlsx (10.1 Kb) · Example_2.xlsx (10.1 Kb)

Делай нормально и будет нормально!

Сообщение отредактировал Che79 - Среда, 13.12.2017, 14:35
 
Ответить
СообщениеВариант, учитывающий
Цитата cartman0098, 13.12.2017 в 13:43, в сообщении № 7 ()
нюанс, в некоторых строчках E-mail идет после номера телефона, а не до, как в первом примере
Код
=ЕСЛИОШИБКА(ПСТР(A1;ПОИСК("89?????????";A1);11);"")

UPD
Можно короче :D
Код
=ЕСЛИОШИБКА(--ПСТР(A2;ПОИСК("89";A2);11);"")

Хотя, не, короче не пойдет, если у кого-то 89-й год рождения, то уже ошибка

Добавлено
А вот так вроде "ест" нормально
Код
=ЕСЛИОШИБКА(--ПСТР(A1;ПОИСК(" 89";A1);12);"")
(файл_2)
Автор - Che79
Дата добавления - 13.12.2017 в 14:22
buchlotnik Дата: Среда, 13.12.2017, 14:35 | Сообщение № 14
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Цитата
Тренирую "регулярки"
тогда уж [vba]
Код
Function tlf(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d{11}"
        If .Test(txt) Then tlf = .Execute(txt)(0) Else tlf = ""
    End With
End Function
[/vba] yes
К сообщению приложен файл: 5316529.xls (36.0 Kb)
 
Ответить
Сообщение
Цитата
Тренирую "регулярки"
тогда уж [vba]
Код
Function tlf(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d{11}"
        If .Test(txt) Then tlf = .Execute(txt)(0) Else tlf = ""
    End With
End Function
[/vba] yes
Автор - buchlotnik
Дата добавления - 13.12.2017 в 14:35
sboy Дата: Среда, 13.12.2017, 14:36 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Цитата Che79, 13.12.2017 в 14:22, в сообщении № 13 ()
Вариант, учитывающий

Эти варианты не учитывают, что год рождения может быть 89 или в адресе почты (если он до телефона) будет 89.

Яндекс: 410016850021169
 
Ответить
Сообщение
Цитата Che79, 13.12.2017 в 14:22, в сообщении № 13 ()
Вариант, учитывающий

Эти варианты не учитывают, что год рождения может быть 89 или в адресе почты (если он до телефона) будет 89.
Автор - sboy
Дата добавления - 13.12.2017 в 14:36
sboy Дата: Среда, 13.12.2017, 14:39 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
buchlotnik, Ну вот еще и ошибки обрабатывать на тренировках) я же лентяй) пусть будет #ЗНАЧ!

Яндекс: 410016850021169
 
Ответить
Сообщениеbuchlotnik, Ну вот еще и ошибки обрабатывать на тренировках) я же лентяй) пусть будет #ЗНАЧ!
Автор - sboy
Дата добавления - 13.12.2017 в 14:39
Che79 Дата: Среда, 13.12.2017, 14:41 | Сообщение № 17
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
sboy, Сергей, я выше в своем сообщении дописал коммент. Судя по данным примера, вот так должно быть нормально (формулу дублирую)
Код
=ЕСЛИОШИБКА(--ПСТР(A1;ПОИСК(" 89";A1);12);"")
К сообщению приложен файл: 0080792.xlsx (10.1 Kb)

Делай нормально и будет нормально!
 
Ответить
Сообщениеsboy, Сергей, я выше в своем сообщении дописал коммент. Судя по данным примера, вот так должно быть нормально (формулу дублирую)
Код
=ЕСЛИОШИБКА(--ПСТР(A1;ПОИСК(" 89";A1);12);"")
Автор - Che79
Дата добавления - 13.12.2017 в 14:41
sboy Дата: Среда, 13.12.2017, 14:47 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Che79,
Цитата sboy, 13.12.2017 в 14:36, в сообщении № 15 ()
или в адресе почты (если он до телефона)

тестируй)
Код
Иванов Иван Иванович 01.01.89 89vanya@mail.ru 89992225555

Яндекс: 410016850021169

Сообщение отредактировал sboy - Среда, 13.12.2017, 14:48
 
Ответить
СообщениеChe79,
Цитата sboy, 13.12.2017 в 14:36, в сообщении № 15 ()
или в адресе почты (если он до телефона)

тестируй)
Код
Иванов Иван Иванович 01.01.89 89vanya@mail.ru 89992225555
Автор - sboy
Дата добавления - 13.12.2017 в 14:47
Che79 Дата: Среда, 13.12.2017, 14:48 | Сообщение № 19
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
sboy, вот шайтан)) здесь, да, формула споткнется, не учел я вариант, когда везде 89.

Делай нормально и будет нормально!
 
Ответить
Сообщениеsboy, вот шайтан)) здесь, да, формула споткнется, не учел я вариант, когда везде 89.
Автор - Che79
Дата добавления - 13.12.2017 в 14:48
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вытащить из базы данных только номера телефонов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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