Tìm chữ trong chuỗi

[pomete]

Ví dụ cột A chứa số liệu gồm số và chữ (số trước, chữ sau), ví dụ:
4646AB
12346987CHFE
15GCDW

Có công thức nào tách nguyên phần số và phần chữ ra không. Tôi nhớ là có ai đó đã chỉ ra cách tìm vị trí chữ số trong chuỗi như thế này nhưng tìm lại mãi mà không thấy. Ai giúp giùm với nhé!!!

 

[ptlong04x1]

Bạn xem link này, dùng hàm tự tạo rất tiện lợi :

http://www.giaiphapexcel.com/forum/showpost.php?p=119595&postcount=2

 

[pomete]

Có cách nào dùng công thức không nhỉ. Chỉ cần tìm ra vị trí của chữ trong chuỗi là tách được. Hic, không nhớ trong chủ đề nào nữa.

 

[ndu96081631]

Có cách nào dùng công thức không nhỉ. Chỉ cần tìm ra vị trí của chữ trong chuỗi là tách được. Hic, không nhớ trong chủ đề nào nữa.

Công thức đương nhiên là vẫn có cách, nhưng khá phức tạp!
Cho bạn file này tham khảo (lấy chuổi dùng 2 name và lấy số dùng đến 5 name)
Bài này "dợt" công thức là chính ---> Dù sao vẫn khuyên bạn dùng VBA vì tính đơn giản và tiện dụng của nó (ngay cả tôi chế ra công thức này cũng chẳng thèm xài)

 

[huuthang_bd]

Ví dụ cột A chứa số liệu gồm số và chữ (số trước, chữ sau), ví dụ:
4646AB
12346987CHFE
15GCDW

Có công thức nào tách nguyên phần số và phần chữ ra không. Tôi nhớ là có ai đó đã chỉ ra cách tìm vị trí chữ số trong chuỗi như thế này nhưng tìm lại mãi mà không thấy. Ai giúp giùm với nhé!!!

Nếu dữ liệu của bạn theo quy tắc số trước, chữ sau thì bạn có thể dùng các công thức này để tách ra.
Ví dụ dữ liệu gốc ở cột A, bạn muốn tách số ra cột B và chữ ra cột C thì:

B1=LEFT(A1,SUMPRODUCT(--ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

C1=SUBSTITUTE(A1,B1,"")

 

[rollover79]

Ví dụ cột A chứa số liệu gồm số và chữ (số trước, chữ sau), ví dụ:
4646AB
12346987CHFE
15GCDW

Có công thức nào tách nguyên phần số và phần chữ ra không. Tôi nhớ là có ai đó đã chỉ ra cách tìm vị trí chữ số trong chuỗi như thế này nhưng tìm lại mãi mà không thấy. Ai giúp giùm với nhé!!!

Nếu là số trước chữ sau thì bạn thử dùng công thức này xem sao, đây là công thức mảng, bạn phải kết thúc bằng Ctrl+Shift+Enter

=LEFT(A1,COUNT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

 

[anhtuan1066]

Nói về bài toán tách lấy số và chữ, tôi xin tổng hợp lại với cải tiến mới nhất (2 name cho tách lấy số và 2 name cho tách lấy chữ)
Giả sử dử liệu nằm tại cột A, từ dòng 2 trở xuống... Đầu tiên ta đặt con trỏ chuột tại dòng 2 và tiến hành các bước sau:
1> Tách lấy số: Tạo 2 name

Temp1 =IF(ISNUMBER(1*MID(Sheet1!$A2,ROW(INDIRECT("1:"&LEN(Sheet1!$A2))),1)),ROW(INDIRECT("1:"&LEN(Sheet1!$A2))),"")

GetNum =IF(COUNT(Temp1),SUMPRODUCT(MID(Sheet1!$A2,LARGE(Temp1,ROW(INDIRECT("1:"&COUNT(Temp1)))),1)*(10^(ROW(INDIRECT("1:"&COUNT(Temp1)))-1))),"")

Gõ vào cell B2 công thức = GetNum rồi kéo fill xuống
2> Tách lấy chữ: Tạo 2 name

Temp2 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A2,0,""),1,""),2,""),3,""),4,"")

GetText = =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Temp2,5,""),6,""),7,""),8,""),9,"")

Gõ vào cell C2 công thức = GetText rồi kéo fill xuống
Công thức tổng quát cho mọi kiểu chuổi, số và chữ nằm thế nào cũng được
Lưu ý quan trọng: Công thức trong name chính xác theo vị trí tương đối của ActiveCell (con trỏ chuột), vì thế hãy lưu ý dòng tô màu đỏ ở trên trước khi tạo name

 

[huuthang_bd]

Nói về bài toán tách lấy số và chữ, tôi xin tổng hợp lại với cải tiến mới nhất (2 name cho tách lấy số và 2 name cho tách lấy chữ)
Giả sử dử liệu nằm tại cột A, từ dòng 2 trở xuống... Đầu tiên ta đặt con trỏ chuột tại dòng 2 và tiến hành các bước sau:
1> Tách lấy số: Tạo 2 name

Temp1 =IF(ISNUMBER(1*MID(Sheet1!$A2,ROW(INDIRECT("1:"&LEN(Sheet1!$A2))),1)),ROW(INDIRECT("1:"&LEN(Sheet1!$A2))),"")

GetNum =IF(COUNT(Temp1),SUMPRODUCT(MID(Sheet1!$A2,LARGE(Temp1,ROW(INDIRECT("1:"&COUNT(Temp1)))),1)*(10^(ROW(INDIRECT("1:"&COUNT(Temp1)))-1))),"")

Gõ vào cell B2 công thức = GetNum rồi kéo fill xuống
2> Tách lấy chữ: Tạo 2 name

Temp2 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A2,0,""),1,""),2,""),3,""),4,"")

GetText = =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Temp2,5,""),6,""),7,""),8,""),9,"")

Gõ vào cell C2 công thức = GetText rồi kéo fill xuống
Công thức tổng quát cho mọi kiểu chuổi, số và chữ nằm thế nào cũng được
Lưu ý quan trọng: Công thức trong name chính xác theo vị trí tương đối của ActiveCell (con trỏ chuột), vì thế hãy lưu ý dòng tô màu đỏ ở trên trước khi tạo name

Góp ý một chút. Name GetNum nên chèm thêm hàm Text() để khỏi bị mất những số 0 đứng đầu dãy số:

GetNum=TEXT(IF(COUNT(Temp1),SUMPRODUCT(MID(Sheet1!$A2,LARGE(Temp1,ROW(INDIRECT("1:"&COUNT(Temp1)))),1)*(10^(ROW(INDIRECT("1:"&COUNT(Temp1)))-1))),""),REPT("0",COUNT(Temp1)))

 

[anhtuan1066]

Góp ý một chút. Name GetNum nên chèm thêm hàm Text() để khỏi bị mất những số 0 đứng đầu dãy số:

GetNum=TEXT(IF(COUNT(Temp1),SUMPRODUCT(MID(Sheet1!$A2,LARGE(Temp1,ROW(INDIRECT("1:"&COUNT(Temp1)))),1)*(10^(ROW(INDIRECT("1:"&COUNT(Temp1)))-1))),""),REPT("0",COUNT(Temp1)))

Vâng! Điều này tôi có nghĩ qua... nhưng đã gọi cái tên là GETNUM, tức tách lấy số thì những số 0 đứng trước chẳng có ý nghĩa gì (nên tôi đã không quan tâm đến)
Nói chung là tùy theo yêu cầu sử dụng (các bạn làm bên kế toán chắc có lẽ cần đến số 0 này chăng?)