한 셀에 주소가 있는데 같은 행의 다른 셀에서 주소를 추출하고 싶습니다. 일부 셀에는 4줄의 주소가 있고 일부 셀에는 3줄의 주소가 있습니다. 텍스트를 열로 쉽게 분할하고 3개는 있지만 4개는 구분하지 않는 다양한 구분 기호를 사용할 수 있습니다.
첫 번째 예에는 4개의 줄이 있고 두 번째에는 3개의 줄이 있습니다.
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()출력은 마지막 줄의 정보를 포함하는 4개의 셀입니다.
생각보다 쉽고 시간이 지나면 몇 번 사용한 후에는 판에 박힌 형태가 됩니다. 주요 노력은 데이터를 조각으로 나누고 준비하는 것입니다. 그 이유는 각각의 새로운 용도에 따라 데이터가 다르게 패키징되는 것 같기 때문입니다.
연습 후의 진부한 성격 외에도 한 가지 장점은 {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()이를 출력하는 데 사용합니다. 그런 다음 모든 것을 쉽게 얻을 수 있도록 많은 문자 중 두 가지 수식을 수행합니다. LEFT()처음 몇 줄(아무리 많아도 3,4,5 등 무엇이든)을 제자리에 버리면 됩니다. 그런 다음 RIGHT()입력 길이에서 이 중단점을 뺀 입력의 나머지 부분을 수집합니다. 이것이 함수의 입력입니다 EVALUATE().
와 마찬가지로 FILTERXML()재료 마사지는 어느 방향으로든 수행되어야 하기 때문에 실제로는 매우 쉽습니다. 그런 다음 EVALUATE().