뛰어나다

셀에서 숫자 제거

Strip Numeric Characters From Cell

Excel 수식: 셀에서 숫자 제거일반 공식 |_+_| 요약

텍스트 문자열에서 숫자를 제거하려면 다음을 기반으로 수식을 사용할 수 있습니다. TEXTJOIN 함수 . 표시된 예에서 C5의 공식은 다음과 같습니다.



{= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (A1, ROW ( INDIRECT ('1:100')),1)+0), MID (A1, ROW ( INDIRECT ('1:100')),1),''))}

참고: 이것은 배열 수식 다음을 제외하고 control + shift + enter로 입력해야 합니다. 엑셀 365 .

설명

Excel에는 텍스트 문자열의 문자를 변환하는 방법이 없습니다. 정렬 공식에서 직접. 해결 방법으로 이 수식은 ROW 및 INDIRECT 함수의 도움으로 MID 함수를 사용하여 동일한 결과를 얻습니다. 복사한 C5의 공식은 다음과 같습니다.





 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, ROW ( INDIRECT ('1:100')),1)+0), MID (B5, ROW ( INDIRECT ('1:100')),1),''))

이것은 꽤 복잡해 보이지만 요지는 우리가 정렬 B5의 모든 문자 중 숫자인지 확인하기 위해 각 문자를 테스트합니다. 그렇다면 값을 버리고 다음으로 대체합니다. 빈 문자열 (''). 그렇지 않은 경우 '처리된' 배열에 숫자가 아닌 문자를 추가합니다. 마지막으로 TEXTJOIN 함수(Excel 2019의 새로운 기능)를 사용하여 빈 값을 무시하고 모든 문자를 연결합니다.

Excel에서 if then 문 만들기

내부에서 외부로 작업, MID 기능 한 번에 한 문자씩 B5의 텍스트를 추출하는 데 사용됩니다.핵심은 그리고 간접 여기에서 스니펫:



 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, ROW ( INDIRECT ('1:100')),1)+0), MID (B5, ROW ( INDIRECT ('1:100')),1),''))

다음과 같이 100개의 숫자를 포함하는 배열을 회전시킵니다.

{1,2,3,4,5,6,7,8 .... 99,100}

참고: 100은 처리할 최대 문자를 나타냅니다. 데이터에 맞게 변경하거나 아래 설명된 대로 LEN 기능을 사용하십시오.

이 배열은 다음과 같이 MID 함수에 들어갑니다. 시작 번호 논쟁. 을위한 num_chars , 우리는 1을 사용합니다.

Excel에서 숫자를 무작위 화하는 방법

MID 함수는 다음과 같은 배열을 반환합니다.

 
 ROW ( INDIRECT ('1:100'))

참고: 가독성을 위해 배열의 추가 항목이 제거되었습니다.

이 배열에 0을 추가합니다. 이것은 Excel에서 텍스트를 숫자로 강제 변환하는 간단한 트릭입니다. '1','2','3','4' 등과 같은 숫자 텍스트 값은 오류 없이 변환되지만 숫자가 아닌 값은 실패하고 #VALUE 오류가 발생합니다. 우리는 사용 IF 함수 이랑 ISERR 기능 이러한 오류를 잡기 위해. 오류가 표시되면 숫자가 아닌 문자가 있다는 것을 알고 해당 문자를 처리된 정렬 다른 MID 기능으로:

 
{'3''4''6''5''3'' ''J''i''m'' ''M''c''D''o''n''a''l''d'''''''...}

만약에 하지마 오류가 발생하면 숫자가 있다는 것을 알고 있으므로 배열에 숫자 대신 빈 문자열('')을 삽입합니다.

최종 배열 결과는 다음과 같이 TEXTJOIN 함수에 들어갑니다. 텍스트 1 논쟁. 을위한 구분자 , 우리는 빈 문자열('')을 사용하고 무시_빈 우리는 TRUE를 제공합니다. 그런 다음 TEXTJOIN 연결 배열의 비어 있지 않은 모든 값을 반환하고 결과를 반환합니다.

정확한 배열 길이

100과 같은 숫자를 INDIRECT로 하드코딩하는 대신 다음을 사용할 수 있습니다. LEN 기능 다음과 같이 셀의 실제 문자 수로 배열을 작성하려면 다음과 같이 하십시오.

 
 MID (B5, ROW ( INDIRECT ('1:100')),1)

LEN은 셀의 문자 수를 100 대신 사용되는 숫자로 반환합니다. 이렇게 하면 수식을 자동으로 원하는 수의 문자로 확장할 수 있습니다.

추가 공간 제거

숫자를 제거할 때 여분의 공백 문자가 남을 수 있습니다. 선행 및 후행 공백을 제거하고 단어 사이의 공백을 정규화하려면 이 페이지에 표시된 공식을 트림 기능 :

Excel의 모든 열을 자동 맞춤하는 방법
 
 MID (A1, ROW ( INDIRECT ('1:'& LEN (A1))),1)

SEQUENCE 포함

엑셀 365 , 새로운 시퀀스 함수 위의 ROW + INDIRECT 코드를 대체할 수 있습니다.

 
= TRIM (formula)

여기에서 SEQUENCE + LEN을 사용하여 한 번에 올바른 길이의 배열을 만듭니다.

렛과 함께

다음을 사용하여 이 공식을 더욱 간소화할 수 있습니다. 렛 함수 . 배열은 SEQUENCE 및 LEN을 사용하여 위에서 두 번 생성되기 때문에 배열을 변수로 정의하고 한 번만 생성할 수 있습니다.

 
= TEXTJOIN ('',TRUE, IF ( ISERR ( MID (B5, SEQUENCE ( LEN (B5)),1)+0), MID (B5, SEQUENCE ( LEN (B5)),1),''))

여기서 값 정렬 한 번만 설정되고 MID 함수 내에서 두 번 사용됩니다.

저자 데이브 브런스


^