Làm việc với các hàm Text

[handung107]

Thật ra, đây chỉ là bài tổng hợp lại các vấn đề nằm rải rác trên diễn đàn từ lâu, nhưng đối với các bạn mới tham gia EFC, các bạn sẽ khó tìm thấy để tham khảo, nên tôi đúc kết lại, các bạn hãy theo dõi để tránh việc đưa ra thảo luận lại những vấn đề đã cũ và đã được thảo luận rất sôi nổi, cặn kẽ, kỹ lưỡng rồi

Vấn đề : Tách tên ra khỏi cột họ, tên

1/ Công thức của bạn anhoanh :

=IF(MID(RIGHT(A8,2),1,1)=" ",PROPER(RIGHT(A8,1)),IF(MID(RIGHT(A8,3),1,1)=" ",PROPER(RIGHT(A8,2)),IF(MID(RIGHT(A8,4),1,1)=" ",PROPER(RIGHT(A8,3)),IF(MID(RIGHT(A8,5),1,1)=" ",PROPER(RIGHT(A8,4)),IF(MID(RIGHT(A8,6),1,1)=" ",PROPER(RIGHT(A8,5)),IF(MID(RIGHT(A8,7),1,1)=" ",PROPER(RIGHT(A8,6)),0))))))

Với công thức trên có những tên không tách được và nhất là những tên dài hơn 7 ký tự

Những tên không tách được : (VNI-Times)
LEÂ THÒ PHÖÔÏNG
ÑAËNG VAÊN THÖÔÛNG
NGOÂ VAÊN CHUYEÅN
NGUYEÃN NGOÏC TRUYEÀN

2/Bạn quocnghia đề nghị công thức sau :

Công thức này cho phép bạn cắt được tên đến 10 ký tự (Nói chung các tên VN thì OK hết), nếu nhiều hơn hoặc rảnh thì các bạn tự thêm vào chổ in đậm 11,12... 1triệu cũng được.

=RIGHT($B1,FIND(" ",CONCATENATE(LEFT(RIGHT($B1,1),1),LEFT(RIGHT($B1,2 ),1),LEFT(RIGHT($B1,3),1),LEFT(RIGHT($B1,4),1),LEF T(RIGHT($B1,5),1),LEFT(RIGHT($B1,6),1),LEFT(RIGHT( $B1,7),1),LEFT(RIGHT($B1,8),1),LEFT(RIGHT($B1,9),1 ),LEFT(RIGHT($B1,10),1)))-1)
Tuy nhiên, nếu tên mà có thêm dấu space ở cuối thì sẽ không tách được
Vì vậy các bạn nên Trim ô B1 trước khi áp dụng công thức này

Tham khảo thêm tại :
http://www.webketoan.com/forum/showthread.php?t=1495&page=3&pp=10

3/ Bạn 8020 đưa ra một công thức khác :

8020 có một cách khác lập công thức ngắn hơn (kết hợp hàm Find & hàm Substitute để tìm ra vị trí của khoảng trắng cuối cùng) :
Td : Cell A1 chứa họ tên "Công Tằng Tôn Nữ Nguyễn Thị Linh Tinh", nhập công thức sau vào ô B1 để tách tên ra: =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))).

Tham khảo thêm tại :

http://www.webketoan.com/forum/showthread.php?t=1617

Bổ sung thêm :

-Tách họ : Td tại ô C1 :

=LEFT(A1,FIND(" ",A1)-1)

-Tách tên đệm :

Giả sử tên nằm trong B1 và họ nằm trong C1

=IF(LEN(B1&C1)+2>=LEN(A1),"",MID(A1,LEN(C1)+2,LEN(A1)-LEN(B1&C1)-2))

-Tách các danh hiệu "Ông", "Bà", "Cô" ra khỏi họ tên :

=IF(OR(LEFT(A1,3)="Ông",LEFT(A1,2)="Bà",LEFT(A1,2)="Cô"),RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1)

-Tách các danh hiệu "Mr", "Mrs", "Ms" ra khỏi họ tên : Tương tự trên

=IF(OR(LEFT(A1,2)="Mr",LEFT(A1,3)="Mrs",LEFT(A1,2)="Ms"),RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1)

4/Bạn Tuanktcdcn đưa ra một giải pháp khác là viết hàm bằng VBA như sau :

Để đơn giản tôi xin đưa ra đây 2 hàm tự viết để giải quyết mục đích đó.
+Nhấn ALT+F11 (Vào chế độ lập trình)
+Vào Menu Insert\Module
+ Bây giờ bạn copy 2 hàm dưới đây vào Module

'============Chép vào Module
Function FRight(ByVal cTim As String, ByVal Chuoi As String) As Integer
FRight = InStrRev(Trim(Chuoi), cTim)
End Function

Function Tachten(ByVal HovaTen As String) As String
Tachten = Right(Trim(HovaTen), Len(Trim(HovaTen)) - FRight(" ", HovaTen))
End Function
'===========
+Nhấn ALT+F11 lần nữa để về chế độ Worksheet (bảng tính)
+Tại ô A1=Trần Thị Song Ngư
+Tách tên:
c1) Tại ô B1=Techten(A1)
c2) Tại ô B1=RIGHT(A1,LEN(A1)-Fright(" ",A1))
(Hàm Fright() tìm chuỗi cTim trong Chuoi từ phải sang trái, nếu tìm được cho ra giá trị >0. Hàm InStrRev chỉ có từ EXCEL 2000 trở lên mói có.)

Tham khảo thêm tại :

http://www.webketoan.com/forum/showthread.php?t=2833

5/Tận dụng khả năng sẵn có của Excel

Bạn vào data/text to columns/delimiters/space

 

[handung107]

Tách số nhà, tên đường ra khỏi cột địa chỉ

Cách giải của bạn 8020 :

Bài toán của bạn (tách số ra khỏi ô địa chỉ) rất đơn giản, nếu:
- Trong cell chứa địa chỉ đầy đủ luôn luôn nhất quán theo thứ tự: "sốnhà_tênđường", trong đó số chuỗi "sốnhà" không có khoảng trằng nào ở liền trước nó & ở giữa nó; và nó cách chuỗi "tênđường" một hoặc nhiều khoảng trắng.

Trong trường hợp đó chỉ cần tìm ra vị trí của khoảng trắng đầu tiên trong cell địa chỉ và cắt tự vị trí đó trở về trước là xong
Công thức: Td ô D3 chứa địa chỉ đầy đủ, nhập công thức sau vào ô E3 để lấy số nhà: =left(D3,search(" ",D3)). Rồi copy công thức này xuống các ô dưới là xong. Ngoài ra để cắt ra tên đường thì nhập vào ô F3 bên cạnh công thức này: =RIGHT(C3,LEN(D3)-LEN(E3)).

Trong trường hợp không có số nhà trong ô địa chỉ, bạn chỉ việc lập công thức kiểm tra xem ô địa chỉ có bắt đầu bằng một chữ số hay không, nếu có thì dùng công thức trên, còn không thì giữ nguyên toàn bộ ô địa chỉ.
Đây là công thức bạn sẽ nhập trong cell E3 để lấy sốnhà: =IF(ISNUMBER(VALUE(LEFT(D3,1))),LEFT(D3,SEARCH(" ",D3)),0). Còn ô F3 để lấy "tênđường" thì không có gì thay đổi.

Tham khảo thêm tại :

http://www.webketoan.com/forum/showthread.php?t=1532&page=3&pp=10

 

[NgânGiang]

Các bác đã làm từ lâu, nay em mới tham gia nên mới biết.
Phải chi đừng mò nữa. Mong các bác cho bình luận về cách làm của em
' Tach ten
Function Tachten(hovaten)
Dim Ten, Dodai, i
hovaten = Trim(hovaten)
Dodai = Len(hovaten)
If Dodai < 2 Then
Ten = ""
Else
For i = 1 To Dodai - 1
If (Mid(hovaten, (Dodai - i), 1) = " ") = True Then
Ten = Right(hovaten, i)
Exit For
Else
Ten = "Khoâng coù khoaûng troáng giöõa Hoï vaø Teân."
End If
Next i
End If
Tachten = Ten
End Function

 

[NgânGiang]

Úi làm sao chẳng thấy có chỗ nào thụt vào thế này. Đọc khó hiểu quá, nguyên bản của em có thế đâu

 

[IChiNoVa]

Sao bạn không dùng vòng lặp While hoặc Repeat thay cho For?
Nếu dùng For thì nên cho i chạy từ (độ dài -1) đến 1, để chương trình khỏi mất công tính (độ dài -1) trong vòng lặp.

Nếu không có khoảng trắng giữa họ và tên thì chuỗi đó là tên luôn.
Mình nghĩ khỏi phải kiểm tra họ và tên <2, làm chi.

 

[NgânGiang]

Sao bạn không dùng vòng lặp While hoặc Repeat thay cho For?
Nếu dùng For thì nên cho i chạy từ (độ dài -1) đến 1, để chương trình khỏi mất công tính (độ dài -1) trong vòng lặp.

Nếu không có khoảng trắng giữa họ và tên thì chuỗi đó là tên luôn.
Mình nghĩ khỏi phải kiểm tra họ và tên <2, làm chi.

Cảm ơn bạn nhiều lắm!
Mình không có nhiều kinh nghiệm về lập trình.
Nghĩ đến đâu viết đến đó ấy mà.

 

[Bình_OverAC]

Xin lổi chị Dung. Em nghĩ trong topic này không nên nói lung tung ngòai lề nhưng chị để em góp ý với Ngângiang một chút nhé.

To Ngângiang:
Function ten(hovaten)
ten = Right(Trim(hovaten), " " & InStr(1, StrReverse(Trim(hovaten)) & " ", " ") - 1)
End Function

 

[NgânGiang]

Cảm ơn OverAC
Mình sẽ thử làm theo cách của bạn.

 

[ForestC]

Xin lổi chị Dung. Em nghĩ trong topic này không nên nói lung tung ngòai lề nhưng chị để em góp ý với Ngângiang một chút nhé.

To Ngângiang:
Function ten(hovaten)
ten = Right(Trim(hovaten), " " & InStr(1, StrReverse(Trim(hovaten)) & " ", " ") - 1)
End Function

Xin alert thêm: Bạn chỉ nên dùng cách này nếu Version Office của bạn cao hơn Office 97 vì VBA Office 97 chưa có hàm InStr.

 

[ketoan153]

Chào các bác,
mình test funtion của OverAC sao không được nhỉ?Nhờ bác nào chỉ mình với.
mình làm theo các bước sau:
+Alt+F11;
+copy funtion
Function ten(hovaten)
ten = Right(Trim(hovaten), " " & InStr(1, StrReverse(Trim(hovaten)) & " ", " ") - 1)
End Function;
+Trở về Excel A1: quoc viet
+A2: +=ten(a1)
+A2:[HASHTAG]#NAME[/HASHTAG]
+Nhờ các bác giúp với.
Cảm ơn lắm lắm.

 

[ForestC]

Mà em copy function vào đâu thế? Hình như em thiếu 1 thao tác Insert/Module.

 

[Bình_OverAC]

Có lẻ security được để ở mức high hoặc very high.
Vào Tool--> macro --> security để chọn lại Medium hoặc low thì mới sài được.

 

[ketoan153]

Cảm ơn Maika nhiều nghen.
đúng là thiếu insert/module.
Hôm nay em mới đăng ký là thành viên mới của diễn đàn, em giới thiệu về mình với các anh chị luôn nhé : tên là Nguyễn Bùi Quốc Việt - Kế toán viên - thích lập trình, kế toán - 27 tuổi - single. Thanks.
ĐT :0914.000512 ;email: ketoan153@yahoo.com.
Kính mong các anh chị, em giúp đỡ thêm.
Thân chào.
(Hơi private một ít, mong các admin thông cảm nhé!)

 

[vutienduc]

8020 có một cách khác lập công thức (kết hợp hàm Find & hàm Substitute để tìm ra vị trí của khoảng trắng cuối cùng) :
Td : Cell A1 chứa họ tên "Công Tằng Tôn Nữ Nguyễn Thị Linh Tinh", nhập công thức sau vào ô B1 để tách tên ra: =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))).

Cách làm của bạn rất hay nhưng chưa đầy đủ :
Vd: người nhập tên vào là: Lê Bá Tiến. thì công thức trên đúng.
nhưng khi nhập là Lê Bá Tiến . thì công thức sẽ sai.
Do đó tôi xin phép được bổ sung thêm:
=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND("*",SUBSTITUTE(TRIM(A1)," ","*",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))).