Tách lấy số trong Lý trình trong nhiều trường hợp

2 Responses

1. hands says:

   01/02/2023 at 11:36 am

   Một cách khác: Tự tạo hàm người dùng (UDF)
   =KM(Ô chứa Text,n)
   Với tham số n=1: Km bắt đầu; n=2: Km kết thúc
   Click chuột phải tại tên sheet, chọn Insert/Module rồi dán code này vào:

   Option Explicit
   Function KM(cellText As Range, n As Integer) As String
   Dim val As String, st1 As String, st2 As String
   Dim p1a&, p1b&, p2a&, p2b&
   val = cellText.Value & " " ' cell chua text
   p1a = InStr(1, val, "Km ") ' vi tri cua "Km" dau tien
   If p1a = 0 Then ' neu khong co "Km" thi tra ve o trong
   KM = ""
   Exit Function
   End If
   p1b = InStr(InStr(p1a + 1, val, "+") + 1, val, " ") ' vi tri ky tu trang sau "Km"
   st1 = Replace(Mid(val, p1a, p1b – p1a), ")", "") ' tach chuoi sau "Km"
   p2a = InStr(p1b, val, "Km ") ' ' vi tri cua "Km" thu 2
   p2b = InStr(InStr(p2a + 1, val, "+") + 1, val, " ") ' vi tri ky tu trang sau "Km" thu 2
   If p2a > 0 Then
   st2 = Replace(Mid(val, p2a, p2b – p2a), ")", "") ' tach chuoi sau "Km" thu 2
   KM = IIf(n = 1, st1, st2) ' neu doi so =1 thi lay chuoi 1, 2 thì lay chuoi 2
   Else
   KM = st1 ' neu chi có 1 "Km" thi coi no la Km bat dau
   End If
   End Function

   Tại ô C28, muốn chuyển lấy Km bắt đầu tại ô A9:
   =KM(A9,1)
   TẠi ô C32:
   =KM(A9,2)

   Tại sheet Testing, mình copy tất cả các dòng text và test công thức và tất cả OK.
   Save as file dưới dạng .xlsm nhé.

   @bebo021999 : Em chờ công thức và code của bác cho vấn đề của em ở #1 mãi từ sau chủ đề "Tạo dãy số ngẫu nhiên không trùng lặp"!.
   Cảm ơn bác. Nhưng sau khi chạy code ra kết quả là có chữ Km 0+…. hoặc Km 1+…. mà em chỉ cần kết quả là các con số như bài #1 em đã nói. để có thể tính toán được.

   Sorry quên mất vụ này, thêm 1 chút nhé
   Option Explicit
   Function KM(cellText As Range, n As Integer)
   Dim val As String, st1 As String, st2 As String
   Dim p1a&, p1b&, p2a&, p2b&
   val = Replace(cellText.Value & " ", "Km ", "Km0") ' cell chua text
   p1a = InStr(1, val, "Km0") + 2 ' vi tri cua "Km" dau tien
   If p1a = 0 Then ' neu khong co "Km" thi tra ve o trong
   KM = ""
   Exit Function
   End If
   p1b = InStr(InStr(p1a + 1, val, "+") + 1, val, " ") ' vi tri ky tu trang sau "Km"
   st1 = Replace(Mid(val, p1a, p1b – p1a), ")", "") ' tach chuoi sau "Km"
   p2a = InStr(p1b, val, "Km0") + 2 ' ' vi tri cua "Km" thu 2
   p2b = InStr(InStr(p2a + 1, val, "+") + 1, val, " ") ' vi tri ky tu trang sau "Km" thu 2
   If p2a > 0 Then
   st2 = Replace(Mid(val, p2a, p2b – p2a), ")", "") ' tach chuoi sau "Km" thu 2
   KM = IIf(n = 1, st1, st2) ' neu doi so =1 thi lay chuoi 1, 2 thì lay chuoi 2
   Else
   KM = st1 ' neu chi có 1 "Km" thi coi no la Km bat dau
   End If
   If Left(KM, 2) = "0+" Then
   KM = Replace(KM, ",", "")
   Else
   KM = Replace(KM, ",", ".")
   End If
   KM = CDbl(Split(KM, "+")(0) + Split(KM, "+")(1))
   End Function

   Ra kết quả là 64926 ở C28 sheet "Cao do – Ho mong" chứ không phải là 649,26 bác a.

   Mình ra 649.26. Nếu muốn 649,26 thì chỉnh phân cách thập phân thôi.

   Sau khi định dạng số là Number thì ra kết quả như hình dưới a.
   Cell C28

   2856

   ps: Có lẽ trên máy tính của bác hiển thị được là 649,26.
   Chứ trên máy tính em thì khi nhấn F2 tại cell C28 rồi Enter thì ra kết quả là 64926

   Lạ nhỉ.
   Bạn post lại file bị sai xem nào
   File mình vẫn đúng.

   @bebo021999 : Em up file như em đã nói ở #16

   ===============================
   * Khai code đầu xuân 2023
   Với bài này tôi viết hàm tự tạo sử dụng Regular expression để nhìn code gọn hơn:

   Public Function Tachlytrinh(s, i)
       Dim str$, sMatch As IMatchCollection2
           str = Trim(CStr(s))
   On Error Resume Next
       With New RegExp
           .Global = True
           .IgnoreCase = True
           .Pattern = "kms*d++(d+.*d+)"
           If .Test(str) Then
               Set sMatch = .Execute(str)
               Tachlytrinh = sMatch.Item(i - 1).SubMatches(0)
           End If
       End With
   End Function

   hàm trong excel sẽ là
   = Tachlytrinh("ô dữ liệu chứa số liệu lý trình cần lấy", "vị trí cần lấy (là 1 hoặc 2)" )
   ví dụ :Tachlytrinh(A1,1)

   ***************
   Chúc mừng năm mới vạn sự như ý

   @hungpecc1 : Tôi thử code thì trên máy tính của tôi báo lỗi như hình dưới
   2853

   Bạn vào Tool – References tìm và tích vào ô …. Regular Expression…
   2854

   Ra kết quả là các số chẵn bạn à. Ví dụ là 649 chứ không phải đúng cần là 649,26.

   thử dùng dòng code dưới (hiệu chỉnh chỗ tô đỏ) xem kết quả ntn:
   hoặc nếu không kết hợp thêm hàm round, hoặc hàm int lấy phần nguyên,….

   Public Function Tachlytrinh(s, i)
       Dim str$, sMatch As IMatchCollection2
           str = Trim(CStr(s))
   On Error Resume Next
       With New RegExp
           .Global = True
           .IgnoreCase = True
           .Pattern = "kms*d++(d+).*d+"
           If .Test(str) Then
               Set sMatch = .Execute(str)
               Tachlytrinh = sMatch.Item(i - 1).SubMatches(0)
           End If
       End With
   End Function

   Với code trên thì ra kết quả chỉ còn số 64. Chứ không là 649,26

   Lỗi này là do nguyên nhân người nhập liệu hoặc định dạng máy của bạn về ký hiệu phân tách phần thập phân cụ thể là dấu chấm (.) hoặc dấu phẩy (,)
   có thể Hiệu chỉnh lại code đề phù hợp hơn như sau:

   Public Function Tachlytrinh(s, i)
       Dim str$, sMatch As IMatchCollection2
           str = Trim(CStr(s))
   On Error Resume Next
       With New RegExp
           .Global = True
           .IgnoreCase = True
           [SIZE=5][COLOR=rgb(226, 80, 65)][B].Pattern = "kms*d++(d+)(.*|,*)d*"[/B][/COLOR][/SIZE]
           If .Test(str) Then
               Set sMatch = .Execute(str)
               Tachlytrinh = sMatch.Item(i - 1).SubMatches(0)
           End If
       End With
   End Function

   @hungpecc1 : Dấu phân cách phần thập phân trên máy tính tôi đang là dấu chấm.
   Thử code bạn vừa sửa ở #18 vẫn ra kết quả là 649

   Reply
2. hands says:

   01/02/2023 at 11:40 am

   Bạn thử dùng công thức này
   C28=TEXT(AGGREGATE(14,6,0+LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A9,"Km",REPT(" ",255),1),255)),"+",REPT(" ",255),1),255)),",",""),ROW($1:$20)),1),"###,##.00"),
   C32=> thay chỗ màu đó số 1 thành số 2.

   @hocexcel_1991 : Dấu , nào trong công thức của bạn đúng là dấu phảy phân cách phần nguyên và số lẻ nhỉ?
   Để tôi đổi các dấu , còn lại thành dấu ;
   Vì trên máy tính tôi đang sử dụng dấu ngăn cách giữa các phần trong công thức là dấu ; chứ không phải dấu ,

   Bạn giữ nguyên 2 dấu phẩy (,) theo hình, còn lại đổi thành dấu chấm phấy (;)

   @hocexcel_1991 Sau khi thay công thức thì báo lỗi

   Bạn thử lưu lại thành .xlsx, xong mở lại xem sao.

   Reply