Manik có một trang tính, trong cột A, có các giá trị văn bản ở định dạng “mikeDAVIS”, trong đó tên của người đó là chữ thường và họ là chữ hoa. Anh ấy muốn tách tên thành hai cột riêng biệt, tùy theo trường hợp của văn bản.

Điều này có thể được thực hiện bằng cách sử dụng công thức hoặc macro. Bất kể bạn sử dụng cách tiếp cận nào, chìa khóa là tìm ra vị trí văn bản chuyển từ chữ thường sang chữ hoa. Điều này chỉ có thể được thực hiện bằng cách kiểm tra từng ký tự trong chuỗi. Vì vậy, nếu bạn muốn sử dụng cách tiếp cận theo công thức, thì bạn sẽ cần sử dụng công thức mảng. Công thức mảng sau trả về họ của bất kỳ thứ gì có trong ô A1:

=MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$255),1))>=65)

* (CODE(MID(A1,ROW($2:$255),1))<90),)+1,255)

Hãy nhớ rằng vì đây là một công thức mảng, bạn nên nhập nó bằng cách nhấn Ctrl + Shift + Enter. Nó trả về mọi thứ trong ô bắt đầu bằng chữ hoa đầu tiên nó tìm thấy. Do đó, trong “mikeDAVIS”, nó sẽ trả về “DAVIS” và trong “mikeDavis”, nó sẽ trả về “Davis”. Giả sử rằng bạn sử dụng công thức mảng trong ô B1, sau đó bạn có thể xác định tên bằng cách sử dụng như sau:

=SUBSTITUTE(A1,B1,"")

Đây là một công thức thông thường, không phải là một công thức mảng.

Có nhiều công thức mảng tương tự có thể hoàn thành nhiều tác vụ giống nhau. Ví dụ: công thức mảng này sẽ trả về tên (tất cả các ký tự cho đến ký tự viết hoa đầu tiên) của bất kỳ thứ gì có trong ô A1:

=LEFT(A1,MAX((CODE(MID(A$1,ROW(INDIRECT("1:"& LEN(A1))),1))>96)*ROW(INDIRECT("1:"&LEN(A1)))))

Sau đó, bạn có thể sử dụng cùng một công thức thông thường (công thức sử dụng hàm SUBSTITUTE) để lấy họ.

Nếu bạn muốn sử dụng cách tiếp cận macro để tìm tên, tất cả những gì bạn cần làm là đưa ra một công thức sẽ trả về vị trí của chữ hoa đầu tiên trong văn bản. Đoạn mã sau trả về “điểm thay đổi” này trong văn bản:

Function GetFirstUpper(MyCell As Range) As Integer     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetFirstUpper = 99     Else         GetFirstUpper = i     End If End Function

Để sử dụng hàm, hãy giả sử rằng tên nằm trong ô A1. Bạn có thể tìm thấy họ và tên bằng cách sử dụng các công thức này trong trang tính của mình:

=LEFT(A1,GetFirstUpper(A1)-1)

=MID(A1,GetFirstUpper(A1),LEN(TRIM(A1))-GetFirstUpper(A1)+1)

Nếu bạn muốn macro của mình trả về các tên thực, bạn có thể sử dụng macro sau để trả về mọi thứ trước chữ cái đầu tiên viết hoa:

Function GetFirstName(MyCell As Range) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetFirstName = sCellValue     Else         GetFirstName = Left(sCellValue, i - 1)

End If End Function

Để sử dụng macro, tất cả những gì bạn cần làm là sử dụng phần sau trong ô trang tính. (Điều này giả định rằng chuỗi văn bản được đánh giá nằm trong ô A1.)

=GetFirstName(A1)

Một biến thể nhỏ trên macro sẽ cho phép bạn tìm nạp tương tự họ, được giả định là mọi thứ bắt đầu bằng chữ cái viết hoa đầu tiên gặp phải.

Function GetLastName(MyCell As Range) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetLastName = sCellValue     Else         GetLastName = Mid(sCellValue, i)

End If End Function

Nếu muốn, bạn có thể kết hợp các macro thành một hàm duy nhất, dựa trên những gì bạn chỉ định, trả về họ hoặc tên:

Function GetName(MyCell As Range, sWanted As String) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetName = sCellValue     Else         If LCase(sWanted) = "first" Then             GetName = Left(sCellValue, i - 1)

Else             GetName = Mid(sCellValue, i)

End If     End If End Function

Để sử dụng chức năng kết hợp này, bạn chỉ cần xác định tên bạn muốn:

=GetName(A1, "First")

Từ “First” được truyền dưới dạng tham số theo cách này trả về tên đầu tiên (mọi thứ trước chữ cái đầu tiên viết hoa). Bất kỳ chuỗi nào khác được truyền dưới dạng tham số thứ hai (chẳng hạn như “Last” hoặc “xxx” hoặc “Rest” hoặc thậm chí “”) sẽ trả về họ.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

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

Mẹo này (9091) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: