Hướng dẫn excel formula to keep only letters - công thức excel chỉ giữ lại các chữ cái

Question

#2

Chào fin fang foom

Nội dung chính Show

Làm cách nào để lọc chỉ bảng chữ cái trong excel?
Làm thế nào để bạn cô lập văn bản trong excel?
Làm cách nào để loại bỏ các số và giữ bảng chữ cái trong Excel?
Làm thế nào để tôi chỉ cho phép văn bản trong excel?

Một giải pháp UDF. Sử dụng như một công thức Excel

= Extractalpha (A2)

Dán vào một mô -đun:

Mã số:

Function ExtractAlpha(rC As Range) As String

With CreateObject("VBSCRIPT.REGEXP")
    .Pattern = "[^a-z]"
    .Global = True
    .IgnoreCase = True
    ExtractAlpha = .Replace(rC.Value, "")
End With
End Function

Hy vọng điều này sẽ giúp PGC
PGC

#3

Xin chào PGC01,

Cảm ơn bạn đã lặp lại. Có chức năng của bạn không hoạt động. Nhưng tôi nên nói nếu chúng ta có thể có một giải pháp công thức không phải là giải pháp VBA hoặc bất kỳ bổ trợ nào. Bởi vì nếu tôi gửi email cho đồng nghiệp của mình với bổ trợ của bạn, họ sẽ nhận được tin nhắn bật lên về virus và phải đặt bí mật vĩ mô của bạn. Có thể có một giải pháp công thức??

#4

chào bạn lần nữa nhé

Tôi có thể sai nhưng tôi nghĩ điều đó là không thể.

Câu hỏi với các vấn đề như của bạn, xảy ra thường xuyên, là Excel không cung cấp bất kỳ chức năng văn bản nào để kết hợp một mảng hoặc một loạt các ô (tương đương với Sum () cho các số).

Trong thực tế bạn không thể viết

= Concatenate (A1: A3) mà bạn muốn trở thành A1 & A2 & A3

Đây là một chức năng cơ bản ngăn cản rất nhiều công thức văn bản đơn giản khác.

Đó là lý do tại sao mconcat từ các tiện ích bổ sung MoreFunc rất phổ biến.

Vì vậy, tôi có thể sai nhưng tôi nghĩ bạn thực sự cần VBA hoặc bổ trợ.

Trân trọng PGC
PGC

#5

Cảm ơn PGC01 Tôi thực sự đánh giá cao việc dành thời gian của bạn trên chủ đề này. Sau đó, tôi có thể sẽ sử dụng một giải pháp VBA.

Cảm ơn!

#6

Nhưng tôi nên nói nếu chúng ta có thể có một giải pháp công thức không phải là giải pháp VBA hoặc bất kỳ bổ trợ nào.

Xin chào F Fang Foom:

Dưới đây là một giải pháp dựa trên công thức-tuy nhiên nó sử dụng chức năng mconcat từ MoreFunc Add-in ...

Công thức mảng trong ô B2 là ...

= Mconcat (if (mã (mid (a2, hàng (gián tiếp ("1:" & len (a2))), 1))> 64, mid (a2, hàng (gián tiếp ("1:" & len (a2))) , 1), ""))

Điều này sau đó được sao chép xuống.

#7

Cảm ơn vì công thức Yogi Anand.

#số 8

Vẫn đang sử dụng morefunc ..

= Regex.subst acad (a2, "[^a-za-z]")

Hoặc..

Xác định chuỗiString

Đề cập đến hộp: = thay thế (thay thế (thay thế (thay thế (thay thế (thay thế (thay thế (Sheet2! $ A18,1, ""), 2, ""), 3, ""), 4, ""), 5, 5, " ""), 6, ""), 7, "")

Trong C2,

= Thay thế (thay thế (thay thế (thay thế (thay thế (thay thế (chuỗi, 8, ""), 9, ""), 0, ""), "\", ""), ";", ""), " /"," ")

Hth

#9

Cảm ơn bạn Krishnakumar nó hoạt động tốt.

#10

Chào bạn

Nếu bạn muốn có một giải pháp một lần nhanh chóng chỉ để loại bỏ các số khỏi cột A, bạn có thể sử dụng công thức này trong B1.

= Thay thế (A1, Cột (A1) -1, "")

Cuộn công thức ngay đến cột K, sau đó xuống. Câu trả lời là trong colum k

Trân trọng Derek
Derek

Điều này có thể được thực hiện với một công thức duy nhất trong Excel 2016 trở đi.

Trong khi giải pháp của PNUTS liệt kê các ký tự rõ ràng để dải, giải pháp này liệt kê các ký tự hợp lệ rõ ràng.

Giả sử dữ liệu bẩn của bạn nằm trong cột A. Giả sử bạn muốn dữ liệu sạch của mình trong cột B.

Sử dụng công thức cuối cùng dưới đây, trong ô B1. Để nhập công thức vào ô B1, hãy làm như sau:

Nhấp vào ô B1
Bấm vào thanh công thức
Dán công thức
Nhấn Ctrl+Shift+Enter

Sao chép ô B1 và dán nó xuống cột B theo như bạn cần.

Đầu tiên, đây là một ví dụ ngắn để giải thích những gì đang diễn ra:

=TEXTJOIN("",TRUE,

IFs(
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "t", "t",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "e", "e",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "s", "s",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "T", "T",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "E", "E",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "S", "S",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "2", "2",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = " ", " ",
    true, ""
  )

)

Trong trường hợp này, tôi đã chỉ định các ký tự sau là hợp lệ: T, E, S, T, E, S, 2 và ký tự không gian.

Rõ ràng bạn cần mở rộng công thức ra bảng chữ cái đầy đủ, liệt kê riêng từng ký tự trên và chữ thường riêng biệt. Tương tự như vậy, mở rộng nó ra để bao gồm các số nếu bạn muốn giữ chúng. Lưu ý rằng các số được mã hóa dưới dạng chuỗi.

Làm thế nào điều này hoạt động:

Tóm lại, chúng tôi đang chia chuỗi nguồn thành một mảng các ký tự riêng biệt, sau đó cho mỗi ký tự, kiểm tra xem nó có nằm trong tập hợp các ký tự hợp lệ của chúng tôi hay không và bao gồm nó trong kết quả nếu nó là, nếu không, thay thế nó bằng một chuỗi trống nếu nó không phải.

Hàm 'ifs' đi qua đối số của nó một cặp tại một thời điểm. Nếu đối số đầu tiên đánh giá là đúng, thì đối số thứ hai được trả về. Nếu không, nó tiến hành với cặp đối số tiếp theo - đây là lý do tại sao bạn thấy các chữ cái được liệt kê hai lần trên mỗi hàng. Cặp giá trị cuối cùng trong hàm IFS là bộ 'true' và chuỗi trống. Điều này nói rằng nếu chúng ta đi đến cuối tập hợp các giá trị hợp lệ (tức là và không khớp với giá trị hợp lệ) thì hãy trả lại chuỗi trống.

Thêm nền tảng về lý do tại sao điều này hoạt động:

Đây là một biến thể trên một giải pháp được đưa ra tại Exceljet. Trong giải pháp đó, hàm TextJoin được sử dụng (để kết hợp các giá trị của một mảng) với hàm gián tiếp (chia chuỗi thành một mảng) cùng với toán tử toán học (biểu tượng cộng) để buộc đánh giá tính toán giữa mọi ký tự Trong chuỗi có giá trị số. Các ký tự số trong chuỗi sẽ trả về các giá trị số trong khi các ký tự khác sẽ trả về lỗi. Giải pháp đó sử dụng hàm ISERR để kiểm tra lỗi để quyết định có bao gồm một ký tự nhất định trong đầu ra cuối cùng hay không. Một bài viết tương tự tồn tại ở đó để làm việc theo cách khác - để loại trừ các số và giữ các chữ cái.

Vấn đề tôi muốn được giải quyết là để mã hóa quyết định giá trị nào là hợp lệ và cái nào không. Tôi đã trải qua cố gắng kết hợp các hàm VLookup và Index với hàm gián tiếp nhưng chúng sẽ chỉ hoạt động trên ký tự đầu tiên trong chuỗi. Bí quyết là không phải tất cả các chức năng sẽ hoạt động theo đầu ra của gián tiếp theo cách để đánh giá mọi yếu tố trong mảng (ví dụ: mỗi ký tự trong chuỗi). Bí mật là Exceljet đã sử dụng một toán tử toán học. Nếu bạn kiểm tra tham chiếu chức năng đầy đủ của Microsoft, IFS được phân loại là hàm "logic". Tôi nghi ngờ các hàm logic có thể được sử dụng với gián tiếp theo cách này.

. - 90 (chữ hoa) hoặc 97 - 122 (chữ thường) sẽ chỉ hoạt động nếu tất cả các ký tự trong chuỗi là chữ hoa hoặc tất cả đều là chữ thường, nhưng không phải nếu có hỗn hợp.)

Tôi không biết làm thế nào hiệu suất của giải pháp này so sánh với đề xuất trước đó bằng cách sử dụng thay thế. Nếu bạn chỉ muốn loại bỏ một vài ký tự, tôi khuyên bạn nên sử dụng giải pháp thay thế. Nếu bạn muốn chỉ định rõ ràng các ký tự hợp lệ để giữ (đó là câu hỏi ban đầu), hãy sử dụng câu hỏi này.

Cuối cùng, đây là câu trả lời chính xác mà bạn cần, bao gồm cả chuyển đổi sang chữ hoa mà bạn không lưu ý trong câu hỏi, nhưng được hiển thị trong ví dụ của bạn. . trong danh sách.)

=TEXTJOIN("",TRUE,
IFs(
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "A", "A",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "B", "B",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "C", "C",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "D", "D",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "E", "E",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "F", "F",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "G", "G",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "H", "H",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "I", "I",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "J", "J",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "K", "K",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "L", "L",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "M", "M",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "N", "N",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "O", "O",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "P", "P",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Q", "Q",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "R", "R",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "S", "S",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "T", "T",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "U", "U",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "V", "V",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "W", "W",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "X", "X",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Y", "Y",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Z", "Z",
    true, ""
)
)

Với câu hỏi ban đầu là "loại bỏ tất cả trừ các ký tự alpha" - câu trả lời này thực hiện thủ thuật trong một công thức mà không cần VBA.

Làm cách nào để lọc chỉ bảng chữ cái trong excel?

Chọn Dữ liệu> Bộ lọc. Lấy bộ lọc văn bản hoặc bộ lọc số, sau đó chọn một so sánh, như giữa.Enter tiêu chí bộ lọc và chọn OK. Select Text Filters or Number Filters, and then select a comparison, like Between. Enter the filter criteria and select OK.

Làm thế nào để bạn cô lập văn bản trong excel?

Đây là cách làm điều này:..

Chọn các ô nơi bạn có văn bản ..

Chuyển đến dữ liệu -> Công cụ dữ liệu -> Văn bản đến cột ..

Trong văn bản để trình hướng dẫn cột Bước 1, chọn phân định và nhấn tiếp theo ..

Trong bước 2, hãy kiểm tra tùy chọn khác và nhập @ vào hộp ngay vào nó.....

Trong bước 3, cài đặt chung hoạt động tốt trong trường hợp này.....

Bấm vào kết thúc ..

Làm cách nào để loại bỏ các số và giữ bảng chữ cái trong Excel?

.(2) Hàm trích xuất này cũng sẽ loại bỏ tất cả các loại ký tự ngoại trừ các ký tự số.type the formula =EXTRACTNUMBERS(A2,TRUE) into selected cell directly, and then drag the Fill handle to the range as you need. (2) This EXTRACTNUMBERS function will also remove all kinds of characters except the numeric characters.

Làm thế nào để tôi chỉ cho phép văn bản trong excel?

Hạn chế nhập dữ liệu..

Chọn các ô nơi bạn muốn hạn chế nhập dữ liệu ..

Trên tab Dữ liệu, nhấp vào Xác thực dữ liệu> Xác thực dữ liệu.....

Trong hộp cho phép, chọn loại dữ liệu bạn muốn cho phép và điền vào các tiêu chí và giá trị giới hạn ..

programming excel Excel remove digits

Hướng dẫn excel formula to keep only letters - công thức excel chỉ giữ lại các chữ cái

Làm cách nào để lọc chỉ bảng chữ cái trong excel?

Làm thế nào để bạn cô lập văn bản trong excel?

Làm cách nào để loại bỏ các số và giữ bảng chữ cái trong Excel?

Làm thế nào để tôi chỉ cho phép văn bản trong excel?

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội