Как вытащить город из адреса в excel
Функция АДРЕС() возвращает текстовое значение в виде адреса ячейки.
Синтаксис функции
АДРЕС(номер_строки, номер_столбца, [тип_ссылки], [a1], [имя_листа])
Номер_строки Обязательный аргумент. Номер строки, используемый в ссылке на ячейку.
Номер_столбца Обязательный аргумент. Номер столбца, используемый в ссылке на ячейку.
Последние 3 аргумента являются необязательными.
[Тип_ссылки] Задает тип возвращаемой ссылки:
- 1 или опущен: абсолютная ссылка , например $D$7
- 2 : абсолютная ссылка на строку; относительная ссылка на столбец, например D$7
- 3 : относительная ссылка на строку; абсолютная ссылка на столбец, например $D7
- 4 : относительная ссылка, например D7
[а1] Логическое значение, которое определяет тип ссылок: А1 или R1C1. При использовании ссылок типа А1 столбцы обозначаются буквами, а строки — цифрами, например D7 . При использовании ссылок типа R1C1 и столбцы, и строки обозначаются цифрами, например R7C5 (R означает ROW - строка, С означает COLUMN - столбец). Если аргумент А1 имеет значение ИСТИНА или 1 или опущен, то функция АДРЕС() возвращает ссылку типа А1; если этот аргумент имеет значение ЛОЖЬ (или 0), функция АДРЕС() возвращает ссылку типа R1C1.
Чтобы изменить тип ссылок, используемый Microsoft Excel, нажмите кнопку Microsoft Office , затем нажмите кнопку Параметры Excel (внизу окна) и выберите пункт Формулы . В группе Работа с формулами установите или снимите флажок Стиль ссылок R1C1 .
[Имя_листа] Необязательный аргумент. Текстовое значение, определяющее имя листа, которое используется для формирования внешней ссылки. Например, формула =АДРЕС(1;1;;;"Лист2") возвращает значение Лист2!$A$1.
Примеры
Как видно из рисунка ниже (см. файл примера ) функция АДРЕС() возвращает адрес ячейки во всевозможных форматах.
Чаще всего адрес ячейки требуется, чтобы вывести значение ячейки. Для этого используется другая функция ДВССЫЛ() .
Формула =ДВССЫЛ(АДРЕС(6;5)) просто выведет значение из 6-й строки 5 столбца (Е). Эта формула эквивалентна формуле =Е6 .
Возникает вопрос: "Зачем весь этот огород с функцией АДРЕС() ?". Дело в том, что существуют определенные задачи, в которых использование функции АДРЕС() очень удобно, например Транспонирование таблиц или Нумерация столбцов буквами или Поиск позиции ТЕКСТа с выводом значения из соседнего столбца.
Есть адрес клиента формата:
456578, Челябинская обл, Еткульский р-н, Лесной п, Центральная ул, дом № 18
или
454000, Челябинская обл, Челябинск г, Ленина пр-кт, дом № 83
Необходимо из этого списка получить только название улицы (в приведенных примерах искомые слова "Центральная" и "Ленина").
Буду очень благодарен, всю голову уже изломал над данной проблемой.
А в ручную сделать невозможно - более 10 000 строк в файле.
Есть адрес клиента формата:
456578, Челябинская обл, Еткульский р-н, Лесной п, Центральная ул, дом № 18
или
454000, Челябинская обл, Челябинск г, Ленина пр-кт, дом № 83
Необходимо из этого списка получить только название улицы (в приведенных примерах искомые слова "Центральная" и "Ленина").
Буду очень благодарен, всю голову уже изломал над данной проблемой.
А в ручную сделать невозможно - более 10 000 строк в файле. bravman
Есть адрес клиента формата:
456578, Челябинская обл, Еткульский р-н, Лесной п, Центральная ул, дом № 18
или
454000, Челябинская обл, Челябинск г, Ленина пр-кт, дом № 83
Необходимо из этого списка получить только название улицы (в приведенных примерах искомые слова "Центральная" и "Ленина").
Буду очень благодарен, всю голову уже изломал над данной проблемой.
А в ручную сделать невозможно - более 10 000 строк в файле. Автор - bravman
Дата добавления - 16.03.2016 в 11:08
Самое простое это текст по столбцам разделитель запятая. А потом ручками убирать все поселки , сп и т.д.
Самое простое это текст по столбцам разделитель запятая. А потом ручками убирать все поселки , сп и т.д. китин
Если с наименованием типа улицы, то можно вот так:
=ПРАВСИМВ(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);ДЛСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1)) + ПРОСМОТР(;-СТРОКА($1:$99)/(","=ПСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);СТРОКА($1:$99);ДЛСТР(","))))-1)
PS: пользовался формулой, выложенной здесь
Если с наименованием типа улицы, то можно вот так:
=ПРАВСИМВ(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);ДЛСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1)) + ПРОСМОТР(;-СТРОКА($1:$99)/(","=ПСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);СТРОКА($1:$99);ДЛСТР(","))))-1)
PS: пользовался формулой, выложенной здесь MacSieM
Если с наименованием типа улицы, то можно вот так:
=ПРАВСИМВ(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);ДЛСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1)) + ПРОСМОТР(;-СТРОКА($1:$99)/(","=ПСТР(ЛЕВСИМВ(A1;НАЙТИ(", дом";A1)-1);СТРОКА($1:$99);ДЛСТР(","))))-1)
PS: пользовался формулой, выложенной здесь Автор - MacSieM
Дата добавления - 16.03.2016 в 11:33
=ЛЕВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(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)
=ЛЕВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(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)
=ЛЕВСИМВ(ПРАВСИМВ(ЛЕВСИМВ(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)
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
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
Всем удачного дня!
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
VIDEO56, боюсь, что этот способ может дать сбой. В приведенном примере неясно, в какой позиции будет находиться улица. Если есть район, то смещение на пятую позицию, если без него, то на четвертой.
VIDEO56, боюсь, что этот способ может дать сбой. В приведенном примере неясно, в какой позиции будет находиться улица. Если есть район, то смещение на пятую позицию, если без него, то на четвертой. MacSieM
Здраствуйте! На форуме новичок и с excel пока на Вы. Прочитав материал предложенный на сайте - полного ответа к своей проблеме не нашел. Надеюсь на помощь сообщества ExcelWorld.
Вся проблема целиком: Есть массив с городами разбитыми по зонам, в зависимости от километража(1, 2, 3, 4 зоны доставки). И есть столбец с адресом покупателя(город, ул ., дом. ).
Необходимо: создать формулу для подсчета количества доставок по каждой зоне.
Как я вижу решение данной задачи:
1) Извлечь из ячейки с адресом ТОЛЬКО название города и зафиксировать его в соседней ячейке.
2) При наличии столбца с городами(пункт 1) и массива с зонами, написанными в 2 столбца(1й- название городов; 2й - номер зоны в которой находится) - применить ВПР.
Проблема с извлечениеи ТОЛЬКО названия города - вот там и появляются варианты, при которых "Текст по столбцам" не помогает.
Приветствуются любые варианты решения(с ВПР без ВПР, главное чтобы работало и экономило время) - пока этот процесс делается вручную.
П.С. В порядке бреда: Нельзя ли столбец с городами(из листа "Для ВПР") использовать как проверочный для каждой ячейки с адресом - при получении совпадения города из ячейки адреса с городом из столбца с городами - получать в отдельном столбце собственно наименование данного города - либо сразу зону в которой он находится. Возможно ли это?
Здраствуйте! На форуме новичок и с excel пока на Вы. Прочитав материал предложенный на сайте - полного ответа к своей проблеме не нашел. Надеюсь на помощь сообщества ExcelWorld.
Вся проблема целиком: Есть массив с городами разбитыми по зонам, в зависимости от километража(1, 2, 3, 4 зоны доставки). И есть столбец с адресом покупателя(город, ул ., дом. ).
Необходимо: создать формулу для подсчета количества доставок по каждой зоне.
Как я вижу решение данной задачи:
1) Извлечь из ячейки с адресом ТОЛЬКО название города и зафиксировать его в соседней ячейке.
2) При наличии столбца с городами(пункт 1) и массива с зонами, написанными в 2 столбца(1й- название городов; 2й - номер зоны в которой находится) - применить ВПР.
Проблема с извлечениеи ТОЛЬКО названия города - вот там и появляются варианты, при которых "Текст по столбцам" не помогает.
Приветствуются любые варианты решения(с ВПР без ВПР, главное чтобы работало и экономило время) - пока этот процесс делается вручную.
П.С. В порядке бреда: Нельзя ли столбец с городами(из листа "Для ВПР") использовать как проверочный для каждой ячейки с адресом - при получении совпадения города из ячейки адреса с городом из столбца с городами - получать в отдельном столбце собственно наименование данного города - либо сразу зону в которой он находится. Возможно ли это? Railord
Вся проблема целиком: Есть массив с городами разбитыми по зонам, в зависимости от километража(1, 2, 3, 4 зоны доставки). И есть столбец с адресом покупателя(город, ул ., дом. ).
Необходимо: создать формулу для подсчета количества доставок по каждой зоне.
Как я вижу решение данной задачи:
1) Извлечь из ячейки с адресом ТОЛЬКО название города и зафиксировать его в соседней ячейке.
2) При наличии столбца с городами(пункт 1) и массива с зонами, написанными в 2 столбца(1й- название городов; 2й - номер зоны в которой находится) - применить ВПР.
Проблема с извлечениеи ТОЛЬКО названия города - вот там и появляются варианты, при которых "Текст по столбцам" не помогает.
Приветствуются любые варианты решения(с ВПР без ВПР, главное чтобы работало и экономило время) - пока этот процесс делается вручную.
П.С. В порядке бреда: Нельзя ли столбец с городами(из листа "Для ВПР") использовать как проверочный для каждой ячейки с адресом - при получении совпадения города из ячейки адреса с городом из столбца с городами - получать в отдельном столбце собственно наименование данного города - либо сразу зону в которой он находится. Возможно ли это? Автор - Railord
Дата добавления - 01.10.2013 в 23:18
Как извлечь штат, почтовый индекс или город из адреса в Excel?
Что бы вы сделали для извлечения штата, почтового индекса или города из адресной ячейки в отдельные ячейки? В этой статье мы покажем вам формулы решения этой проблемы. Пожалуйста, просмотрите для более подробной информации.
Извлечь штат, почтовый индекс или город из адреса в Excel
Предположим, что адрес находится в ячейке A2, выполните следующие действия, шаг за шагом, чтобы извлечь штат, почтовый индекс или город из адреса в Excel.
Извлечь состояние из адреса
1. Выберите пустую ячейку для размещения извлеченного состояния. Здесь я выбираю ячейку B2.
2. Скопируйте в нее приведенную ниже формулу и нажмите Enter ключ.
=MID(SUBSTITUTE(A2," ",""), FIND(",",SUBSTITUTE(A2," ",""))+8,2)
Тогда вы получите Состояние в выбранной ячейке.
Заметки:
3). Цифра 2 означает, что состояние в этом адресе состоит только из двух символов. Вы можете изменить номера в зависимости от вашего адреса.
Извлечь почтовый индекс из адреса
1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ.
=MID(SUBSTITUTE(A2," ",""), FIND(",",SUBSTITUTE(A2," ",""))+10,5)
Внимание: Как упоминалось выше, цифра 10 в формуле является десятым символом после первой запятой; а цифра 5 означает, что почтовый индекс состоит из пяти цифр.
Пожалуйста, нажмите Как извлечь почтовый индекс из списка адресов в Excel? для получения более подробной информации о извлечении почтового индекса из адреса.
Извлечь город из адреса
Чтобы извлечь город из адреса, просто измените два последних числа в приведенной выше формуле на 1 и 6.
=MID(SUBSTITUTE(A2," ",""), FIND(",",SUBSTITUTE(A2," ",""))+1,6)
Легко извлекайте домен электронной почты из адреса электронной почты в Excel:
Работы С Нами Kutools for Excel's Извлечь текст Утилита, вы можете легко извлечь домен электронной почты из адреса электронной почты или извлечь текст между двумя символами из ячеек в Excel. Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная трасса)
Как извлечь номер улицы из адреса в Excel?
Во многих случаях для какой-либо цели вам нужно извлечь номер улицы из адреса. В этой статье вы узнаете, как извлечь номер дома из адреса в Excel с подробностями.
Извлечь номер дома из адреса в Excel
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Предположим, у вас есть адрес, расположенный в ячейке A2, для извлечения номера улицы из адресной ячейки в Excel выполните следующие действия.
1. Выберите пустую ячейку рядом с ячейкой адреса.
2. Есть три формулы, которые можно использовать для извлечения номера дома из адреса. Скопируйте и вставьте одну из формул ниже в Панель формул, затем нажмите Enter ключ.
Формула 1: = ЕСЛИ (ЕСТЬ ОШИБКА (ЗНАЧЕНИЕ (ЛЕВО (A2,1; 2))); ""; ЛЕВО (A2; НАЙТИ (""; A1) -XNUMX))
Формула 2. = ЕСЛИ (ЕЧИСЛО (ЗНАЧЕНИЕ (ЛЕВО (A2,1; 2))); ЗНАЧЕНИЕ (ЛЕВО (A2; НАЙТИ (""; A1) -XNUMX)), "")
Формула 3. = ЛЕВЫЙ (A2; НАЙТИ (""; A2,1))
Тогда вы получите номер дома в выбранной ячейке. Смотрите скриншот:
Если есть список адресов, из которых нужно извлечь номера улиц, просто перетащите маркер заполнения в нужный диапазон.
Легко извлекайте домен электронной почты из адреса электронной почты в Excel:
Работы С Нами Kutools for Excel's Извлечь текст Утилита, вы можете легко извлечь домен электронной почты из адреса электронной почты или извлечь текст между двумя символами из ячеек в Excel. Смотрите скриншот: Скачайте и попробуйте прямо сейчас! 30-дневный бесплатный маршрут
Читайте также: