Frank cần chuyển đổi 4.000-5.000 tên hàng ngày từ chữ hoa sang chữ hoa và chữ thường. Hàm PROPER mang lại cho anh ta một kết quả khả quan mà vẫn cần xem xét và chỉnh sửa thủ công để đối phó với các từ viết tắt, v.v. không nên che giấu. Suy nghĩ của Frank là anh ta có thể cần hàm để bao gồm tra cứu đối với một số loại danh sách ngoại lệ, vì vậy anh ta tự hỏi liệu có khả năng như vậy để chuyển đổi trường hợp trong Excel hay không.

Cách tốt nhất để xử lý điều này sẽ phụ thuộc vào dữ liệu mà bạn đang bắt đầu. Ví dụ: giả sử sau đây là giá trị của hai ô trong dữ liệu nguồn của bạn:

Big John's Mining, LLC USA

Nếu ngoại lệ của bạn là bạn không muốn thay đổi trường hợp LLC, thì bạn cần một phương pháp sẽ xem xét các phần của mỗi ô. Nếu ngoại lệ của bạn là bạn không muốn trường hợp của Hoa Kỳ thay đổi, thì bạn cần một phương pháp sẽ đánh giá tổng thể nội dung của từng ô.

Việc xử lý loại dữ liệu thứ hai dễ dàng hơn so với loại dữ liệu đầu tiên, vì vậy chúng ta hãy xem xét nó trước. Công thức sau dựa vào danh sách ngoại lệ mà bạn có ở đâu đó trong sổ làm việc của mình. Danh sách ngoại lệ này cần được thiết lập dưới dạng phạm vi được đặt tên, sử dụng tên Exceptions.

Nếu dữ liệu ban đầu của bạn nằm trong cột A, bạn có thể đặt công thức này vào ô B1 và ​​sau đó sao chép nó xuống nếu cần:

=IFERROR(VLOOKUP(A1,Exceptions,1,0),PROPER(A1))

Bất kỳ ô nào khớp hoàn toàn với bất kỳ nội dung nào trong bảng Ngoại lệ của bạn sẽ trông giống hệt như ngoại lệ và bất kỳ ô nào không khớp hoàn toàn sẽ bị áp dụng PROPER cho ô đó.

Đối với loại dữ liệu đầu tiên (nơi bạn cần xem xét bên trong mỗi ô để biết các ngoại lệ), tốt nhất là dựa vào macro. Sau đây là ví dụ về một ví dụ bạn có thể sử dụng làm điểm bắt đầu.

Function MyProper(ByVal r As Range) As String     Dim vExceptions As Variant     Dim vReplacements As Variant     Dim vWords As Variant     Dim iRaw As String     Dim J As Integer     Dim K As Integer     Dim sTemp As String

' Exceptions array     vExceptions = Array("USA", "PhD", "LLC", "and", _       "Kentucky", "D.C.")



' Replacements array     vReplacements = Array("USA", "PhD", "LLC", "and", _       "KY", "DC")



' Convert the text to Proper case and store in a string     iRaw = StrConv(r, 3)

' Split the words into an array     vWords = Split(iRaw, " ")

For J = LBound(vWords) To UBound(vWords)

For K = LBound(vExceptions) To UBound(vExceptions)

If UCase(vWords(J)) = UCase(vExceptions(K)) Then                 vWords(J) = vReplacements(K)

End If         Next K     Next J

' Rebuild the cell contents     sTemp = ""

For J = LBound(vWords) To UBound(vWords)

sTemp = sTemp & " " & vWords(J)

Next J

MyProper = Trim(sTemp)

End Function

Đây là một chức năng do người dùng định nghĩa, vì vậy bạn có thể sử dụng cách sau để thực hiện chuyển đổi trên dữ liệu nguồn của mình:

=MyProper(A1)

Tốc độ của macro sẽ phụ thuộc vào hai điều: Số lần nó được sử dụng trong trang tính của bạn (số từ bạn cần sửa đổi) và số lượng ngoại lệ bạn đang kiểm tra trong macro. Với 4.000-5.000 từ và hàng tá ngoại lệ đang được kiểm tra, macro vẫn phải hoạt động đủ nhanh để có thể chấp nhận được. (Chắc chắn sẽ nhanh hơn việc bạn kiểm tra bằng tay!)

Hàm dựa vào hai mảng, vExceptions và vReplacements. Nó đưa nội dung ô vào mảng AdWords bằng cách sử dụng chức năng Split. (Sau khi hàm Split được thực thi, mọi phần tử của mảng v AdWords sẽ chứa một từ, như được xác định bởi sự xuất hiện của khoảng trắng.) Sau đó, mỗi phần tử của mảng v AdWords được so sánh với mỗi phần tử của mảng vExceptions. Nếu chúng khớp (hoặc đúng hơn, nếu phiên bản viết hoa của mỗi chúng khớp với nhau), thì phần tử tương ứng của mảng vReplacements sẽ được sử dụng thay cho từ gốc. Cách tiếp cận này có thêm lợi ích là cho phép bạn thay thế các từ viết tắt, như được thực hiện khi thay thế KY cho Kentucky và DC cho D.C.

Hãy nhớ rằng tôi đã đề cập rằng macro này chỉ là một điểm khởi đầu tốt.

Rõ ràng bạn sẽ cần phải sửa đổi nó để phản ánh danh sách ngoại lệ và danh sách thay thế của mình. Ngoài ra, bạn cần hiểu rằng nếu có dấu chấm câu trong dữ liệu gốc của bạn, thì dấu câu đó được coi là một phần của “các từ” được phát nổ bởi chức năng Split. Ví dụ: nếu dữ liệu gốc có nội dung như “Davis, LLC, Stanton”, thì dấu phẩy được coi là một phần của các từ mà chúng theo sau. (Hãy nhớ rằng sự phân chia được thực hiện tại các dấu cách.) Do đó, bạn sẽ kết thúc bằng “Davis, Llc, Stanton” trong kết quả của mình vì “LLC” trong mảng vExceptions sẽ không khớp với “LLC” trong v AdWords mảng.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (7840) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.