Cách tách chuỗi có điều kiện trong list cho trước

nai_con · 25/2/14

Thân nhờ Anh/Chị giúp dùm:
Mình có 1 list như sau:

hoa072013qbt
hoanhai14q1
hoacuc09122013q2
layon02022014q1
ly051112014q1
Các Anh/Chị chỉ dùm mình có công thức nào lấy tên hoa và khi đến số thì dừng lại:
Kết quả:

hoa
hoanhai
hoacuc
layon
ly.
Xin cảm ơn Anh/Chị nhiều.

hungpecc1 · 25/2/14

nai_con đã viết:
Thân nhờ Anh/Chị giúp dùm:
Mình có 1 list như sau:

hoa072013qbt
hoanhai14q1
hoacuc09122013q2
layon02022014q1
ly051112014q1
Các Anh/Chị chỉ dùm mình có công thức nào lấy tên hoa và khi đến số thì dừng lại:
Kết quả:

hoa
hoanhai
hoacuc
layon
ly.
Xin cảm ơn Anh/Chị nhiều.

giả sử ô A1 chứa 1 chuỗi nào đó
công thức mảng cho ô A2 có thể như sau :

PHP:

=LEFT(A1,MIN(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)),ROW(INDIRECT("1:" & LEN(A1))),""))-1)

kết thúc lệnh = cltr +shift +enter

nai_con · 25/2/14

Cảm ơn Hùng đã trợ giúp xin cho hỏi nếu trong list trên ví dụ hàng A1 thay bằng hoabanglang và không có số trong chuỗi đó thì công thức tổng quát cho A1 - Ạ có cần thay đổi gì không?.

ndu96081631 · 25/2/14

hungpecc1 đã viết:
giả sử ô A1 chứa 1 chuỗi nào đó
công thức mảng cho ô A2 có thể như sau :

PHP:

=LEFT(A1,MIN(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)),ROW(INDIRECT("1:" & LEN(A1))),""))-1)

kết thúc lệnh = cltr +shift +enter

Mình nghĩ chỉ vầy là được chứ nhỉ:

Mã:

=LEFT(A1,MIN(IFERROR(FIND(ROW($1:$10)-1,A1),10^10))-1)

hungpecc1 · 25/2/14

ndu96081631 đã viết:
Mình nghĩ chỉ vầy là được chứ nhỉ:

Mã:

=LEFT(A1,MIN(IFERROR(FIND(ROW($1:$10)-1,A1),10^10))-1)

công thức nhẹ hẳn ^^
p/s : anh "già" rồi mà vẫn "tinh" gứm

be nam · 25/2/14

ndu96081631 đã viết:
Mình nghĩ chỉ vầy là được chứ nhỉ:

Mã:

=LEFT(A1,MIN(IFERROR(FIND(ROW($1:$10)-1,A1),10^10))-1)

Công thức của anh bị lỗi ở A2 ạ. Em sử dụng công thức mảng như sau:

PHP:

=IFERROR(LEFT(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW($1:$30),1)),0)-1),A1)

ndu96081631 · 26/2/14

Bé Nấm đã viết:
Công thức của anh bị lỗi ở A2 ạ.

Lỗi thế nào vậy đồng chí?

nai_con · 26/2/14

Em xin cảm ơn Thầy Cô, Anh/Chị đã giúp đỡ.
Tuy nhiên hàng xuất kho trong tháng của Em có khi lên tới 10000 dòng áp dụng công thức của " hungpecc1 thì rất nặng. Còn của sư phụ "ndu96081631"thì lại chưa đúng với yêu cầu ở A2. cụ thể kết quả cần "hoanhai" chứ không phải "hoanhai14q1".
Nhân tiện đây Em xin đưa ra điều kiền tách: ( Vì chuỗi này có quy luật luôn là chữ cái đứng trước)
Tách phần chữ trong chuỗi từ ký tự 1 đến lúc gặp số tự nhiên thì dừng lại.
Công thức càng tối ưu càng tốt nhé Thầy Cô, Anh/Chị.
Cảm ơn các Thầy Cô, Anh Chị giúp đỡ.

siwtom · 26/2/14

nai_con đã viết:
Em xin cảm ơn Thầy Cô, Anh/Chị đã giúp đỡ.
Tuy nhiên hàng xuất kho trong tháng của Em có khi lên tới 10000 dòng áp dụng công thức của "hungpecc1 thì rất nặng. Còn của sư phụ "ndu96081631"thì lại chưa đúng với yêu cầu ở A2. cụ thể kết quả cần "hoanhai" chứ không phải "hoanhai14q1".
.

Bạn thấy chưa đúng do bạn không "kết thúc lệnh = cltr +shift +enter"
Người ta không nói thì bạn cũng phải tự biệt chứ?

nmhung49 · 26/2/14

siwtom đã viết:
Bạn thấy chưa đúng do bạn không "kết thúc lệnh = cltr +shift +enter"
Người ta không nói thì bạn cũng phải tự biệt chứ?

Công thức này không ngắn hơn các công thức trên như có thể enter bình thường nhờ vào hàm Index, kiểm tra với dữ liệu bạn thì chưa phát sinh lỗi

=LEFT(A1,MATCH(TRUE,INDEX(ISNUMBER(-MID(A1,ROW($1:$99),1)),0),0)-1)

be nam · 26/2/14

ndu96081631 đã viết:
Lỗi thế nào vậy đồng chí?

Xin lỗi anh ạ, em quên Ctrl-Shift-Enter nên bị lỗi ở ô A2 !$@!!

nai_con · 26/2/14

Anh Chị nào pro VBA viết cho Em code cho khoảng 15000 dòng cũng với yêu cầu trên với. Chứ bỏ công thức vào excel rùng mình lên xuống trông đến sợ.
Em cảm ơn trước. Nhân tiện xin lỗi sư phụ "ndu96081631", vì Em chưa xem xét kỹ.

siwtom · 26/2/14

nai_con đã viết:
Anh Chị nào pro VBA viết cho Em code cho khoảng 15000 dòng cũng với yêu cầu trên với. Chứ bỏ công thức vào excel rùng mình lên xuống trông đến sợ.
Em cảm ơn trước. Nhân tiện xin lỗi sư phụ "ndu96081631", vì Em chưa xem xét kỹ.

Bạn không thạo VBA nên tôi viết theo cách dễ hiểu nhất, chỉ cần học vài buổi "khóa VBA cơ bản". Cũng có thể viết theo những cách khác.

1. Đặt 1 Button trên sheet và gán cho nó macro Tach_ten_hoa

2. Sub Tach_ten_hoa

Mã:

Sub [COLOR=#4b0082]Tach_ten_hoa[/COLOR]()
Dim Arr(), r As Long, c As Long, text As String
    Arr = Range([[COLOR=#0000ff][B]A2[/B][/COLOR]], [[B][COLOR=#0000ff]A[/COLOR][/B]65535].End(xlUp)).Value
    For r = 1 To UBound(Arr, 1)
        text = Arr(r, 1)
        For c = 1 To Len(text)
            If IsNumeric(Mid(text, c, 1)) Then Exit For
        Next
        If c <= Len(text) Then
            Arr(r, 1) = Mid(text, 1, c - 1)
        [B][COLOR=#ff0000]Else
            Arr(r, 1) = ""[/COLOR][/B]
        End If
    Next
    [[B][COLOR=#0000ff]A2[/COLOR][/B]].Offset(0, 1).Resize(UBound(Arr), 1).Value = Arr
End Sub

3. Do bạn tách từ đầu chuỗi cho tới chữ số đầu tiên nên tôi cho là khi chuỗi không có chữ số nào thì kết quả là chuỗi rỗng. Nếu bạn cho là khi đó thì lấy toàn bộ chuỗi thì bạn xóa trong code trên 2 dòng đỏ đỏ

4. Chỗ xanh xanh có ý nghĩa là: dữ liệu trong cột A, và ô đầu tiên là ô A2. Nếu dữ liệu của bạn bố trí khác thì sửa chỗ xanh xanh. Vd.dữ liệu bắt đầu từ C5 thì sửa thành [C5], [C65536], và [C5].Offset

5. Kết quả trả về ở cột bên cạnh.

nmhung49 · 26/2/14

Dựa vào code của Chú Tom mình sửa lại thành Function bạn có thể tham khảo

[GPECODE=vb]
Function TachTenHoa(ByVal txt As Range) As String
Dim r As Long
For r = 1 To Len(txt)
If IsNumeric(Mid(txt, r, 1)) Then Exit For
Next
If r <= Len(txt) Then
TachTenHoa = Left(txt, r - 1)
Else
TachTenHoa = ""
End If
End Function
[/GPECODE]

Cú pháp TachTenHoa(địa chỉ ô muốn tách) ví dụ TachTenHoa(A1)

ndu96081631 · 27/2/14

nmhung49 đã viết:
Dựa vào code của Chú Tom mình sửa lại thành Function bạn có thể tham khảo

[GPECODE=vb]
Function TachTenHoa(ByVal txt As Range) As String
Dim r As Long
For r = 1 To Len(txt)
If IsNumeric(Mid(txt, r, 1)) Then Exit For
Next
If r <= Len(txt) Then
TachTenHoa = Left(txt, r - 1)
Else
TachTenHoa = ""
End If
End Function
[/GPECODE]

Cú pháp TachTenHoa(địa chỉ ô muốn tách) ví dụ TachTenHoa(A1)

Người ta nói rằng:

nai_con đã viết:
Anh Chị nào pro VBA viết cho Em code cho khoảng 15000 dòng cũng với yêu cầu trên với. Chứ bỏ công thức vào excel rùng mình lên xuống trông đến sợ.

Giờ bạn lại viết code thành 1 Function, tức cũng phải gõ xuống sheet thì cũng bằng không? Hổng lẽ bạn nghĩ rằng hàm tự tạo sẽ ít... rùng mình hơn hàm của anh Bill?

dhamma · 28/2/14

Chào mọi người:
Tôi là thành viên mới tham gia diễn đàn GPE. Hôm nay, tôi mới được xem đề tài này và xin được đóng góp cho bài viết.

Tôi xin gửi đáp án cho yêu cầu tách chuỗi này theo 2 cách:
1. Giải theo công thức thông thường (standard formula)
2. Giải theo công thức mảng (array formula)

Hy vọng bài giải này sẽ "dễ hiểu và dễ tiếp nhận" hơn đối với "những người mới bắt đầu".

Vui lòng tải bài giải tại đây.

Đối cách 1 (standard formula) thì rất rõ ràng và dễ hiểu.
Đối với cách 2:
- nếu dữ liệu đặt ở cột A, bắt đầu từ A2 thì
- công thức tại ô B2 là: =LEFT(A2,MIN(IF(ISNUMBER(SEARCH({0,1,2,3,4,5,6,7,8,9},A2)),SEARCH({0,1,2,3,4,5,6,7,8,9},A2),""))-1)
(chỉ cần Enter thôi, không cần nhấn Ctrl+Shift+Enter)
- Copy công thức xuống cho các ô B3:B...

nai_con · 7/3/14

Xin các su phụ chỉ giáo thêm một vấn đề nữa:
Mình có 1 dữ liệu như vậy:
hoalayon: 06052014qbt
hoanhai: 12122013q1
hoadongtien: 2013q3
hoalan: 07quanbinhthanh
hoa: 302quan5
Cũng vẫn là tách chuỗi với điều kiện chuổi được tách từ sau dấu ":" và dừng lại khi gặp chữ.
Ví dụ như dữ liệu trên kết quả:
06052014
12122013
2013
07
302
Mong Anh/Chị cùng các thầy cô chỉ giúp.
Chân thành mong đợi.

vodoi2x · 7/3/14

siwtom đã viết:
Bạn không thạo VBA nên tôi viết theo cách dễ hiểu nhất, chỉ cần học vài buổi "khóa VBA cơ bản". Cũng có thể viết theo những cách khác.

1. Đặt 1 Button trên sheet và gán cho nó macro Tach_ten_hoa

2. Sub Tach_ten_hoa

Mã:

Sub [COLOR=#4b0082]Tach_ten_hoa[/COLOR]() Dim Arr(), r As Long, c As Long, text As String Arr = Range([[COLOR=#0000ff][B]A2[/B][/COLOR]], [[B][COLOR=#0000ff]A[/COLOR][/B]65535].End(xlUp)).Value For r = 1 To UBound(Arr, 1) text = Arr(r, 1) For c = 1 To Len(text) If IsNumeric(Mid(text, c, 1)) Then Exit For Next If c <= Len(text) Then Arr(r, 1) = Mid(text, 1, c - 1) [B][COLOR=#ff0000]Else Arr(r, 1) = ""[/COLOR][/B] End If Next [[B][COLOR=#0000ff]A2[/COLOR][/B]].Offset(0, 1).Resize(UBound(Arr), 1).Value = Arr End Sub

3. Do bạn tách từ đầu chuỗi cho tới chữ số đầu tiên nên tôi cho là khi chuỗi không có chữ số nào thì kết quả là chuỗi rỗng. Nếu bạn cho là khi đó thì lấy toàn bộ chuỗi thì bạn xóa trong code trên 2 dòng đỏ đỏ

4. Chỗ xanh xanh có ý nghĩa là: dữ liệu trong cột A, và ô đầu tiên là ô A2. Nếu dữ liệu của bạn bố trí khác thì sửa chỗ xanh xanh. Vd.dữ liệu bắt đầu từ C5 thì sửa thành [C5], [C65536], và [C5].Offset

5. Kết quả trả về ở cột bên cạnh.

Vẫn chú ý như các điều bác Siwtom nói trên, chỉ thay code như sau để giải quyết vấn đề mới của bạn

Mã:

Sub Tach_so()
    Dim Arr(), r As Long, d As Long, c As Long, LL As Long, text As String
    Arr = Range([[COLOR="#0000FF"]A[/COLOR]2], [[COLOR="#0000FF"]A[/COLOR]65535].End(xlUp)).Value
    For r = 1 To UBound(Arr, 1)
        text = Arr(r, 1)
        LL = Len(text)
        For d = 1 To LL
            If Mid(text, d, 1) = ":" Then Exit For
        Next
       [COLOR="#FF0000"] Arr(r, 1) = ""[/COLOR]
        d = d + 2
        If d <= LL Then
            For c = d To LL
                If Not IsNumeric(Mid(text, c, 1)) Then Exit For
            Next
            c = c - 1
            If c <= LL Then Arr(r, 1) = "'" & Mid(text, d, c - d + 1)
        End If
    Next
    [A2].Offset(0, 1).Resize(UBound(Arr), 1).Value = Arr
End Sub

nai_con · 8/3/14

Cảm on vodoi2x đã giúp đõ. Mình rất cảm ơn về sự hồi âm của bạn. Tuy nhiên nếu có công thức áp dụng cho trường hợp này thì giúp mình với. Vì với trường hợp này nếu dùng công thức mình vẫn chưa nghĩ được cách tối ưu nhất để áp dụng.
Trân trọng!

vodoi2x · 8/3/14

nai_con đã viết:
Cảm on vodoi2x đã giúp đõ. Mình rất cảm ơn về sự hồi âm của bạn. Tuy nhiên nếu có công thức áp dụng cho trường hợp này thì giúp mình với. Vì với trường hợp này nếu dùng công thức mình vẫn chưa nghĩ được cách tối ưu nhất để áp dụng.
Trân trọng!

công thức thì có, nhưng nếu áp dụng cho nhiều dòng (>500) thì là mệt mỏi đó, vì là công thức phức tạp

thích thế, thì công thức đây: giả định A1 là ô chứa chuỗi dữ liệu, thì ô kết quả có công thức:

PHP:

=MID(A1,FIND(":",A1)+2,MATCH(FALSE,INDEX(ISNUMBER(1*MID(TRIM(MID(A1,FIND(":",A1)+2,255)),ROW($1:$255),1)),0),0)-1)

Cách tách chuỗi có điều kiện trong list cho trước

Thành viên chính thức

Thành viên gắn bó

Thành viên chính thức

Huyền thoại GPE

Thành viên gắn bó

Thành viên chính thức

Huyền thoại GPE

Thành viên chính thức

Thành viên gắn bó

Thành viên tích cực

Thành viên chính thức

Thành viên chính thức

Thành viên gắn bó

Thành viên tích cực

Huyền thoại GPE

Thành viên mới

Thành viên chính thức

Nothing & Nothing

Thành viên chính thức

Nothing & Nothing

Đếm ngược thời gian