Marty có một trang tính với một chuỗi dài các phần trong cột A.

Chúng bao gồm các chữ cái và số, chẳng hạn như A123BC, AB123C, v.v. Marty muốn chia dữ liệu thành ba cột để văn bản trước các số sẽ nằm trong một cột, số trong cột thứ hai và văn bản sau các số trong ngày thứ ba.

Yếu tố làm phức tạp việc chia số bộ phận thành các phân đoạn là không có độ dài đặt cho mỗi thành phần của số bộ phận kết hợp. Nếu các thành phần có độ dài tiêu chuẩn, thì bạn có thể sử dụng hàm Văn bản thành Cột trong Excel. Vì chúng không có và không có dấu phân cách giữa các thành phần, nên lối đi tiềm năng cho giải pháp đó bị đóng.

Nếu bạn muốn sử dụng công thức để tách các số bộ phận, thì bạn sẽ cần ba trong số chúng, một cho mỗi thành phần bạn muốn trích xuất.

Giả sử rằng số bộ phận tuân theo mẫu được chỉ ra (văn bản, chữ số, văn bản) và số bộ phận đầu tiên nằm trong ô A1, bạn có thể sử dụng như sau trong ô B1:

=LEFT(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0)-1)

Điều này cần được nhập dưới dạng công thức mảng, có nghĩa là bạn cần nhập nó bằng Ctrl + Shift + Enter. Công thức tìm chữ số đầu tiên trong phần số, sau đó trả về mọi thứ trước chữ số đó.

Nó sẽ hoạt động trên bất kỳ số phần nào có độ dài không quá 100 ký tự.

Để trích xuất thành phần thứ hai của số bộ phận, bạn có thể đặt công thức sau vào ô C1:

=MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0),COUNT(1MID(A1,ROW(INDIRECT("1:100")),1)))

Một lần nữa, đây là một công thức đơn lẻ và nó cần được nhập dưới dạng công thức mảng (Ctrl + Shift + Enter) để có thể hoạt động trên từng ký tự trong số phần ban đầu. Nó kiểm tra số bộ phận và xác định điểm đầu của các chữ số, sau đó trích xuất tất cả các chữ số đó. Nó trả về một chuỗi văn bản, mặc dù chuỗi đó bao gồm các chữ số. Nếu bạn muốn nó thực sự được coi là một số (tất nhiên sẽ loại bỏ mọi số 0 ở đầu), thì bạn cần phải đặt toàn bộ công thức trong một hàm Giá trị, như được hiển thị ở đây:

=VALUE(MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0),COUNT(1MID(A1,ROW(INDIRECT("1:100")),1))))

Để lấy thành phần cuối cùng của số bộ phận, bạn cần sử dụng công thức sau, một lần nữa được nhập dưới dạng công thức mảng:

=MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0)+COUNT(1MID(A1,ROW(INDIRECT("1:100")),1)),100)

Mặc dù cách tiếp cận này hoạt động rất tốt, nhưng công thức mảng nổi tiếng là tính toán chuyên sâu, đặc biệt là khi bạn có rất nhiều công thức trong trang tính của mình. Nếu bạn cần tách ra một phần nghìn con số, điều đó có nghĩa là bạn kết thúc với 3.000 công thức mảng, điều này có thể rất rất rất chậm trong việc tính toán lại.

Nếu bạn thấy mình trong tình huống này, bạn có thể muốn sử dụng macro để thực sự tách các số bộ phận ra. Macro sau đây sẽ hoạt động trên các số bộ phận tuân theo mẫu văn bản, chữ số, văn bản, như đã được mô tả.

Sub Split1()

Dim C As Range     Dim sNew As New     Dim i As Integer          For Each C In Selection         sNew = ""

i = 1

' Get first part, which is text         Do While IsNumeric(Mid(C, i, 1)) = False             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 1).Value = sNew

' Pull next part, which should be digits         sNew = ""

Do While IsNumeric(Mid(C, i, 1)) = True             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 2).Value = sNew

' Use rest of original cell         sNew = Mid(C, i, Len(C))

C.Offset(0, 3).Value = sNew     Next C End Sub

Để sử dụng macro, chỉ cần chọn một số bộ phận và chạy nó. Macro sử dụng khái niệm tìm kiếm các thay đổi giữa các giá trị số / không phải số trong chuỗi ký tự trong ô. Khi đạt đến một trong những ranh giới này, phần của chuỗi ban đầu trước ranh giới sẽ được nhồi vào một ô mới. Khái niệm này có thể được rút ngắn một chút, như được thực hiện trong ví dụ sau.

Sub Split2()

Dim C As Range     Dim j As Integer     Dim k As Integer

For Each C In Selection         j = 1         Do While Not (IsNumeric(Mid(C.Value, j, 1))) And j <= Len(C)

j = j + 1         Loop         k = j

Do While IsNumeric(Mid(C.Value, k, 1)) And k <= Len(C)

k = k + 1         Loop

C.Offset(0, 1) = Left(C, j - 1)

C.Offset(0, 2) = Mid(C, j, k - j)

C.Offset(0, 3) = Mid(C, k, Len(C) - (k - 1))

Next C End Sub

Tất nhiên, sự khác biệt giữa phiên bản macro này và phiên bản trước đó là phiên bản này đi qua ô ban đầu và xác định ranh giới cùng một lúc. Khi chúng được biết, sau đó các thành phần của số phần ban đầu được nhồi vào các ô.

Một cách tiếp cận thú vị để tách các số bộ phận ra là sử dụng một vài hàm ngắn do người dùng xác định để xác định đâu là ranh giới giữa các bộ phận. Hãy xem xét hai chức năng sau:

Function pNumber(X)

i = 1     Do Until Mid(X, i, 1) Like "#": i = i + 1: Loop     pNumber = i End Function
Function pAlpha(X)

X = UCase(X)

i = pNumber(X)

Do Until Mid(X, i, 1) Like "[A-Z]": i = i + 1: Loop     pAlpha = i End Function

Chúng ngắn hơn nhiều so với các macro trước đó và tất cả những gì chúng làm là trả về ranh giới nơi các số bắt đầu (trong trường hợp pNumber) và ranh giới nơi nhóm văn bản thứ hai bắt đầu (trong trường hợp pAlpha). Để sử dụng các hàm, bạn sử dụng ba công thức sau để trả về lần lượt các thành phần thứ nhất, thứ hai và thứ ba của số phần ban đầu:

=MID(A1,1,pNumber(A1)-1)

=MID(A1,pNumber(A1),pAlpha(A1)-pNumber(A1))

=MID(A1,pAlpha(A1),LEN(A1)-pAlpha(A1)+1)

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (3141) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: