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

Вход

Регистрация

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

 

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

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

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

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

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

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

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

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

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

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

Буду очень благодарен, всю голову уже изломал над данной проблемой.
А в ручную сделать невозможно - более 10 000 строк в файле.....
Автор - bravman
Дата добавления - 16.03.2016 в 11:08
китин Дата: Среда, 16.03.2016, 11:17 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7029
Репутация: 1078 ±
Замечаний: 0% ±

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

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеСамое простое это текст по столбцам разделитель запятая. А потом ручками убирать все поселки , сп и т.д.
Автор - китин
Дата добавления - 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
Группа: Модераторы
Ранг: Экселист
Сообщений: 7029
Репутация: 1078 ±
Замечаний: 0% ±

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

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениену или вот такой монстр получился
Код
=ПСТР(ПОДСТАВИТЬ(ЛЕВБ(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
Репутация: 27 ±
Замечаний: 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 (31.0 Kb)

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

Сообщение отредактировал 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
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

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

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

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

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

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

Сообщение отредактировал 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
Всем большое спасибо, все способы работают!!!

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

Цитата SLAVICK, 16.03.2016 в 11:49, в сообщении № 8
И у меня вариант есть:

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

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

Цитата SLAVICK, 16.03.2016 в 11:49, в сообщении № 8
И у меня вариант есть:

=СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(ПСТР(A1;1;МИН(ЕСЛИОШИБКА(ПОИСК(" "&{"пр-кт";"пер";"ул"};A1;1);9^9)));",";ПОВТОР(" ";100));100))
Автор - bravman
Дата добавления - 16.03.2016 в 12:19
МВТ Дата: Среда, 16.03.2016, 20:00 | Сообщение № 10
Группа: Проверенные
Ранг: Обитатель
Сообщений: 476
Репутация: 137 ±
Замечаний: 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 из 1
  • 1
Поиск:

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