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

Thảo luận trong 'Ứng dụng Excel' bắt đầu bởi handung107, 12 Tháng năm 2005.

7,451 lượt xem

  1. handung107

    handung107 Thành viên thân thiết

    Bài viết:
    576
    Đã được thích:
    13
    Nơi ở:
    VN
    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
     
    #1
  2. handung107

    handung107 Thành viên thân thiết

    Bài viết:
    576
    Đã được thích:
    13
    Nơi ở:
    VN
    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
     
    #2
  3. NgânGiang

    NgânGiang Cố lên! Cố lên...

    Bài viết:
    202
    Đã được thích:
    0
    Nơi ở:
    Trong Mây mù
    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
     
    #3
  4. NgânGiang

    NgânGiang Cố lên! Cố lên...

    Bài viết:
    202
    Đã được thích:
    0
    Nơi ở:
    Trong Mây mù
    Ú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
     
    #4
  5. IChiNoVa

    IChiNoVa Phu cà phê

    Bài viết:
    134
    Đã được thích:
    4
    Nơi ở:
    Sòn Gài
    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.
     
    #5
  6. NgânGiang

    NgânGiang Cố lên! Cố lên...

    Bài viết:
    202
    Đã được thích:
    0
    Nơi ở:
    Trong Mây mù
    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à.
     
    #6
  7. Bình_OverAC

    Bình_OverAC Over Abnormal / Crazy

    Bài viết:
    845
    Đã được thích:
    7
    Nơi ở:
    Nha Trang
    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
     
    #7
  8. NgânGiang

    NgânGiang Cố lên! Cố lên...

    Bài viết:
    202
    Đã được thích:
    0
    Nơi ở:
    Trong Mây mù
    Cảm ơn OverAC
    Mình sẽ thử làm theo cách của bạn.
     
    #8
  9. ForestC

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

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    E'rywhere
    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 97VBA Office 97 chưa có hàm InStr.
     
    #9
  10. ketoan153

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

    Bài viết:
    34
    Đã được thích:
    0
    Nơi ở:
    Danang - Vietnam
    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.
     
    #10
  11. ForestC

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

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    E'rywhere
    Mà em copy function vào đâu thế? Hình như em thiếu 1 thao tác Insert/Module.
     
    #11
  12. Bình_OverAC

    Bình_OverAC Over Abnormal / Crazy

    Bài viết:
    845
    Đã được thích:
    7
    Nơi ở:
    Nha Trang
    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.
     
    #12
  13. ketoan153

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

    Bài viết:
    34
    Đã được thích:
    0
    Nơi ở:
    Danang - Vietnam
    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é!)
     
    #13
  14. vutienduc

    vutienduc Thành viên sơ cấp

    Bài viết:
    5
    Đã được thích:
    0
    Nơi ở:
    Ha Noi
    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)," ",""))))).
     
    #14

Chia sẻ trang này