Excel 셀의 문자열에서 마지막 7개의 숫자 값(문자 아님)을 추출해야 합니다. 문자열에는 파일 이름(문자, 숫자 및 대시의 혼합 가능)과 다른 열로 이동해야 하는 7개의 숫자가 포함되어 있습니다.
예를 들어Johnson Set1- 0 0 1 14 9 54 0
숫자 값은 0부터 999까지 다양하므로 마지막 7자를 추출하는 것은 불가능합니다.
답변1
따라서 원하는 것을 얻기 위해 사용할 수 있는 몇 가지 트릭이 있습니다. 나는 여기서 최선을 다해 설명하고 이러한 트릭을 사용하여 원하는 것을 제공하는 공식을 제공할 것입니다.
핵심은 숫자 목록이 시작되기 전에 공백을 찾는 것입니다. 귀하의 예가 충분히 일반적이라면 이 공간은 항상 오른쪽에서 7번째 공간이 됩니다. 문제는 오른쪽에서 7번째가 왼쪽에서 임의의 숫자 공백이 될 수 있다는 것입니다. 왜냐하면 파일 이름에 설정된 공백 수에 대한 규칙이 없는 것처럼 들리기 때문입니다. 그리고 이 SUBSTITUTE함수를 사용하면 교체할 문자열의 인스턴스(왼쪽부터)를 지정할 수 있습니다.
오른쪽에서 7번째 공백을 얻으려면 이를 왼쪽에서 공백으로 변환해야 합니다. 이를 위해서는 전체 문자열의 공백 수를 알아야 합니다. 일단 그것을 갖고 나면, 그것은 단지 뺄셈의 문제일 뿐입니다.
문자열의 총 공백 수를 찾으려면 SUBSTITUTE및 LEN함수를 사용하는 트릭이 있습니다. 문자열의 모든 공백을 아무것도 없는 것으로 바꾼 다음 출력 길이를 원래 문자열의 길이와 비교하는 것이 아이디어입니다. 물론 차이점은 원래 문자열의 공백 수입니다. 따라서 의 문자열에 대해 A1다음은 공백 수를 반환합니다.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
따라서 원하는 왼쪽에서 공간의 숫자 인스턴스는 항상 왼쪽에 6개의 공간을 더 갖게 됩니다.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6
이제 다음 문제는 SUBSTITUTE변경하려는 인스턴스 번호만 지정할 수 있다는 것입니다. FIND, 숫자 추출에 가장 편리한 에는 이 기능이 없습니다. 그런 다음 원하는 대로 SUBSTITUTE사용할 수 있는 문자열을 생성하는 데 사용하는 것이 비결입니다 . FIND오른쪽에서 7번째 공백을 문자열의 다른 곳에서는 나타나지 않는 문자열로 바꾸면 검색할 고유한 하위 문자열이 있습니다. 따라서 예를 들어 다음을 사용하여 원하는 공간을 "REPLACE!ME"로 바꿀 수 있습니다.
=SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6)
이제 문자열에서 "REPLACE!ME" 인덱스를 찾아 모든 것을 오른쪽으로 반환하기만 하면 됩니다. 이는 , 및 함수를 사용하여 수행할 RIGHT수 LEN있습니다 FIND. 마지막 함수는 일종의 반복적이지만 작업을 완료합니다.
=RIGHT(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6),LEN(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-FIND("REPLACE!ME",SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-LEN("REPLACE!ME")+1)