PHÂN TÍCH CÔNG THỨC TÁCH TÊN ĐỆM TRONG EXCEL

PHÂN TÍCH CÔNG THỨC
ĐỂ TÁCH TÊN ĐỆM TRONG EXCEL
Để tách Họ, tên đệm và tên thành 3 cột khác nhau mà trước đó chúng ở cùng một chuỗi trong một ô (một cột), chúng ta phải kết hợp nhiều Hàm. Bài trước chúng tôi đã chia sẽ ý nghĩa kết hợp các Hàm để tách Họ, tách Tên, bài này chúng tôi chia sẻ PHÂN TÍCH CÔNG THỨC ĐỂ TÁCH TÊN ĐỆM.
A/MINH HỌA:


I/PHÂN TíCH CÔNG THỨC:
Công thức trên kết hợp Các Hàm TRIM; REPLACE; LEFT; FIND; SUBTITUTE; LEN ( 6 Hàm!!!!)
Hàm nào đứng đầu là Hàm đó chỉ huy! Công thức trên Hàm TRIM chỉ huy. Nhưng ông tướng này bất tài nên ông phó tướng LEFT thực quyền hơn.
1.HÀM TRIM: Loại bỏ các khoảng trắng dư thừa (TRƯỚC, TRONG, SAU) chuỗi văn bản.
Cú pháp: =TRIM(text)
-Ví dụ:



2.HÀM REPLACE: thay thế một phần của chuỗi văn bản (bắt đầu từ vị trí ký tự thứ mấy và bao nhiêu ký tự sau nó) bằng một chuỗi các ký tự mới.
Cú pháp: =REPLACE(old_text,start_num,num_chars,new_text)
Ví dụ:


Ví dụ 1: Tôi thay Mộng thành “Thanh”
+ M là ký tự ở vị trí thứ 10 (kể cả 2 khoảng trắng), tôi chọn 4 ký tự là M,ộ,n,g thành “Thanh”
Ví dụ 2: Tôi thay Mộng thành “” (không có gì cả)
+Ta thấy giữa Thị và Tốt có 2 khoảng trắng.
Ví dụ 3: Tôi muốn bỏ “ Mộng” như vậy khoảng trắng ở vị trí thứ 9, tôi chọn cả khoảng trắng là 5 ký tự. (các bạn so sánh 3 công thức trên sẽ hiểu!)
3.HÀM SUBTITUTE:
CÚ PHÁP: =SUBTITUTE(text,old_text,new_text,[instance_num)


-Ví dụ 1:
+B3 là chuỗi văn bản.
+Tôi muốn thay “Mộng” thành “Thanh”
+Tôi không chọn số vì “Mộng” chỉ có một lần.
-Ví dụ 2:
+Tôi thay “Mộng” thành “Thanh” nghĩa là bao nhiêu chữ “Mộng” cũng đều được thay thành “Thanh”
-Ví dụ 3:
+Tôi thay “Mộng” thành “Thanh”, chọn 1: nghĩa là chỉ thay “Mộng” đầu tiên, còn “Mộng” thứ 2 không thay.(nếu chọn số 2 thì “Mộng” sau thay, Mộng trước giữ nguyên).
-Ví dụ 4: Tôi muốn bỏ “Mộng” ở vị trí đầu luôn nên tôi gõ (“”). Còn “Mộng” sau không bỏ.
-Ví dụ 5: Tôi muốn bỏ “Mộng tốt lành” nên gõ (“”)
Bạn giỏi toán lập luận thì rất dễ hiểu!!!!!!
4.HÀM LEN: Tính độ dài của văn bản có bao nhiêu ký tự kể cả khoảng trắng trước, giữa, sau chuỗi văn bản (được nhấn phím)!!!!!



A.CÁCH KẾT CÁC HÀM:
I.Trở lại công thức tách Tên Đệm:
=TRIM(REPLACE(LEFT(B3,FIND("*",SUBSTITUTE(B3," ","*",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))-1),1,FIND(" ",B3,1)-1,""))
(Các bạn đừng quá chú ý địa chỉ HÀNG CỦA CỘT B ô)
-Như chúng ta biết Hàm TRIM nhằm xóa các khoảng trắng (“ ”). Mà cụ thể trong công thức này là xóa HỌ và xóa TÊN.
II. TA XEM HỌ XÓA TÊN NHƯ THẾ NÀO?
1.Lập luận và minh hoa:
-Dùng Hàm LEFT để xóa Tên (tức là chỉ chọn HỌ và TÊN ĐỆM), thì phải nhờ Hàm FIND tìm xem phải chọn từ ký tự thứ mấy trong chuỗi.


-Chọn ký tự gì? Chọn ký tự “*” THAY CHO khoảng cách TRẮNG (“ ”) giữa TÊN ĐỆM và TÊN phải nhờ Hàm FIND.


-Mà muốn thay thì phải nhờ hàm SUBSTITUTE. Nhưng anh chàng SUBSTITUTE lại không biết chọn “*” nào vì có mấy nàng “*” ??? (Giữa Họ và Tên Đệm- Tên Đệm cũng có thể có 1,2,3…. nàng “*”; giữa Tên Đệm và Tên.


-Lại phải nhờ Hàm LEN tính giúp! Hàm LEN đếm trong chuỗi có tất cả bao nhiêu ký tự kể cả khoảng trắng (“ ”) rồi trừ đi Hàm LEN đếm trong chuỗi bao nhiêu ký tự không tính khoảng trắng (“ ”).


-Lại phải nhờ Hàm SUBSTITUTE thay giúp những khoảng trắng (“ ”) trong chuỗi thành không khoảng trắng (“”).


-Rồi lại trừ đi 1, vì Hàm LEFT chỉ chọn HỌ và TÊN ĐỆM có bao nhiêu ký tự thôi.
=LEFT(B7,FIND("*",SUBSTITUTE(B7