У меня есть адреса в одной ячейке, и я хочу извлечь их в разные ячейки в той же строке. В некоторых ячейках четыре строки адреса, а в некоторых — три. Я могу легко разделить, используя текст по столбцам и различные разделители для тех, у кого три, но не для тех, у кого четыре.
введите описание изображения здесь
В первом примере у меня четыре строки, а во втором — три.
Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2
Anchorage, AK 99508 US
Я хотел бы, чтобы вышеизложенное было разделено на 5 ячеек. По одной ячейке для адреса, города, штата, почтового индекса и страны.
Anchorage Oncology Centre
3801 University Lake Drive
Suite 300-B2
Anchorage
AK
99508
US
во втором примере ниже
Providence Alaska Medical Center
3200 Providence Drive
Anchorage, AK 99508 US
Я хотел бы
Providence Alaska Medical Center
3200 Providence Drive
Anchorage
AK
99508
US
Можно ли это сделать с помощью формулы?
Спасибо
решение1
Предположим, что текст «Anchorage, AK 99508 US» находится в ячейке A1. Введите эти формулы.
A4 --> =MID(A1,1,(FIND(",",A1,1))-1)
B4 --> =MID($A$1,(FIND(" ",$A$1,A3))+1,C3-(FIND(" ",$A$1,A3))-1)
C4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))-(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))-1)
D4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))+1,LEN(A1))
key: find() использовался для определения начального/конечного значения для mid(). Здесь мы находим каждый "," и " " в тексте A1.
решение2
Или... вы можете использовать совершенно другой подход. Фактически, два.
Один из них — использовать эту FILTERXML()технику. Один из них занимается разделением ваших данных на две части: «остальную часть» и последнюю строку. «Остальная часть» помещается в первую выходную ячейку. (Ниже я покажу довольно простой способ разделить части.) Последняя строка обрабатывается как строка для замены запятой-пробелов и пробелов на HTML-теги ( </Something><Something>), наполовину преобразуя строку в HTML-строку, затем добавляется открывающий тег в начале и закрывающий тег в конце, далее добавляются внешние HTML-теги, оборачивающие все это. Это фактический ввод для функции, FILTERXML()а ее вывод — четыре ячейки, содержащие информацию последней строки.
Это проще, чем кажется, и становится шаблонным после нескольких раз использования, если время близко. Основные усилия направлены на разделение данных на части и их подготовку. Это потому, что каждое новое использование, похоже, имеет данные, упакованные по-разному.
Одним из преимуществ, помимо шаблонности после практики, является то, что он создает массив выходных данных "естественным образом", а не требует {CSE}ввода ( Ctrl-Shift-Enter). Другое заключается в том, что вы можете выбирать выходные элементы по номеру или с помощью [Last](и этот элемент адресации может быть построен с помощью строковых методов), так что вы можете извлечь точные позиционные данные, если вы можете их указать.
Другой подход заключается в использовании Excel 4 Macroкоманды ("E4M"), которая называется EVALUATE(). Вы не можете использовать ее непосредственно в формуле ячейки, но должны создать именованный диапазон для ее хранения. Вы можете создать то, что вы представляете ей в ячейках, или разработать все это в одной формуле и поместить ее внутрь функции в именованном диапазоне. Что бы вам ни подходило лучше всего или соответствовало вашим склонностям.
В любом случае, как и в случае с FILTERXML(), вы должны подготовить входные данные. Разница здесь в том, что то, что вы делаете таким образом, очень знакомо, и небольшие ошибки, пропуск запятой или опечатка, выглядят "естественно" и их относительно легко найти. Когда FILTERXML()все выглядит "неправильно", и вы некоторое время не будете знать, как должен выглядеть вывод, поэтому ошибки сделать проще и сложнее найти. Преимущество, EVALUATE().
Итак, как подготовить данные? Вам нужны два фрагмента: первый фрагмент (который я назвал выше "остальным"), который вы просто извлечете и представите, и фрагмент, который вы хотите обработать особым образом. Затем в последней части вы создадите строку, которая выглядит как массив, который Excel создает внутри своих формул. Тот, который будет отображаться по всей строке, может выглядеть так, {"a","b",1,"K"}а те же данные, созданные для отображения по столбцу, будут выглядеть так {"a";"b";1;"K"}, и, конечно, их можно смешивать для отображения в диапазоне столбцов и строк. Вы хотите создать один, как последний, с точками с запятой в качестве разделителей.
Но у вас нет ни одного разделителя в последней строке. У вас есть ", " и " ". Итак, сначала вы берете этот кусок и помещаете его в , SUBSTITUTE()чтобы изменить ", " ("запятая-пробел") на просто пробел. Теперь у вас есть только один разделитель, и вы можете изменить его с помощью , SUBSTITUTE()на ";", который вам нужен. Вы также можете применить большинство двойных кавычек, которые вам нужны, вокруг всех элементов, используя ";"вместо только ;. На самом деле, поскольку Excel делает использование двойных кавычек в качестве элементов формулы довольно неприятным, использовать CHAR(34)для них намного проще. "Большинство", потому что вы можете добавлять их только между элементами, SUBSTIITUTE()оставляя начальные и конечные двойные кавычки все еще необходимыми. Поэтому вы добавляете их в открывающую и закрывающую строки вместе с соответствующей из фигурных скобок. Теперь у вас есть подходящая строка, которую EVALUATE()можно разорвать.
Создайте это в ячейке, и когда это заработает, создайте именованный диапазон и поместите все это в EVALUATE()скобки. Вы увидите в формуле ниже, как это выглядит. Неясно, как будут располагаться ваши выходные данные относительно входных данных, поэтому я просто использовал две строки под одним входом, так что входные данные в A1, выходные данные в A2 и A3. Вы можете настроить по своему усмотрению. Итак, внутри именованного диапазона:
=EVALUATE("{"&CHAR(34)&SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-MAX(IFERROR(FIND(CHAR(10),A1,SEQUENCE(1,LEN(A1))),""))),", "," ")," ",CHAR(34)&";"&CHAR(34))&CHAR(34)&"}")
SEQUENCE()Возможно, он был доступен, когда вопрос был написан. Возможно, не был. Я использовал его для простоты, поскольку, в конце концов, Я пишу для людей, которые ищут решение проблемы, но хотят получить актуальное решение. Однако тогда существовали его заменители, распространенный вариант использовал ROW(1:xxx)либо просто, либо сложным образом.
«В тот день», возможно, следовало бы ввести при {CSE}входе.
Он делает все то, о чем уже говорилось.
Маленький элемент здесь — как разделить входные данные. Каждая проблема отличается, но ваша не слишком, слишком сложная. Каждая строка во входной ячейке заканчивается на Alt-Enter, или CHAR(10). Таким образом, вы можете подсчитать их, используя технику «длина содержимого минус длина содержимого после SUBSTITUTE()их удаления». Затем вы оборачиваете вокруг этого a , FIND()который делает то, что обычно не делается: он использует параметр «номер позиции» (третий), чтобы взять массив значений от 1 до длины входных данных, так что у вас есть массив из , FIND()каждый из которых ищет на одно место дальше во входных данных. Это дает массив значений следующего вхождения, поскольку он проходит через входные данные с ошибками в конце, так как после последней есть что-то. Эти ошибки нельзя использовать, поэтому IFERROR()они очищаются. Наибольшее найденное число является точкой останова для входных данных, поэтому используйте MAX()для вывода этого. Затем вы выполняете две формулы, a LEFT()из такого количества символов, чтобы получить все просто, просто сбрасываете на место первые несколько строк (сколько бы их ни было, 3,4,5, сколько угодно). Затем a RIGHT()от длины входа минус эта точка разрыва собирает остаток. Это вход для функции EVALUATE().
Как и с FILTERXML(), это все на самом деле довольно просто, так как массирование материала должно быть сделано в любом направлении. Затем он просто погружается в EVALUATE().