Перевернуть почтовый адрес
alefkontar
Дата: Пятница, 24.09.2021, 08:52 |
Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Добрый день. Требуется "перевернуть" почтовый адрес. Попытки разделить текстовую строку по разделителю "запятая", а потом собрать в обратном порядке далеко не во всех случаях дают нужный результат, потому что итоговый вариант нужен в формате: "улица, дом, офис," + "населенный пункт," + "район," + "область," + "индекс". В приложенном файле указано, что дано и что требуется. Логика, на мой взгляд, такая: найти заглавную букву, от нее влево найти запятую и по этой запятой разделить, а потом собрать. Решение лучше формулами, хотя возможны варианты. Спасибо всем, кто откликнется.
Добрый день. Требуется "перевернуть" почтовый адрес. Попытки разделить текстовую строку по разделителю "запятая", а потом собрать в обратном порядке далеко не во всех случаях дают нужный результат, потому что итоговый вариант нужен в формате: "улица, дом, офис," + "населенный пункт," + "район," + "область," + "индекс". В приложенном файле указано, что дано и что требуется. Логика, на мой взгляд, такая: найти заглавную букву, от нее влево найти запятую и по этой запятой разделить, а потом собрать. Решение лучше формулами, хотя возможны варианты. Спасибо всем, кто откликнется. alefkontar
Ответить
Сообщение Добрый день. Требуется "перевернуть" почтовый адрес. Попытки разделить текстовую строку по разделителю "запятая", а потом собрать в обратном порядке далеко не во всех случаях дают нужный результат, потому что итоговый вариант нужен в формате: "улица, дом, офис," + "населенный пункт," + "район," + "область," + "индекс". В приложенном файле указано, что дано и что требуется. Логика, на мой взгляд, такая: найти заглавную букву, от нее влево найти запятую и по этой запятой разделить, а потом собрать. Решение лучше формулами, хотя возможны варианты. Спасибо всем, кто откликнется. Автор - alefkontar Дата добавления - 24.09.2021 в 08:52
Erjoma1981
Дата: Пятница, 24.09.2021, 10:36 |
Сообщение № 2
Группа: Проверенные
Ранг: Участник
Сообщений: 66
Репутация:
25
±
Замечаний:
0% ±
Excel 2010, 2019
Код
=СЦЕПИТЬ(ПСТР(A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+1)+2;ДЛСТР(A2)-НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+1));", ";ПСТР(A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+1)-НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1));ПСТР(A2; НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)- НАЙТИ(",";A2;НАЙТИ(",";A2)+1));ПСТР(A2;НАЙТИ(",";A2)+2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)-НАЙТИ(",";A2)); ЛЕВСИМВ(A2;НАЙТИ(",";A2)-1))
[vba]Код
Public Function ПеревернутьАдрес(Ячейка As Range) Dim МассивСтрок() As String МассивСтрок = Split(Ячейка.Value, ",") ПеревернутьАдрес = Trim(МассивСтрок(4)) & ", " & Trim(МассивСтрок(5)) & ", " & Trim(МассивСтрок(6)) & ", " & Trim(МассивСтрок(7)) _ & ", " & Trim(МассивСтрок(3)) & ", " & Trim(МассивСтрок(2)) & ", " & Trim(МассивСтрок(1)) & ", " & Trim(МассивСтрок(0)) End Function
[/vba]
Код
=СЦЕПИТЬ(ПСТР(A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+1)+2;ДЛСТР(A2)-НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+1));", ";ПСТР(A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+1)-НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1));ПСТР(A2; НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)- НАЙТИ(",";A2;НАЙТИ(",";A2)+1));ПСТР(A2;НАЙТИ(",";A2)+2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)-НАЙТИ(",";A2)); ЛЕВСИМВ(A2;НАЙТИ(",";A2)-1))
[vba]Код
Public Function ПеревернутьАдрес(Ячейка As Range) Dim МассивСтрок() As String МассивСтрок = Split(Ячейка.Value, ",") ПеревернутьАдрес = Trim(МассивСтрок(4)) & ", " & Trim(МассивСтрок(5)) & ", " & Trim(МассивСтрок(6)) & ", " & Trim(МассивСтрок(7)) _ & ", " & Trim(МассивСтрок(3)) & ", " & Trim(МассивСтрок(2)) & ", " & Trim(МассивСтрок(1)) & ", " & Trim(МассивСтрок(0)) End Function
[/vba]Erjoma1981
Ответить
Сообщение Код
=СЦЕПИТЬ(ПСТР(A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+1)+2;ДЛСТР(A2)-НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+1));", ";ПСТР(A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)+1)-НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1));ПСТР(A2; НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+2;НАЙТИ(",";A2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)+1)- НАЙТИ(",";A2;НАЙТИ(",";A2)+1));ПСТР(A2;НАЙТИ(",";A2)+2;НАЙТИ(",";A2;НАЙТИ(",";A2)+1)-НАЙТИ(",";A2)); ЛЕВСИМВ(A2;НАЙТИ(",";A2)-1))
[vba]Код
Public Function ПеревернутьАдрес(Ячейка As Range) Dim МассивСтрок() As String МассивСтрок = Split(Ячейка.Value, ",") ПеревернутьАдрес = Trim(МассивСтрок(4)) & ", " & Trim(МассивСтрок(5)) & ", " & Trim(МассивСтрок(6)) & ", " & Trim(МассивСтрок(7)) _ & ", " & Trim(МассивСтрок(3)) & ", " & Trim(МассивСтрок(2)) & ", " & Trim(МассивСтрок(1)) & ", " & Trim(МассивСтрок(0)) End Function
[/vba]Автор - Erjoma1981 Дата добавления - 24.09.2021 в 10:36
alefkontar
Дата: Пятница, 24.09.2021, 11:39 |
Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Erjoma1981, Спасибо. Формула работает хорошо, а вот функциях почему-то работает только на ячейке-примере, а на других адресах выдает ошибку #ЗНАЧ/
Erjoma1981, Спасибо. Формула работает хорошо, а вот функциях почему-то работает только на ячейке-примере, а на других адресах выдает ошибку #ЗНАЧ/ alefkontar
Ответить
Сообщение Erjoma1981, Спасибо. Формула работает хорошо, а вот функциях почему-то работает только на ячейке-примере, а на других адресах выдает ошибку #ЗНАЧ/ Автор - alefkontar Дата добавления - 24.09.2021 в 11:39
Erjoma1981
Дата: Пятница, 24.09.2021, 12:00 |
Сообщение № 4
Группа: Проверенные
Ранг: Участник
Сообщений: 66
Репутация:
25
±
Замечаний:
0% ±
Excel 2010, 2019
Раз формула работает, а функция нет, тогда в адресе количество запятых больше 4, но меньше 7.
Раз формула работает, а функция нет, тогда в адресе количество запятых больше 4, но меньше 7. Erjoma1981
Ответить
Сообщение Раз формула работает, а функция нет, тогда в адресе количество запятых больше 4, но меньше 7. Автор - Erjoma1981 Дата добавления - 24.09.2021 в 12:00
alefkontar
Дата: Пятница, 24.09.2021, 14:23 |
Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Erjoma1981, я правильно понимаю, что для формулы количество запятых вообще не важно? И как скорректировать функцию, чтобы она могла преобразовывать адрес независимо от количества запятых (или задать для нее диапазон, например, от 2 до 10)? Главное получается, чтобы она искала не любые запятые, а ближайшие слева от заглавной буквы.
Erjoma1981, я правильно понимаю, что для формулы количество запятых вообще не важно? И как скорректировать функцию, чтобы она могла преобразовывать адрес независимо от количества запятых (или задать для нее диапазон, например, от 2 до 10)? Главное получается, чтобы она искала не любые запятые, а ближайшие слева от заглавной буквы. alefkontar
Ответить
Сообщение Erjoma1981, я правильно понимаю, что для формулы количество запятых вообще не важно? И как скорректировать функцию, чтобы она могла преобразовывать адрес независимо от количества запятых (или задать для нее диапазон, например, от 2 до 10)? Главное получается, чтобы она искала не любые запятые, а ближайшие слева от заглавной буквы. Автор - alefkontar Дата добавления - 24.09.2021 в 14:23
Erjoma1981
Дата: Пятница, 24.09.2021, 15:05 |
Сообщение № 6
Группа: Проверенные
Ранг: Участник
Сообщений: 66
Репутация:
25
±
Замечаний:
0% ±
Excel 2010, 2019
я правильно понимаю, что для формулы количество запятых вообще не важно?
Формула будет выдавать ошибку, если запятых меньше 4. И как скорректировать функцию, чтобы она могла преобразовывать адрес независимо от количества запятых (или задать для нее диапазон, например, от 2 до 10)? Главное получается, чтобы она искала не любые запятые, а ближайшие слева от заглавной буквы.
Вижу два варианта: 1. Можно проанализировать первый слева символ в каждом элементе массива, полученного с помощью split. 2. Отказаться от split и сделать через цикл.
я правильно понимаю, что для формулы количество запятых вообще не важно?
Формула будет выдавать ошибку, если запятых меньше 4. И как скорректировать функцию, чтобы она могла преобразовывать адрес независимо от количества запятых (или задать для нее диапазон, например, от 2 до 10)? Главное получается, чтобы она искала не любые запятые, а ближайшие слева от заглавной буквы.
Вижу два варианта: 1. Можно проанализировать первый слева символ в каждом элементе массива, полученного с помощью split. 2. Отказаться от split и сделать через цикл.Erjoma1981
Ответить
Сообщение я правильно понимаю, что для формулы количество запятых вообще не важно?
Формула будет выдавать ошибку, если запятых меньше 4. И как скорректировать функцию, чтобы она могла преобразовывать адрес независимо от количества запятых (или задать для нее диапазон, например, от 2 до 10)? Главное получается, чтобы она искала не любые запятые, а ближайшие слева от заглавной буквы.
Вижу два варианта: 1. Можно проанализировать первый слева символ в каждом элементе массива, полученного с помощью split. 2. Отказаться от split и сделать через цикл.Автор - Erjoma1981 Дата добавления - 24.09.2021 в 15:05
Erjoma1981
Дата: Пятница, 24.09.2021, 17:05 |
Сообщение № 7
Группа: Проверенные
Ранг: Участник
Сообщений: 66
Репутация:
25
±
Замечаний:
0% ±
Excel 2010, 2019
Вроде бы получилось: [vba]Код
Public Function ПеревернутьАдрес(Ячейка As Range) Dim МассивСтрок() As String, МассивАдрес() As String Dim СчетчикЦикла As Byte, КоличествоЭлементовАдреса As Byte Dim objRegex As Object ПеревернутьАдрес = "" If Len(Ячейка.Value) > 0 Then Set objRegex = CreateObject("vbscript.regexp") With objRegex .Pattern = "[А-Я]+" .Global = True .ignorecase = False End With МассивСтрок = Split(Ячейка.Value, ",") ReDim МассивАдрес(КоличествоЭлементовАдреса) КоличествоЭлементовАдреса = 1 МассивАдрес(0) = Trim(МассивСтрок(0)) For СчетчикЦикла = 1 To UBound(МассивСтрок) If objRegex.test(МассивСтрок(СчетчикЦикла)) Then ReDim Preserve МассивАдрес(КоличествоЭлементовАдреса) МассивАдрес(КоличествоЭлементовАдреса) = Trim(МассивСтрок(СчетчикЦикла)) КоличествоЭлементовАдреса = КоличествоЭлементовАдреса + 1 Else МассивАдрес(КоличествоЭлементовАдреса - 1) = МассивАдрес(КоличествоЭлементовАдреса - 1) & ", " & Trim(МассивСтрок(СчетчикЦикла)) End If Next СчетчикЦикла If КоличествоЭлементовАдреса = 5 Then ПеревернутьАдрес = МассивАдрес(4) & ", " & МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) Else ПеревернутьАдрес = "Адрес содержит " & КоличествоЭлементовАдреса & " элементов " End If End If End Function
[/vba]
Вроде бы получилось: [vba]Код
Public Function ПеревернутьАдрес(Ячейка As Range) Dim МассивСтрок() As String, МассивАдрес() As String Dim СчетчикЦикла As Byte, КоличествоЭлементовАдреса As Byte Dim objRegex As Object ПеревернутьАдрес = "" If Len(Ячейка.Value) > 0 Then Set objRegex = CreateObject("vbscript.regexp") With objRegex .Pattern = "[А-Я]+" .Global = True .ignorecase = False End With МассивСтрок = Split(Ячейка.Value, ",") ReDim МассивАдрес(КоличествоЭлементовАдреса) КоличествоЭлементовАдреса = 1 МассивАдрес(0) = Trim(МассивСтрок(0)) For СчетчикЦикла = 1 To UBound(МассивСтрок) If objRegex.test(МассивСтрок(СчетчикЦикла)) Then ReDim Preserve МассивАдрес(КоличествоЭлементовАдреса) МассивАдрес(КоличествоЭлементовАдреса) = Trim(МассивСтрок(СчетчикЦикла)) КоличествоЭлементовАдреса = КоличествоЭлементовАдреса + 1 Else МассивАдрес(КоличествоЭлементовАдреса - 1) = МассивАдрес(КоличествоЭлементовАдреса - 1) & ", " & Trim(МассивСтрок(СчетчикЦикла)) End If Next СчетчикЦикла If КоличествоЭлементовАдреса = 5 Then ПеревернутьАдрес = МассивАдрес(4) & ", " & МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) Else ПеревернутьАдрес = "Адрес содержит " & КоличествоЭлементовАдреса & " элементов " End If End If End Function
[/vba] Erjoma1981
Ответить
Сообщение Вроде бы получилось: [vba]Код
Public Function ПеревернутьАдрес(Ячейка As Range) Dim МассивСтрок() As String, МассивАдрес() As String Dim СчетчикЦикла As Byte, КоличествоЭлементовАдреса As Byte Dim objRegex As Object ПеревернутьАдрес = "" If Len(Ячейка.Value) > 0 Then Set objRegex = CreateObject("vbscript.regexp") With objRegex .Pattern = "[А-Я]+" .Global = True .ignorecase = False End With МассивСтрок = Split(Ячейка.Value, ",") ReDim МассивАдрес(КоличествоЭлементовАдреса) КоличествоЭлементовАдреса = 1 МассивАдрес(0) = Trim(МассивСтрок(0)) For СчетчикЦикла = 1 To UBound(МассивСтрок) If objRegex.test(МассивСтрок(СчетчикЦикла)) Then ReDim Preserve МассивАдрес(КоличествоЭлементовАдреса) МассивАдрес(КоличествоЭлементовАдреса) = Trim(МассивСтрок(СчетчикЦикла)) КоличествоЭлементовАдреса = КоличествоЭлементовАдреса + 1 Else МассивАдрес(КоличествоЭлементовАдреса - 1) = МассивАдрес(КоличествоЭлементовАдреса - 1) & ", " & Trim(МассивСтрок(СчетчикЦикла)) End If Next СчетчикЦикла If КоличествоЭлементовАдреса = 5 Then ПеревернутьАдрес = МассивАдрес(4) & ", " & МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) Else ПеревернутьАдрес = "Адрес содержит " & КоличествоЭлементовАдреса & " элементов " End If End If End Function
[/vba] Автор - Erjoma1981 Дата добавления - 24.09.2021 в 17:05
bmv98rus
Дата: Пятница, 24.09.2021, 17:12 |
Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация:
766
±
Замечаний:
0% ±
Excel 2013/2016
ну так чтоб было что написать, хотя в новых версиях можно все значительно короче сделать.Код
=MID(A2; LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);1)+1;99)&IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);1)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);2);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);2)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);3);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);3)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);4);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);4)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);5);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);5)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);6);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);6)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);7);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);7)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);8);)+1;99);"")
ну так чтоб было что написать, хотя в новых версиях можно все значительно короче сделать.Код
=MID(A2; LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);1)+1;99)&IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);1)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);2);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);2)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);3);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);3)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);4);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);4)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);5);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);5)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);6);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);6)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);7);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);7)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);8);)+1;99);"")
bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Ответить
Сообщение ну так чтоб было что написать, хотя в новых версиях можно все значительно короче сделать.Код
=MID(A2; LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);1)+1;99)&IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);1)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);2);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);2)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);3);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);3)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);4);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);4)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);5);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);5)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);6);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);6)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);7);)+1;99);"")& IFERROR(", "& MID(LEFT(A2;LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);7)-2);IFERROR(LARGE(IFERROR(LOOKUP(IF(LOOKUP(CODE(MID(A2;ROW($1:$256);1));{0;192;224};{0;1;0});ROW($1:$256));ROW($1:$256)/(MID(","&A2;ROW($1:$256);1)=","));1=0);8);)+1;99);"")
Автор - bmv98rus Дата добавления - 24.09.2021 в 17:12
alefkontar
Дата: Понедельник, 27.09.2021, 13:29 |
Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
bmv98rus, у меня эта формула просто копирует исходный адрес
bmv98rus, у меня эта формула просто копирует исходный адрес alefkontar
Ответить
Сообщение bmv98rus, у меня эта формула просто копирует исходный адрес Автор - alefkontar Дата добавления - 27.09.2021 в 13:29
alefkontar
Дата: Понедельник, 27.09.2021, 13:36 |
Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Erjoma1981, формула в строках 11 и 12 приложенного файла выдает не вполне корректный (с точки зрения задачи) результат, потому что в исходном адресе нет, например, элемента "район". Может не быть указания области. То есть возможны варианты "количества элементов адреса". Большая просьба к вам (поскольку ваш вариант наиболее близок к тому, что требуется) - нужно скорректировать формулу, чтобы она была более универсальное. Спасибо.
Erjoma1981, формула в строках 11 и 12 приложенного файла выдает не вполне корректный (с точки зрения задачи) результат, потому что в исходном адресе нет, например, элемента "район". Может не быть указания области. То есть возможны варианты "количества элементов адреса". Большая просьба к вам (поскольку ваш вариант наиболее близок к тому, что требуется) - нужно скорректировать формулу, чтобы она была более универсальное. Спасибо. alefkontar
Ответить
Сообщение Erjoma1981, формула в строках 11 и 12 приложенного файла выдает не вполне корректный (с точки зрения задачи) результат, потому что в исходном адресе нет, например, элемента "район". Может не быть указания области. То есть возможны варианты "количества элементов адреса". Большая просьба к вам (поскольку ваш вариант наиболее близок к тому, что требуется) - нужно скорректировать формулу, чтобы она была более универсальное. Спасибо. Автор - alefkontar Дата добавления - 27.09.2021 в 13:36
Erjoma1981
Дата: Вторник, 28.09.2021, 08:01 |
Сообщение № 11
Группа: Проверенные
Ранг: Участник
Сообщений: 66
Репутация:
25
±
Замечаний:
0% ±
Excel 2010, 2019
Добавил [vba]Код
ElseIf КоличествоЭлементовАдреса = 4 Then НомерЗапятой = InStr(МассивАдрес(3), ",") If НомерЗапятой > 0 Then ПеревернутьАдрес = Trim(Mid(МассивАдрес(3), НомерЗапятой + 1)) & "," & Trim(Left(МассивАдрес(3), НомерЗапятой - 1)) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) Else ПеревернутьАдрес = МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) End If
[/vba]
Добавил [vba]Код
ElseIf КоличествоЭлементовАдреса = 4 Then НомерЗапятой = InStr(МассивАдрес(3), ",") If НомерЗапятой > 0 Then ПеревернутьАдрес = Trim(Mid(МассивАдрес(3), НомерЗапятой + 1)) & "," & Trim(Left(МассивАдрес(3), НомерЗапятой - 1)) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) Else ПеревернутьАдрес = МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) End If
[/vba] Erjoma1981
Сообщение отредактировал Erjoma1981 - Вторник, 28.09.2021, 08:02
Ответить
Сообщение Добавил [vba]Код
ElseIf КоличествоЭлементовАдреса = 4 Then НомерЗапятой = InStr(МассивАдрес(3), ",") If НомерЗапятой > 0 Then ПеревернутьАдрес = Trim(Mid(МассивАдрес(3), НомерЗапятой + 1)) & "," & Trim(Left(МассивАдрес(3), НомерЗапятой - 1)) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) Else ПеревернутьАдрес = МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) End If
[/vba] Автор - Erjoma1981 Дата добавления - 28.09.2021 в 08:01
alefkontar
Дата: Вторник, 28.09.2021, 09:31 |
Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Erjoma1981, по аналогии добавил в функцию проверку наличия 3 элементов - сработало. Но почему-то в 12 строке (см. скриншот) номер дома и офиса идет перед названием улицы, а не наоборот
Erjoma1981, по аналогии добавил в функцию проверку наличия 3 элементов - сработало. Но почему-то в 12 строке (см. скриншот) номер дома и офиса идет перед названием улицы, а не наоборотalefkontar
Ответить
Сообщение Erjoma1981, по аналогии добавил в функцию проверку наличия 3 элементов - сработало. Но почему-то в 12 строке (см. скриншот) номер дома и офиса идет перед названием улицы, а не наоборотАвтор - alefkontar Дата добавления - 28.09.2021 в 09:31
Erjoma1981
Дата: Вторник, 28.09.2021, 09:52 |
Сообщение № 13
Группа: Проверенные
Ранг: Участник
Сообщений: 66
Репутация:
25
±
Замечаний:
0% ±
Excel 2010, 2019
Просто не правильно понял (в столбце "Требуется" файла сообщения 10 такое же выражение ) Измените [vba]
Код
ElseIf КоличествоЭлементовАдреса = 4 Then НомерЗапятой = InStr(МассивАдрес(3), ",") If НомерЗапятой > 0 Then ПеревернутьАдрес = Trim(Mid(МассивАдрес(3), НомерЗапятой + 1)) & "," & Trim(Left(МассивАдрес(3), НомерЗапятой - 1)) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) Else ПеревернутьАдрес = МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) End If
[/vba]
на [vba]Код
ElseIf КоличествоЭлементовАдреса = 4 Then ПеревернутьАдрес = МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0)
[/vba]
Просто не правильно понял (в столбце "Требуется" файла сообщения 10 такое же выражение ) Измените [vba]
Код
ElseIf КоличествоЭлементовАдреса = 4 Then НомерЗапятой = InStr(МассивАдрес(3), ",") If НомерЗапятой > 0 Then ПеревернутьАдрес = Trim(Mid(МассивАдрес(3), НомерЗапятой + 1)) & "," & Trim(Left(МассивАдрес(3), НомерЗапятой - 1)) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) Else ПеревернутьАдрес = МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) End If
[/vba]
на [vba]Код
ElseIf КоличествоЭлементовАдреса = 4 Then ПеревернутьАдрес = МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0)
[/vba] Erjoma1981
Сообщение отредактировал Erjoma1981 - Вторник, 28.09.2021, 09:55
Ответить
Сообщение Просто не правильно понял (в столбце "Требуется" файла сообщения 10 такое же выражение ) Измените [vba]
Код
ElseIf КоличествоЭлементовАдреса = 4 Then НомерЗапятой = InStr(МассивАдрес(3), ",") If НомерЗапятой > 0 Then ПеревернутьАдрес = Trim(Mid(МассивАдрес(3), НомерЗапятой + 1)) & "," & Trim(Left(МассивАдрес(3), НомерЗапятой - 1)) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) Else ПеревернутьАдрес = МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0) End If
[/vba]
на [vba]Код
ElseIf КоличествоЭлементовАдреса = 4 Then ПеревернутьАдрес = МассивАдрес(3) & ", " & МассивАдрес(2) & ", " & МассивАдрес(1) & ", " & МассивАдрес(0)
[/vba] Автор - Erjoma1981 Дата добавления - 28.09.2021 в 09:52
alefkontar
Дата: Вторник, 28.09.2021, 15:24 |
Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация:
0
±
Замечаний:
0% ±
Erjoma1981, спасибо вам, добрый человек! Вот бы еще четко понимать, как это работает! А формулу "допилить" не получится? Я ее хотя бы прочитать могу...
Erjoma1981, спасибо вам, добрый человек! Вот бы еще четко понимать, как это работает! А формулу "допилить" не получится? Я ее хотя бы прочитать могу... alefkontar
Ответить
Сообщение Erjoma1981, спасибо вам, добрый человек! Вот бы еще четко понимать, как это работает! А формулу "допилить" не получится? Я ее хотя бы прочитать могу... Автор - alefkontar Дата добавления - 28.09.2021 в 15:24
jakim
Дата: Вторник, 28.09.2021, 16:42 |
Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 1197
Репутация:
313
±
Замечаний:
0% ±
Excel 2010
Power Query
Ответить
Сообщение Power Query Автор - jakim Дата добавления - 28.09.2021 в 16:42
bmv98rus
Дата: Четверг, 30.09.2021, 16:44 |
Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация:
766
±
Замечаний:
0% ±
Excel 2013/2016
bmv98rus, у меня эта формула просто копирует исходный адрес
там массивный ввод нужен, я сбил пока копировал
bmv98rus, у меня эта формула просто копирует исходный адрес
там массивный ввод нужен, я сбил пока копировалbmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Ответить
Сообщение bmv98rus, у меня эта формула просто копирует исходный адрес
там массивный ввод нужен, я сбил пока копировалАвтор - bmv98rus Дата добавления - 30.09.2021 в 16:44