Các phương pháp tách họ và tên đề nghị post ở đây

befaint · 28/3/17

anhchauvina đã viết:
mình dùng hàm như bạn tedaynui

Nhưng trong ô tên ví dụ có chữ HT hoặc (HT) nằm cuối cùng thì mình muốn loại bỏ các chữ đó và lấy từ cuối cùng trước chữ HT hoặc (HT) thì sửa công thức thế nào ạ?

Ví dụ ô có chứa:
aa bb cc HT - muốn lấy chữ cc
aa bb cc dd (HT) - muốn lấy chữ dd

Loại bỏ mấy ký tự màu đỏ đi trước khi áp dụng công thức đó...

Mã:

=[COLOR=#0000ff]TRIM(SUBSTITUTE(SUBSTITUTE(A1&" "," HT ","")," (HT) ",""))[/COLOR]

------
Thay đoạn màu xanh vào [A1] trong công thức =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

anhchauvina · 29/3/17

befaint đã viết:
Loại bỏ mấy ký tự màu đỏ đi trước khi áp dụng công thức đó...

Mã:

=[COLOR=#0000ff]TRIM(SUBSTITUTE(SUBSTITUTE(A1&" "," HT ","")," (HT) ",""))[/COLOR]

------
Thay đoạn màu xanh vào [A1] trong công thức =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

thank bạn nhiều, mình hỏi thêm nữa:

Mình muốn giữ lại chữ HT với (HT) thì làm sao:

Ví dụ ô có chứa:
aa bb cc HT (muốn lấy chữ cc HT)
aa bb cc dd (HT) (muốn lấy chữ dd (HT))

thobeo · 5/4/17

Không biết có ai hỏi chưa, nhưng cũng khá nhiều trang rồi mình lội không được.
Bác nào cho mình hỏi mình muốn lấy user của người đó,
ví dụ: Nguyễn Hồng Sơn thành sonnh
thì mình có thể dùng công thức gì ko, thanks các bác.

anhchauvina · 14/4/17

mình dùng hàm như bạn tedaynui

Giả sử ô A1 có chứa "Họ Chữ lót Tên"
Lấy Họ :
=LEFT(A4,FIND(" ",A4,1))

Lấy Họ và Chữ lót :
=LEFT(A4,FIND("*",SUBSTITUTE(A4," ","*",LEN(A4)-LEN(SUBSTITUTE(A4," ",""))))-1)

Lấy Tên :
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Nhưng trong ô tên ví dụ có chữ HT hoặc (HT) nằm cuối cùng thì mình muốn lấy chữ 2 chữ sau cùng thì sửa công thức thế nào ạ?

Ví dụ ô có chứa:
aa bb cc HT (muốn lấy chữ cc HT)
aa bb cc dd (HT) (muốn lấy chữ dd (HT))

TRIỆU QUẢNG HÀ · 3/5/17

Hôm trước em thấy cao nhân nào đó chỉ Text column hình như cũng được đó mấy anh, chị ạ

heodyboss · 17/5/17

Em có file này cần các Thầy và Anh chị giúp đỡ !
File excel của em có 2 sheet (Sheet Data + Sheet Cocktail)
Sheet Data:
Chứa các dữ liệu để Sheet Cocktail có thể dò tìm và trả về kết quả
Sheet Cocktail
Chưa dữ liệu để so sánh và dò tìm từ Sheet Data, giá trị trả về là Cột "PATH"
vd:
* CỘT "MÃ HÀNG"
DRCK0083AL01
† DRCK0083 = MÃ SẢN PHẨM
† A,B,C = CÙNG MÃ SẢN PHẨM NHƯNG PHÁT SINH THÊM
† S,M,L,XL,XXL = SIZE SẢN PHẨM
† 01,03,138 = MÃ MÀU SP
Em muốn hỏi là làm cách nào để dò tìm giá trị trên Sheet DATA và trả về giá trị Cột Path bên Sheet Coocktail
--------------------------------------
Em cám ơn

Mão Thảo · 5/6/17

vungoc đã viết:
Tách họ và tên

ĐÂY LÀ CÁCH TÔI ĐÃ HỌC ĐƯỢC TRÊN DIỄN ĐÀN VÀ ĐANG THỰC HIỆN, XIN CHIA SẺ CÙNG BẠN:

Từ file EXCEL có chứa dữ liệu bạn đang muốn tách họ và tên - bạn hãy nhấn Alt + F11 sau đó nhấn insert nhấn module dán đọan mã sau vào trong cửa sổ module:

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

SỬ DỤNG HÀM TÍNH TÁCH HỌ VÀ TÊN:
Giả sử bạn muốn tách tên ra khỏi họ và họ đệm tại ô A1 = Phạm Xuân Trường / Gõ = tachten(A1,0) / Kết quả: Phạm Xuân
Giả sử bạn muốn tách tên ra khỏi họ và họ đệm tại ô A1 = Phạm Xuân Trường / Gõ = tachten(A1,1) / Kết quả: Trường

(Nếu muốn sử dụng hàm này cho tòan excel bạn hãy đọc thêm trên diễn đàn này)

Chúc bạn thành công !

Quá hay. Cái này áp dụng thành coongluoon. em cảm ơn anh

sangotunhien · 15/6/17

Mình thấy chuyển lên Google Sheet tách rất nhanh nhé, rồi down lại về file excel nếu bạn chỉ cần tách chứ k làm 1 cách có hệ thống

Mr. Duck · 16/6/17

Chủ đề tách họ và tên có vẻ rất được nhiều người quan tâm. Có rất nhiều cách giải quyết như dùng CT tách chuỗi, dùng VBA, dùng mẹo "find and replace". Nếu để tham khảo học hỏi cách giải quyết vấn đề trên diễn đàn thì rất ok. Tuy nhiên, đối với những ai chỉ quan tâm đến kết quả thì mong rằng ad chốt lại cách giải quyết tốt nhất và link để các bạn tải về dùng luôn là được.

thienbui · 1/11/17

phamduylong đã viết:
Hàm vả thủ tục tách họ tên

Họ tên thường nhập chung 1 cột cho nhanh. Sau đó ta có thể tách riêng tên ra 1 cột. Nhưng có lúc chúng ra cần tách ra 2 cột: cột họ, cột tên. Đây là 2 làm làm công việc đó:
'======================
Function TachHo(hoten As String) As String
hoten = Trim(hoten)
If hoten = "" Then
TachHo = ""
Else
vt = InStrRev(hoten, " ", Len(hoten))
If vt = 0 Then
TachHo = ""
Else
TachHo = Trim(Mid(hoten, 1, vt))
End If
End If
End Function

'====================
Function TachTen(hoten As String) As String
hoten = Trim(hoten)
If hoten = "" Then
TachTen = ""
Else
vt = InStrRev(hoten, " ", Len(hoten))
If vt = 0 Then
TachTen = hoten
Else
TachTen = Mid(hoten, vt + 1)
End If
End If
End Function

'===================
Bạn sử dụng hàm này bình thường như các hàm khác của Excel, nhưng nếu bạn cần tách họ tên thành 2 cột riêng biệt thì bạn phải thực hiện một loạt các thao tác sau:
1. tách họ, tách tên trên 2 cột phụ
2. chèn thêm cột bên trái cột họ tên
3. Copy 2 cột họ tên mới tách và dán bằng Paste Special - Value trở về nơi cũ.
Để làm nhanh các công việc đó, bạn có thể sử dụng thủ tuc TachHoTen.
Sử dụng thủ tục này như sau:
1. Chọn tất cả các ô chứa họ tên, kể cả ô chứa tiêu đề trên đầu.
2. Chạy Sub TachHoTen.
VBA sẽ làm thay cho bạn các việc còn lại. Lưu ý bạn là số cột trong vùng chọn phải là 1, nếu lớn hơn 1 VBA không thực hiện.

'====================
Sub TachHoTen()
rd = Selection.Row
sr = Selection.Rows.Count
rc = rd + sr - 1
c = Selection.Column
sc = Selection.Columns.Count
If sc > 1 Then
MsgBox "Ban chon " & sc & " cot. Ban phai chon lai 1 cot", vbOKOnly, "Thong bao"
Exit Sub
End If
Range(Cells(rd, c), Cells(rc, c)).Insert Shift:=xlToRight
Range(Cells(rd, c), Cells(rc, c)).Insert Shift:=xlToRight
For r = rd To rc
Cells(r, c) = TachHo(Cells(r, c + 2))
Cells(r, c + 1) = TachTen(Cells(r, c + 2))
Next
Range(Cells(rd, c + 2), Cells(rc, c + 2)).Delete Shift:=xlToLeft
End Sub
'==============
Bạn có thể tham khảo trong tập tin TachHoTen.zip

Cám ơn bạn nhiều lắm.

lengoctoan8x · 3/5/18

Nhờ các anh chị viết công thức ở ô A2 khi công thức của A1 là =OR(B$1=TRUE,B2=TRUE,B3=TRUE), thì ô A2 sẽ là công thức gì để A2 chỉ còn là =OR(B2=TRUE,B3=TRUE)

lengoctoan8x · 3/5/18

lengoctoan8x đã viết:
Nhờ các anh chị viết công thức ở ô A2 khi công thức của A1 là =OR(B$1=TRUE,B2=TRUE,B3=TRUE), thì ô A2 sẽ là công thức gì để A2 chỉ còn là =OR(B2=TRUE,B3=TRUE)

Không dùng đến Find and Replace ạ

iloveit · 3/5/18

Từ Excel 2013 việc tách họ tên đã đơn giản hơn rất nhiều

Mộng Thường 2001 · 3/5/18

lengoctoan8x đã viết:
Nhờ các anh chị viết công thức ở ô A2 khi công thức của A1 là =OR(B$1=TRUE,B2=TRUE,B3=TRUE), thì ô A2 sẽ là công thức gì để A2 chỉ còn là =OR(B2=TRUE,B3=TRUE)

A1=B1+B2+B3>0
A2=B2+B3>0
hihi ^o^

lengoctoan8x · 3/5/18

Mộng Thường 2001 đã viết:
A1=B1+B2+B3>0
A2=B2+B3>0
hihi ^o^

Khó hiểu thế bác. File của e đây

Mộng Thường 2001 · 3/5/18

lengoctoan8x đã viết:
Khó hiểu thế bác. File của e đây

Tại ô B5 gõ công thức:

Mã:

=B3+C3+D3>0

Rồi fill qua phải.
hihi ^o^

Phạm Vũ Tấn · 5/5/18

phamduylong đã viết:
Hàm vả thủ tục tách họ tên

Họ tên thường nhập chung 1 cột cho nhanh. Sau đó ta có thể tách riêng tên ra 1 cột. Nhưng có lúc chúng ra cần tách ra 2 cột: cột họ, cột tên. Đây là 2 làm làm công việc đó:
'======================
Function TachHo(hoten As String) As String
hoten = Trim(hoten)
If hoten = "" Then
TachHo = ""
Else
vt = InStrRev(hoten, " ", Len(hoten))
If vt = 0 Then
TachHo = ""
Else
TachHo = Trim(Mid(hoten, 1, vt))
End If
End If
End Function

'====================
Function TachTen(hoten As String) As String
hoten = Trim(hoten)
If hoten = "" Then
TachTen = ""
Else
vt = InStrRev(hoten, " ", Len(hoten))
If vt = 0 Then
TachTen = hoten
Else
TachTen = Mid(hoten, vt + 1)
End If
End If
End Function

'===================
Bạn sử dụng hàm này bình thường như các hàm khác của Excel, nhưng nếu bạn cần tách họ tên thành 2 cột riêng biệt thì bạn phải thực hiện một loạt các thao tác sau:
1. tách họ, tách tên trên 2 cột phụ
2. chèn thêm cột bên trái cột họ tên
3. Copy 2 cột họ tên mới tách và dán bằng Paste Special - Value trở về nơi cũ.
Để làm nhanh các công việc đó, bạn có thể sử dụng thủ tuc TachHoTen.
Sử dụng thủ tục này như sau:
1. Chọn tất cả các ô chứa họ tên, kể cả ô chứa tiêu đề trên đầu.
2. Chạy Sub TachHoTen.
VBA sẽ làm thay cho bạn các việc còn lại. Lưu ý bạn là số cột trong vùng chọn phải là 1, nếu lớn hơn 1 VBA không thực hiện.

'====================
Sub TachHoTen()
rd = Selection.Row
sr = Selection.Rows.Count
rc = rd + sr - 1
c = Selection.Column
sc = Selection.Columns.Count
If sc > 1 Then
MsgBox "Ban chon " & sc & " cot. Ban phai chon lai 1 cot", vbOKOnly, "Thong bao"
Exit Sub
End If
Range(Cells(rd, c), Cells(rc, c)).Insert Shift:=xlToRight
Range(Cells(rd, c), Cells(rc, c)).Insert Shift:=xlToRight
For r = rd To rc
Cells(r, c) = TachHo(Cells(r, c + 2))
Cells(r, c + 1) = TachTen(Cells(r, c + 2))
Next
Range(Cells(rd, c + 2), Cells(rc, c + 2)).Delete Shift:=xlToLeft
End Sub
'==============
Bạn có thể tham khảo trong tập tin TachHoTen.zip

Dùng VBA rất tiện lợi và khả năng tùy biến cao. Tuy nhiên có không ít người chưa biết đến VBA. Vậy nên mình tham khảo cách dùng hàm thông thường đủ chức năng như hàm tự tạo của bạn. Bạn xem tham khảo và góp ý nhé!
Mình chèn công thức vào file của bạn (cột chữ màu đỏ)

befaint · 5/5/18

Phạm Vũ Tấn đã viết:
Dùng VBA rất tiện lợi và khả năng tùy biến cao. Tuy nhiên có không ít người chưa biết đến VBA. Vậy nên mình tham khảo cách dùng hàm thông thường đủ chức năng như hàm tự tạo của bạn. Bạn xem tham khảo và góp ý nhé!
Mình chèn công thức vào file của bạn (cột chữ màu đỏ)

Có nhất nhiết trích dẫn bài của bác ấy không nhỉ?

Công thức ở chủ đề này có vô vàn rồi, ngắn gọn lắm rồi... sao mình còn làm dài vậy?
Ví dụ tách lấy tên:

Mã:

D4=TRIM(RIGHT(SUBSTITUTE(TRIM(B4)," ",REPT(" ",50)),30))

Còn họ và tên đệm:

Mã:

C4=LEFT(TRIM(B4),LEN(TRIM(B4))-LEN(D4)-1)
'Hoặc:
C4=LEFT(TRIM(B4),LEN(TRIM(B4))-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(B4)," ",REPT(" ",50)),30)))-1)

Mình cùi bắp thì như thế. Còn nếu dùng Excel 2013 trở lên thì Flash Fill rẹt rẹt 10 giây xong hết, không cần nghĩ ngợi gì.

Phạm Vũ Tấn · 5/5/18

Nguyễn Duy Tuân đã viết:
Ai không biết VBA (lập trình) thì làm theo tôi như sau:

Giả sử A1 chứa "Họ và Tên".

Lấy Họ:
=LEFT(A1,LEN(A1)-FIND(" ",MID(A1,LEN(A1),1)&MID(A1,LEN(A1)-1,1)&MID(A1,LEN(A1)-2,1)&MID(A1,LEN(A1)-3,1)&MID(A1,LEN(A1)-4,1)&MID(A1,LEN(A1)-5,1)&MID(A1,LEN(A1)-6,1)&MID(A1,LEN(A1)-7,1)))

Lấy Tên:
=RIGHT(A1,FIND(" ",MID(A1,LEN(A1),1)&MID(A1,LEN(A1)-1,1)&MID(A1,LEN(A1)-2,1)&MID(A1,LEN(A1)-3,1)&MID(A1,LEN(A1)-4,1)&MID(A1,LEN(A1)-5,1)&MID(A1,LEN(A1)-6,1)&MID(A1,LEN(A1)-7,1))-1)

Như anh "Đỗ Phi Đencasstro" (trên mạng) hoặc những cái tên dài trên 7 ký tự thì hàm này lại không dùng được rồi

Phạm Vũ Tấn · 5/5/18

befaint đã viết:
Có nhất nhiết trích dẫn bài của bác ấy không nhỉ?
View attachment 194954

Công thức ở chủ đề này có vô vàn rồi, ngắn gọn lắm rồi... sao mình còn làm dài vậy?
Ví dụ tách lấy tên:

Mã:

D4=TRIM(RIGHT(SUBSTITUTE(TRIM(B4)," ",REPT(" ",50)),30))

Còn họ và tên đệm:

Mã:

C4=LEFT(TRIM(B4),LEN(TRIM(B4))-LEN(D4)-1) 'Hoặc: C4=LEFT(TRIM(B4),LEN(TRIM(B4))-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(B4)," ",REPT(" ",50)),30)))-1)

Mình cùi bắp thì như thế. Còn nếu dùng Excel 2013 trở lên thì Flash Fill rẹt rẹt 10 giây xong hết, không cần nghĩ ngợi gì.

Ôi hay quá! Cảm ơn bạn nhiều nhé!

Các phương pháp tách họ và tên đề nghị post ở đây

|||||||||||||

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên hoạt động

Thành viên mới

File đính kèm

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên thường trực

Thành viên tiêu biểu

Thành viên mới

File đính kèm

Thành viên tiêu biểu

File đính kèm

Thành viên mới

File đính kèm

|||||||||||||

Thành viên mới

Thành viên mới

Facebook

Group