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

Вход

Регистрация

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

 

= Мир MS Excel/Из полного адреса клиента необходимо вычленить название улиц - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Из полного адреса клиента необходимо вычленить название улиц (Формулы/Formulas)
Из полного адреса клиента необходимо вычленить название улиц
bravman Дата: Среда, 16.03.2016, 11:08 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день!

Есть адрес клиента формата:

456578, Челябинская обл, Еткульский р-н, Лесной п, Центральная ул, дом № 18
или
454000, Челябинская обл, Челябинск г, Ленина пр-кт, дом № 83

Необходимо из этого списка получить только название улицы (в приведенных примерах искомые слова "Центральная" и "Ленина").

Буду очень благодарен, всю голову уже изломал над данной проблемой.
А в ручную сделать невозможно - более 10 000 строк в файле.....
К сообщению приложен файл: 354786.xlsx(9Kb)
 
Ответить
СообщениеДобрый день!

Есть адрес клиента формата:

456578, Челябинская обл, Еткульский р-н, Лесной п, Центральная ул, дом № 18
или
454000, Челябинская обл, Челябинск г, Ленина пр-кт, дом № 83

Необходимо из этого списка получить только название улицы (в приведенных примерах искомые слова "Центральная" и "Ленина").

Буду очень благодарен, всю голову уже изломал над данной проблемой.
А в ручную сделать невозможно - более 10 000 строк в файле.....

Автор - bravman
Дата добавления - 16.03.2016 в 11:08
китин Дата: Среда, 16.03.2016, 11:17 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3433
Репутация: 544 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
Самое простое это текст по столбцам разделитель запятая. А потом ручками убирать все поселки , сп и т.д.


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
СообщениеСамое простое это текст по столбцам разделитель запятая. А потом ручками убирать все поселки , сп и т.д.

Автор - китин
Дата добавления - 16.03.2016 в 11:17
MacSieM Дата: Среда, 16.03.2016, 11:33 | Сообщение № 3
Группа: Проверенные
Ранг: Участник
Сообщений: 84
Репутация: 18 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте.

Если с наименованием типа улицы, то можно вот так:
Код
=ПРАВСИМВ(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);ДЛСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1)) + ПРОСМОТР(;-СТРОКА($1:$99)/(","=ПСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);СТРОКА($1:$99);ДЛСТР(","))))-1)


PS: пользовался формулой, выложенной здесь


Сообщение отредактировал MacSieM - Среда, 16.03.2016, 11:42
 
Ответить
СообщениеЗдравствуйте.

Если с наименованием типа улицы, то можно вот так:
Код
=ПРАВСИМВ(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);ДЛСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1)) + ПРОСМОТР(;-СТРОКА($1:$99)/(","=ПСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);СТРОКА($1:$99);ДЛСТР(","))))-1)


PS: пользовался формулой, выложенной здесь

Автор - MacSieM
Дата добавления - 16.03.2016 в 11:33
китин Дата: Среда, 16.03.2016, 11:36 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3433
Репутация: 544 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
ну или вот такой монстр получился
Код
=ПСТР(ПОДСТАВИТЬ(ЛЕВБ(A1;ПОИСК("дом";A1)-3);",";"ЯЯЯ";ДЛСТР(ЛЕВБ(A1;ПОИСК("дом";A1)-3))-ДЛСТР(ПОДСТАВИТЬ(ЛЕВБ(A1;ПОИСК("дом";A1)-3);",";"")));ПОИСК("ЯЯЯ";ПОДСТАВИТЬ(ЛЕВБ(A1;ПОИСК("дом";A1)-3);",";"ЯЯЯ";ДЛСТР(ЛЕВБ(A1;ПОИСК("дом";A1)-3))-ДЛСТР(ПОДСТАВИТЬ(ЛЕВБ(A1;ПОИСК("дом";A1)-3);",";""))))+4;99)
К сообщению приложен файл: bravman.xlsx(9Kb)


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщениену или вот такой монстр получился
Код
=ПСТР(ПОДСТАВИТЬ(ЛЕВБ(A1;ПОИСК("дом";A1)-3);",";"ЯЯЯ";ДЛСТР(ЛЕВБ(A1;ПОИСК("дом";A1)-3))-ДЛСТР(ПОДСТАВИТЬ(ЛЕВБ(A1;ПОИСК("дом";A1)-3);",";"")));ПОИСК("ЯЯЯ";ПОДСТАВИТЬ(ЛЕВБ(A1;ПОИСК("дом";A1)-3);",";"ЯЯЯ";ДЛСТР(ЛЕВБ(A1;ПОИСК("дом";A1)-3))-ДЛСТР(ПОДСТАВИТЬ(ЛЕВБ(A1;ПОИСК("дом";A1)-3);",";""))))+4;99)

Автор - китин
Дата добавления - 16.03.2016 в 11:36
MacSieM Дата: Среда, 16.03.2016, 11:39 | Сообщение № 5
Группа: Проверенные
Ранг: Участник
Сообщений: 84
Репутация: 18 ±
Замечаний: 0% ±

Excel 2010
Без наименования типа улицы (очень длинная формула, я не оптимизировал)
Код
=ЛЕВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);ДЛСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1))+ПРОСМОТР(;-СТРОКА($1:$99)/(","=ПСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);СТРОКА($1:$99);ДЛСТР(","))))-1);
-ПРОСМОТР(;-СТРОКА($1:$99)/(" "=ПСТР(ПРАВСИМВ(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);ДЛСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1))+ПРОСМОТР(;-СТРОКА($1:$99)/(","=ПСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);СТРОКА($1:$99);ДЛСТР(","))))-1);СТРОКА($1:$99);ДЛСТР(" "))))-1)


Сообщение отредактировал MacSieM - Среда, 16.03.2016, 11:40
 
Ответить
СообщениеБез наименования типа улицы (очень длинная формула, я не оптимизировал)
Код
=ЛЕВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);ДЛСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1))+ПРОСМОТР(;-СТРОКА($1:$99)/(","=ПСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);СТРОКА($1:$99);ДЛСТР(","))))-1);
-ПРОСМОТР(;-СТРОКА($1:$99)/(" "=ПСТР(ПРАВСИМВ(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);ДЛСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1))+ПРОСМОТР(;-СТРОКА($1:$99)/(","=ПСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);СТРОКА($1:$99);ДЛСТР(","))))-1);СТРОКА($1:$99);ДЛСТР(" "))))-1)

Автор - MacSieM
Дата добавления - 16.03.2016 в 11:39
VIDEO56 Дата: Среда, 16.03.2016, 11:40 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 262
Репутация: 23 ±
Замечаний: 0% ±

Excel 2010
И еще один вариант
[vba]
Код
Function ExtractElement(Txt, n, Separator) As String
'   Функция выдает n-ый элемент текстовой строки Txt, где
'   символ Separator используется как разделитель

    Dim Txt1 As String, TempElement As String
    Dim ElementCount As Integer, i As Integer
    
    Txt1 = Txt
'   Если в качестве разделителя используется пробел, то убираем лишние
'   и двойные пробелы
    If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
    
'   Добавляем разделитель в конец строки (если необходимо)
    If Right(Txt1, 1) <> Separator Then Txt1 = Txt1 & Separator
    
'   Начальные значения
    ElementCount = 0
    TempElement = ""
    
'   Извлекаем элемент
    For i = 1 To Len(Txt1)
        If Mid(Txt1, i, 1) = Separator Then
            ElementCount = ElementCount + 1
            If ElementCount = n Then
'               Found it, so exit
                ExtractElement = TempElement
                Exit Function
            Else
                TempElement = ""
            End If
        Else
            TempElement = TempElement & Mid(Txt1, i, 1)
        End If
    Next i
    ExtractElement = ""
End Function

[/vba]
Код
=ExtractElement(A7;2;",")
К сообщению приложен файл: ___2.xls(31Kb)


Всем удачного дня!

Сообщение отредактировал VIDEO56 - Среда, 16.03.2016, 11:42
 
Ответить
СообщениеИ еще один вариант
[vba]
Код
Function ExtractElement(Txt, n, Separator) As String
'   Функция выдает n-ый элемент текстовой строки Txt, где
'   символ Separator используется как разделитель

    Dim Txt1 As String, TempElement As String
    Dim ElementCount As Integer, i As Integer
    
    Txt1 = Txt
'   Если в качестве разделителя используется пробел, то убираем лишние
'   и двойные пробелы
    If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
    
'   Добавляем разделитель в конец строки (если необходимо)
    If Right(Txt1, 1) <> Separator Then Txt1 = Txt1 & Separator
    
'   Начальные значения
    ElementCount = 0
    TempElement = ""
    
'   Извлекаем элемент
    For i = 1 To Len(Txt1)
        If Mid(Txt1, i, 1) = Separator Then
            ElementCount = ElementCount + 1
            If ElementCount = n Then
'               Found it, so exit
                ExtractElement = TempElement
                Exit Function
            Else
                TempElement = ""
            End If
        Else
            TempElement = TempElement & Mid(Txt1, i, 1)
        End If
    Next i
    ExtractElement = ""
End Function

[/vba]
Код
=ExtractElement(A7;2;",")

Автор - VIDEO56
Дата добавления - 16.03.2016 в 11:40
MacSieM Дата: Среда, 16.03.2016, 11:45 | Сообщение № 7
Группа: Проверенные
Ранг: Участник
Сообщений: 84
Репутация: 18 ±
Замечаний: 0% ±

Excel 2010
VIDEO56, боюсь, что этот способ может дать сбой. В приведенном примере неясно, в какой позиции будет находиться улица. Если есть район, то смещение на пятую позицию, если без него, то на четвертой.
 
Ответить
СообщениеVIDEO56, боюсь, что этот способ может дать сбой. В приведенном примере неясно, в какой позиции будет находиться улица. Если есть район, то смещение на пятую позицию, если без него, то на четвертой.

Автор - MacSieM
Дата добавления - 16.03.2016 в 11:45
SLAVICK Дата: Среда, 16.03.2016, 11:49 | Сообщение № 8
Группа: Модераторы
Ранг: Старожил
Сообщений: 1838
Репутация: 613 ±
Замечаний: 0% ±

2007,2010,2013,2016
И у меня вариант есть:
Код
=СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР(A1;1;МИН(ЕСЛИОШИБКА(ПОИСК(" "&{"пр-кт";"пер";"ул"};A1;1);9^9)));",";ПОВТОР(" ";100));100))

Формула массива.

Обратите внимание на перечисление типов улиц {"пр-кт";"пер";"ул"} - для этого примера хватило 3-х. Но возможно у Вас будет больше - тогда добавьте их в этот кусок.

Ну и если везде есть дом, и нужно с названием улицы - то еще короче - обычная формула:
Код
=СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР(A1;1;ПОИСК(", дом";A1;1)-1);",";ПОВТОР(" ";100));100))
К сообщению приложен файл: 4452337-1-.xlsx(10Kb)


Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Среда, 16.03.2016, 12:01
 
Ответить
СообщениеИ у меня вариант есть:
Код
=СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР(A1;1;МИН(ЕСЛИОШИБКА(ПОИСК(" "&{"пр-кт";"пер";"ул"};A1;1);9^9)));",";ПОВТОР(" ";100));100))

Формула массива.

Обратите внимание на перечисление типов улиц {"пр-кт";"пер";"ул"} - для этого примера хватило 3-х. Но возможно у Вас будет больше - тогда добавьте их в этот кусок.

Ну и если везде есть дом, и нужно с названием улицы - то еще короче - обычная формула:
Код
=СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР(A1;1;ПОИСК(", дом";A1;1)-1);",";ПОВТОР(" ";100));100))

Автор - SLAVICK
Дата добавления - 16.03.2016 в 11:49
bravman Дата: Среда, 16.03.2016, 12:19 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Всем большое спасибо, все способы работают!!!

Но особенно понравился вот этот

И у меня вариант есть:

=СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР(A1;1;МИН(ЕСЛИОШИБКА(ПОИСК(" "&{"пр-кт";"пер";"ул"};A1;1);9^9)));",";ПОВТОР(" ";100));100))
 
Ответить
СообщениеВсем большое спасибо, все способы работают!!!

Но особенно понравился вот этот

И у меня вариант есть:

=СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР(A1;1;МИН(ЕСЛИОШИБКА(ПОИСК(" "&{"пр-кт";"пер";"ул"};A1;1);9^9)));",";ПОВТОР(" ";100));100))

Автор - bravman
Дата добавления - 16.03.2016 в 12:19
МВТ Дата: Среда, 16.03.2016, 20:00 | Сообщение № 10
Группа: Проверенные
Ранг: Обитатель
Сообщений: 475
Репутация: 135 ±
Замечаний: 0% ±

Excel 2007
А я опять предложу регулярку
[vba]
Код
Option Explicit
Function tt(text As String) As String
    Dim obj As Object
    With CreateObject("VBScript.Regexp")
        .Ignorecase = True
        .Pattern = "(?: (?:п|г|с|рп)\.?,)(.*? (ул|пер|пр-кт))"
        Set obj = .Execute(text)
        If obj.Count <> 0 Then tt = obj(0).Submatches(0)
    End With
End Function
[/vba]
 
Ответить
СообщениеА я опять предложу регулярку
[vba]
Код
Option Explicit
Function tt(text As String) As String
    Dim obj As Object
    With CreateObject("VBScript.Regexp")
        .Ignorecase = True
        .Pattern = "(?: (?:п|г|с|рп)\.?,)(.*? (ул|пер|пр-кт))"
        Set obj = .Execute(text)
        If obj.Count <> 0 Then tt = obj(0).Submatches(0)
    End With
End Function
[/vba]

Автор - МВТ
Дата добавления - 16.03.2016 в 20:00
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Из полного адреса клиента необходимо вычленить название улиц (Формулы/Formulas)
Страница 1 из 11
Поиск:

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