Bài viết: Tổng hợp các phương pháp tách họ và tên

Liên hệ QC
hoangdanh282vn

hoangdanh282vn

Nguyễn Cảnh Hoàng Danh
Thành viên danh dự
Tham gia
21/12/07
Bài viết
1,900
Được thích
5,277
Nghề nghiệp
Kinh doanh các mặt hàng văn phòng phẩm
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ọ tên, các bạn thảo luận trong Topic sau:
http://www.giaiphapexcel.com/forum/showthread.php?426

tonghopTachHoTen.JPG


A. Dùng Công Thức

I.Tách họ

1>.
PHP: 
=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>.
PHP: 
=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>.
PHP: 
 =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>.
PHP: 
{=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>.
PHP: 
=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>.
PHP: 
{=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>.
PHP: 
{=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>.
PHP: 
{=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>.
PHP: 
=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.

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
C. Sử dụng hàm tự tạo
Mã: 
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

Ví dụ áp dụng:
=tachten("Nguyễn Cảnh Hoàng Danh,2) kết quả là Nguyễn Cảnh Hoàng
=tachten("Nguyễn Cảnh Hoàng Danh) kết quả là Danh


Hoặc dùng:

Mã: 
'Link: http://www.giaiphapexcel.com/forum/showthread.php?30872

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)    'Xoa khoang trang hai ben cua "HoVaTen"
    Pos_Right = InStrRev(HoVaTen, " ")
   
    'Kiem tra hop le
    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

Ví dụ áp dụng:
=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.


Hoặc dùng:

Mã: 
'Link: http://www.giaiphapexcel.com/forum/showthread.php?30872


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

Ví dụ áp dụng:
=TachTen("Nguyễn Văn Sơn",1) kết quả là "Sơn"


Hoặc dùng:

Mã: 
'Link: http://www.giaiphapexcel.com/forum/showthread.php?30872


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
Ví dụ áp dụng: A1 chứa chuỗi "Nguyễn Văn A"
=Tach(A1," ","ho") kết quả là Nguyễn
=Tach(A1," ","lot") kết qủa là Văn
=Tach(A1," ","ten") kết quả là A

Hoặc dùng:

Mã: 
'Link: http://www.giaiphapexcel.com/forum/showthread.php?92080
Option Explicit


Function TachHo(ByVal HoTen As String) As String
    HoTen = Trim(HoTen)
    If HoTen = "" Then Exit Function
    Dim SplHoTen
    SplHoTen = Split(HoTen, " ")
    TachHo = SplHoTen(0)
End Function


Function TachTen(ByVal HoTen As String) As String
    HoTen = Trim(HoTen)
    If HoTen = "" Then Exit Function
    Dim SplHoTen
    SplHoTen = Split(HoTen, " ")
    TachTen = SplHoTen(UBound(SplHoTen))
End Function


Function TachTenLot(ByVal HoTen As String) As String
    HoTen = WorksheetFunction.Trim(HoTen)
    If HoTen = "" Then Exit Function
    Dim SplHoTen, u As Long
    SplHoTen = Split(HoTen, " ")
    u = UBound(SplHoTen) - 1
    If u > 0 Then
        ReDim Preserve SplHoTen(0 To u)
        TachTenLot = Join(SplHoTen, " ")
        TachTenLot = Mid(TachTenLot, InStr(TachTenLot, " ") + 1)
    End If
End Function


Function TachHoTenLot(ByVal HoTen As String) As String
    HoTen = WorksheetFunction.Trim(HoTen)
    If HoTen = "" Then Exit Function
    Dim SplHoTen, u As Long
    SplHoTen = Split(HoTen, " ")
    u = UBound(SplHoTen) - 1
    If u = -1 Then
        TachHoTenLot = HoTen
    Else
        ReDim Preserve SplHoTen(0 To u)
        TachHoTenLot = Join(SplHoTen, " ")
    End If
End Function

'Tach Ten Tat
Function HoTenTat(ByVal HoTen As String) As String
    HoTen = Trim(HoTen)
    If HoTen = "" Then Exit Function
    Dim SplHoTen, i As Long
    SplHoTen = Split(HoTen, " ")
    For i = 0 To UBound(SplHoTen)
        If SplHoTen(i) > " " Then
            HoTenTat = HoTenTat + Left(SplHoTen(i), 1)
        End If
    Next
End Function

Ví dụ áp dụng: Ví dụ ô A1 chưa chuỗi "Hoàng Trọng Nghĩa"
=TachHoTenLot(A1) kết quả là "Hoàng Trọng"
=TachHo(A1) kết quả là "Hoàng"
=TachTenLot(A1) kết quả là "Trọng"
=TachTen(A1) kết quả là "Nghĩa"
 
Chỉnh sửa lần cuối bởi điều hành viên:
Upvote 0
Cho em hỏi Code sau
Function TinhTue(ByVal HoTen As String) As String
HoTen = Trim(HoTen)
If HoTen = "" Then Exit Function
Dim SplHoTen
SplHoTen = Split(HoTen, ",")
TinhTue = SplHoTen(UBound(SplHoTen))
End Function
Dùng tách Tỉnh (Nam Hồng, Đông Anh, Hà Nội) thành (Hà Nội)\
Tuy nhiên với code ở trên em không biết làm thế nào để bớt ký tự dấu cách
"Kết quả mong muốn là "Hà Nội" tuy nhiên hiện tại là " Hà Nội")

Mong anh chị giải đáp giúp em.
 
tueyennhi đã viết:
Cho em hỏi Code sau
Function TinhTue(ByVal HoTen As String) As String
HoTen = Trim(HoTen)
If HoTen = "" Then Exit Function
Dim SplHoTen
SplHoTen = Split(HoTen, ",")
TinhTue = SplHoTen(UBound(SplHoTen))
End Function
Dùng tách Tỉnh (Nam Hồng, Đông Anh, Hà Nội) thành (Hà Nội)\
Tuy nhiên với code ở trên em không biết làm thế nào để bớt ký tự dấu cách
"Kết quả mong muốn là "Hà Nội" tuy nhiên hiện tại là " Hà Nội")

Mong anh chị giải đáp giúp em.
Nhấp chuột vào đây để mở rộng...

Lồng thêm hàm Trim() vào
 
Thú thực em không có chút VBA căn bản, chỉ sao chép của anh chị và tự nghĩ thêm, nên em còn không biết cho hàm Trim vào đâu mặc dù có biết ý nghĩa của hàm.
Em thử Hoten = Trim(Hoten) mà không được
 
Lần chỉnh sửa cuối:
A, em làm được rồi, thank anh :)
 
tueyennhi đã viết:
Cho em hỏi Code sau
Function TinhTue(ByVal HoTen As String) As String
HoTen = Trim(HoTen)
If HoTen = "" Then Exit Function
Dim SplHoTen
SplHoTen = Split(HoTen, ",")
TinhTue = SplHoTen(UBound(SplHoTen))
End Function
Dùng tách Tỉnh (Nam Hồng, Đông Anh, Hà Nội) thành (Hà Nội)\
Tuy nhiên với code ở trên em không biết làm thế nào để bớt ký tự dấu cách
"Kết quả mong muốn là "Hà Nội" tuy nhiên hiện tại là " Hà Nội")

Mong anh chị giải đáp giúp em.
Nhấp chuột vào đây để mở rộng...

Nếu chỉ tách như thế thôi sao không dùng hàm cho khỏe
=TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",255)),255))
 
Cho em hỏi với các bác,tách như thế rồi nếu muốn ghép lại như cũ thì dùng hàm nào hoặc có code nào đế sử dụng
 
Sao đánh công thức xong , 2 đầu có dấu {} mà nhấn Ctrl+ Shift+ Enter mà công thức cứ trơ ra . mong các bạn giúp đỡ
 
Cám ơn diễn đàn rất nhiều
Tôi không phải chuyên ngành kế toán nên không phải là dân chuyên ghiệp Excel nhưng trong công việc đòi hỏi phải học hỏi thêm rất nhiều
Qua diễn đàn tôi thấy mình học được rất nhiều kiến thức hay
Một lần nữa xin chân thành cám ơn các thành viên và admin đã tạo ra một website rất hay và bổ ích như thế này.
Mong diễn đàn của mình ngày càng phát triển mạnh mẽ
 
Cho em hỏi tách tên như này tách thế nào
2682 4R0-5B =1, R0-5C =6, R0-5D =5, P1-02 =2
31196 1R2A2 =3, R2-2B =1, R2-2BM =1, R2-2C =1,
R2-2E =1, R1-2 =3, R4 =2, R1-2C =1, P1-02 =1
41196 1R1-2CM =2, R1-2 =6, R4 =2, R4-1 =2, R2A2 =1
51196 1R2A2 =4, R2-2 =4, R2-2B =2, R2-2C =1,
R2-2CM =1, R4B3 =1, R4B3M =1
 
Cho em hỏi tách tên như này tách thế nào


TTRộngLineNhịpTên cấu kiện
11196 1R2-2 =13, P1 =1
2682 4R0-5B =1, R0-5C =6, R0-5D =5, P1-02 =2
31196 1R2A2 =3, R2-2B =1, R2-2BM =1, R2-2C =1,
R2-2E =1, R1-2 =3, R4 =2, R1-2C =1, P1-02 =1
41196 1R1-2CM =2, R1-2 =6, R4 =2, R4-1 =2, R2A2 =1
51196 1R2A2 =4, R2-2 =4, R2-2B =2, R2-2C =1,
R2-2CM =1, R4B3 =1, R4B3M =1
 
docuong_hn đã viết:
Cho em hỏi tách tên như này tách thế nào


TTRộngLineNhịpTên cấu kiện
111961R2-2 =13, P1 =1
26824R0-5B =1, R0-5C =6, R0-5D =5, P1-02 =2
311961R2A2 =3, R2-2B =1, R2-2BM =1, R2-2C =1,
R2-2E =1, R1-2 =3, R4 =2, R1-2C =1, P1-02 =1
411961R1-2CM =2, R1-2 =6, R4 =2, R4-1 =2, R2A2 =1
511961R2A2 =4, R2-2 =4, R2-2B =2, R2-2C =1,
R2-2CM =1, R4B3 =1, R4B3M =1
Nhấp chuột vào đây để mở rộng...
Mà bạn muốn tách cái gì?
 
cho em hỏi chút giờ em muốn làm như sau: em muốn ngày tháng tự nhảy thì làm thế nào
86Kết quả thí nghiệm nén BT 28 của ngày đúc mẫu 20/12/2015m50Nén BT mác 500Lấy mẫu ngày 20/12/201516/1/2016 Sàn Rntb=57,5 Mpa;
87Kết quả thí nghiệm nén BT 28 của ngày đúc mẫu 21/12/2015m51Nén BT mác 500Lấy mẫu ngày 21`/12/201517/01/2016 Sàn Rntb=58,9 Mpa; Dầm Rntb= 59,7Mpa
88Kết quả thí nghiệm nén BT 28 của ngày đúc mẫu 22/12/2015m53Nén BT mác 500Lấy mẫu ngày 22/12/201518/1/2016 Sàn Rntb=58,7 Mpa; Dầm Rntb= 58,1Mpa
89Kết quả thí nghiệm nén BT 28 của ngày đúc mẫu 23/12/2015m53Nén BT mác 500Lấy mẫu ngày 23/12/201519/1/2016 Sàn Rntb=59,1 Mpa; Dầm Rntb= 61,9Mpa
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

hongquan93vn
Tìm công thức có thể đánh STT của giá trị mỗi lần xuất hiện
2
Trả lời
21
Đọc
142
SA_DQ
SA_DQ
maytinhvp01
  • Question
Chuyên đề giải đáp những thắc mắc về code VBA
    • Thích
151 152 153
Trả lời
3K
Đọc
275K
SA_DQ
SA_DQ
Justine Miller
em cần sự trợ giúp
Trả lời
1
Đọc
23
CHAOQUAY
C
8
  • Question
Xin giúp: Nhóm (group data) các dòng trong bảng tính
Trả lời
11
Đọc
403
forcebk
F
hung2412
  • Question
Có Code VBA nào để tự động điều chỉnh tỷ lệ hiển thị màn hình theo thông số Recommended (Theo từng Laptop, PC)?
Trả lời
5
Đọc
122
befaint
befaint
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom