Tổng hợp các phương pháp tách họ và tên (2 người xem)

[hoangdanh282vn]

Topic này tập hợp các phương pháp mẫu, các công thức, hàm tự tạo hỗ trợ cho việc tách họ và tên.
Để hỏi đáp các vấn đề về tách họ và tên, các bạn thảo luận trong Topic sau :
http://www.giaiphapexcel.com/forum/showthread.php?p=206488#post206488

A>. Dùng Công Thức

I>.Tách họ

1>.

=LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)

+ TRIM(A2) : Cắt bỏ những khoảng trắng thừa trong chuỗi. Tạo ra chuỗi Họ và tên theo chuẩn.
+ FIND(" ",TRIM(A2)) : Tìm vị trí khoảng trắng đầu tiên trong chuỗi.
+ FIND(" ",TRIM(A2))-1 : Tìm chiều dài Họ cần tách

2>.

=TRIM(LEFT(SUBSTITUTE(TRIM(A2)," ","      "),7))

+ SUBSTITUTE(TRIM(A2)," "," ") : Tìm và thay thế chiều dài khoảng trắng trong chuỗi từ 1 lên 6.
+ LEFT(SUBSTITUTE(TRIM(A2)," "," "),7) : Cắt chuỗi từ bên trái qua phải với 7 ký tự (Trả về Chuỗi Họ).
+ TRIM(LEFT(SUBSTITUTE(TRIM(A2)," "," "),7)) : Cắt bỏ khoảng trắng thừa trong Chuỗi Họ tìm được.

3>.

 =REPLACE(TRIM(A2),FIND(" ",TRIM(A2)),30,"")

+ FIND(" ",TRIM(A2)) : Tìm vị trí khoảng trắng đầu tiên trong chuỗi.
+ REPLACE(TRIM(A2),FIND(" ",TRIM(A2)),30,"") : Thay thế chuỗi bắt đầu từ vị trí khoảng trắng đầu tiên đến hết chuỗi (chuỗi con trong chuỗi Họ và tên) bằng rỗng.

II>. Tách tên lót

1>.

{=MID(TRIM(A2),FIND(" ",TRIM(A2))+1,MAX((MID(TRIM(A2),ROW($1:$50),1) =" ")*ROW($1:$50))-FIND(" ",TRIM(A2)))}

+ ROW($1:$50) : Tạo ra một mảng gồm 50 phần tử từ 1->50
+ MID(TRIM(A2),ROW($1:$50),1) : Cắt giữa chuỗi của chuỗi Họ và tên với vị trí bắt đầu từ 1 ->50, với chiều dài chuỗi cần cắt là 1 ký tự.
----Hàm trên sẽ trả về một mảng gồm 50 phần tử.vừa có ký tự thường, vùa có khoảng trắng và ký tự rỗng.
+ MID(TRIM(A2),ROW($1:$50),1)=" " : Toán tử so sánh 50 phần tử tìm được với ký tự khoảng trắng.
----Toán tử trên sẽ trả về một mảng gồm 50 phần tử True or False, Nếu so sánh đúng thì trả về True, ngược lại là False.
+ MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50) : Toán tử tạo ra một mảng gồm 50 phần tử (mảng số)
----Nếu phần tử nào là False thì sẽ trả về 0, Là TRUE thì trả về số ứng với vị trí của phần tử TRUE đó.
+ MAX((MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50)) : Tìm ra phần tử có giá trị lớn nhất trong mảng số tìm được ở trên.
----Hàm trên sẽ trả về vị trí khoảng trắng lớn nhất trong chuỗi Họ và tên.
+ MAX((MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50))-FIND(" ",TRIM(A2)) : Trả về chiều dài của Tên lót.
+ MID(TRIM(A2),FIND(" ",TRIM(A2))+1,MAX((MID(TRIM(A2),ROW($1:$50),1) =" ")*ROW($1:$50))-FIND(" ",TRIM(A2)))
----Hàm cắt giữa chuỗi Họ và tên với vị trí bắt đầu là vị trí khoảng trắng đầu tiên+1, với chiều dài chuỗi cần cắt đã tìm ở trên.
+ {} : Ký hiệu của hàm mảng, sau khi gõ xong công thức, nhấn Ctrl+Shift+Enter, các toán tử mảng sẽ bắt đầu hoạt động.

III>. TÁCH TÊN

1>.

=TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," "," "),7))

+ Diễn giải công thức tương tự như mục 2 phần Tách Họ.

2>.

{=MID(TRIM(A2),MAX((MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50))+1,7)}

+ MAX((MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50)) : Tìm vị trí khoảng trắng lớn nhất trong chuỗi Họ và Tên.
+ MAX((MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50))+1 : Tìm vị trí đầu tiên của chuỗi Tên.
+ MID(TRIM(A2),MAX((MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50))+1,7) : Cắt chuỗi Họ và Tên với chiều dài chuỗi cần cắt là 7.

3>.

{=RIGHT(TRIM(A2),LEN(TRIM(A2))-MAX((MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50)))}

+ LEN(TRIM(A2)) : Tìm chiều dài chuẩn của chuỗi Họ và Tên.
+ MAX((MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50)) : Tìm vị trí khoảng trắng lớn nhất trong chuỗi Họ và Tên.
+ LEN(TRIM(A2))-MAX((MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50)) : Tìm chiều dài của chuỗi Tên cần tách.
+ RIGHT(TRIM(A2),LEN(TRIM(A2))-MAX((MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50))) : Cắt phải chuỗi Họ và Tên.

4>.

{=REPLACE(TRIM(A2),1,MAX((MID(TRIM(A2),ROW($1:$50) ,1)=" ")*ROW($1:$50)),"")}

+ MAX((MID(TRIM(A2),ROW($1:$50),1)=" ")*ROW($1:$50)) : Tìm vị trí khoảng trắng lớn nhất trong chuỗi Họ và Tên.
+ REPLACE(TRIM(A2),1,MAX((MID(TRIM(A2),ROW($1:$50),1 )=" ")*ROW($1:$50)),"") : Thay thế chuỗi không chứa tên bằng rỗng.

5>.

=RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND("-",SUBSTITUTE(TRIM(A2)," ","-",LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ","")))))

+ SUBSTITUTE(A2," ","") : Thay thế ký tự khoảng trắng trong chuỗi Họ và Tên bằng rỗng.
+ LEN(SUBSTITUTE(A2," ","")) : Tìm chiều dài chuỗi Họ và Tên sau khi bỏ đi khoảng trắng.
+ LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ","")) : Tìm số khoảng trắng trong chuỗi Họ và Tên ban đầu.
+ SUBSTITUTE(TRIM(A2)," ","-",LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))) : Thay thế ký tự " " bằng "-" ở vị trí " " lớn nhất trong chuỗi.
+ FIND("-",SUBSTITUTE(TRIM(A2)," ","-",LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ","")))) : Tìm vị trí của ký tự "-" trong chuỗi.
+ LEN(TRIM(A2))-FIND("-",SUBSTITUTE(TRIM(A2)," ","-",LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ","")))) : Tìm chiều dài chuỗi Tên cần tách.

 

[hoangdanh282vn]

B. Dùng chức năng Text to Column

1>. Bước 1 : Chọn toàn bộ vùng dữ liệu muốn tách.

2>. Bước 2 : Vào Data Chọn Text to Columns, xuất hiện bảng chọn Original data type, chọn Delimited, Next

3>. Bước 3 : Xuất hiện bảng chọn Delimited : chọn Space, Finish

 

[hoangdanh282vn]

C. Sử dụng hàm tự tạo :

Function Tachten(Hovaten As String, Optional Tuychon As Byte = 1) As String
Dim F As Byte, L As Byte
Application.Volatile (False)
Hovaten = WorksheetFunction.Trim(Hovaten)
F = InStr(Hovaten, " "): L = InStrRev(Hovaten, " ")
Select Case Tuychon
    Case 1 'Ten
        Tachten = Mid(Hovaten, L + 1, 10)
    Case 2 'Ho lot
        Tachten = Left(Hovaten, L - 1)
    Case 3 'Ho
        Tachten = Left(Hovaten, F - 1)
    Case 4 'Lot
        Tachten = Mid(Hovaten, F + 1, L - F - 1)
End Select
End Function

 

[hoangdanh282vn]

Khi nhập liệu vào bảng tính Excel, thông thường ta nhập cả họ và tên vào cùng một ô. Tuy nhiên, có khi sau đó ta cần phải tách riêng tên ra khỏi họ và tên đệm, nếu làm thủ công bằng thao tác sao chép rồi dán qua ô khác thì sẽ rất bất tiện. Ngoài ra có những lúc bạn phải xắp xếp lại theo thứ tự A,B,C,...
Nếu sử dụng hàm Right kết hợp với một vài hàm khác thì cũng có thể cho ra kết quả như bạn muốn, nhưng trong một số trường hợp sẽ cho kết quả không chính xác. Có một phương pháp tối ưu hơn sẽ giúp bạn hoàn thành tốt công việc này, cách thực hiện cũng khá đơn giản như sau:
- Trên bảng tính chứa cột họ tên cần tách, bạn chọn menu Tools > Macro > Visual Basic Editor, lập tức xuất hiện chương trình Microsoft Visual Basic.
- Tiếp theo, bạn nhấn menu Insert và chọn Module sẽ xuất hiện một cửa sổ soạn thảo.

Private Function Tachten(ten As String, lg As Integer)
Dim j As Integer
     Name = Trim(ten)
  For j = Len(Name) To 1 Step -1
    If Mid(Name, j, 1) = " " Then
       If lg = "1" Then
          Tachten = Right(Name, Len(Name) - j)
       Else
          Tachten = Left(Name, j)
       End If
  Exit For
    End If
  Next
End Function

Bạn nhập đoạn mã lệnh sau vào cửa sổ soạn thảo:

- Bạn nhấn Ctrl+S để lưu lại và quay trở về bảng tính Excel.
- Bây giờ để tiến hành tách riêng họ và tên đệm vào một ô mới, bạn đặt con trỏ tại ô mới chứa họ và tên đệm rồi nhập vào công thức =TachTen(B3,0) và nhấn Enter sẽ thấy ngay kết quả.
- Để tách riêng tên vào một ô mới, bạn đặt con trỏ tại ô sẽ chứa tên cần tách và nhập vào công thức =TachTen(B3,1) rồi nhấn Enter là xong.
Ghi chú: B3 chính là địa chỉ của ô ban đầu chứa cả họ, tên đệm và tên.

 

[hoangdanh282vn]

Cách 2: Không dùng vòng For. Dùng cho 2 trường hợp tách Họ hoặc Tên.

Function TachHoTen(ByVal HoVaTen As String, Optional TachTen As Boolean = True) As String
Dim Pos_Right As Integer
If HoVaTen = "" Then GoTo EndFunc
HoVaTen = Trim(HoVaTen) 'Xoá khoảng trống hai bên của "HoVaTen"
Pos_Right = InStrRev(HoVaTen, " ")
'Kiểm tra hợp lệ
If Pos_Right = 0 Or Pos_Right - 1 < 0 Or Pos_Right + 1 > Len(HoVaTen) Then 
    GoTo EndFunc:
End If

If TachTen Then
    TachHoTen = Mid(HoVaTen, Pos_Right + 1)
Else
    TachHoTen = Left(HoVaTen, Pos_Right - 1)
End If

EndFunc:
End Function

=TachHoTen("Nguyễn Duy Khánh"). Kết quả là "Khánh".
=TachHoTen("Nguyễn Duy Khánh",False). Kết quả là "Nguyễn Duy".
Hàm InStrRev chỉ dùng từ Office2000(v9) trở lên.

 

[hoangdanh282vn]

Tham gia 01 đoạn code cho thêm phần sinh động:

Option Explicit
Public Function TachTen(strText, Typename) As String
On Error GoTo thoat
Dim Ho, Ten
Ten = Split(Trim(strText), Space(1), , vbTextCompare)
Ho = Trim(Replace(Trim(strText), Ten(UBound(Ten)), ""))
If Typename Then
    TachTen = Ten(UBound(Ten))
Else
    TachTen = Ho
End If
thoat:
End Function

 

[hoangdanh282vn]

Và đây là hàm Tách tên của anh Mr okebab

Đây là hàm tách họ, tên, lót. Các bác xem cho ý kiến nhé.

Thân!

Option Explicit
Function tach(Chuoi As Range, Kytu As String, Loai As String)
Dim m, n, i As Byte
Dim Bebe As String

    Bebe = WorksheetFunction.Trim(Chuoi.Value)
    For i = 1 To Len(Bebe)
        If Mid(Bebe, i, 1) = Kytu Then
            m = i
        If n = 0 Then n = i
        End If
    Next
    
    If Bebe = "" Or n = 0 Then
        tach = ""
    ElseIf m = n And UCase(Loai) = "LOT" Then
        tach = ""
    Else
        Select Case UCase(Loai)
        Case "HO"
            tach = Left(Bebe, n - 1)
        Case "TEN"
            tach = Mid(Bebe, m + 1)
        Case "LOT"
            tach = Mid(Bebe, n + 1, m - n - 1)
        End Select
    End If
End Function