Giả sử bạn có một trang tính có nhiều mã sản phẩm trong cột A.

Các mã này có định dạng A4, B12, AD4, v.v. Do sự thay đổi trong cách hoạt động của công ty bạn, bạn được hướng dẫn thay đổi tất cả các mã sản phẩm để chúng có dấu gạch ngang giữa các chữ cái và số.

Có một số cách bạn có thể thực hiện tác vụ này. Nếu cấu trúc mã sản phẩm của bạn nhất quán, thì việc chèn các dấu gạch ngang sẽ rất nhanh chóng.

Ví dụ: nếu luôn có một chữ cái duy nhất theo sau là số, thì bạn có thể sử dụng công thức như sau:

=LEFT(A1,1) & "-" & RIGHT(A1,LEN(A1)-1)

Rất có thể dữ liệu của bạn sẽ không có cấu trúc, nghĩa là bạn có thể có một hoặc hai chữ cái theo sau là tối đa ba chữ số. Do đó, cả A4 và QD284 đều sẽ là mã sản phẩm hợp lệ. Trong trường hợp này, một công thức giải pháp cần nhiều sáng tạo hơn một chút.

Một cách để xử lý nó là sử dụng công thức mảng. Hãy xem xét công thức sau:

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

Nếu các giá trị nằm trong A1-A10, bạn có thể đặt công thức này vào B1, sau đó sao chép nó xuống cột. Vì nó là một công thức mảng, nó phải được nhập bằng cách nhấn Ctrl + Shift + Enter. Công thức tìm vị trí của số đầu tiên trong ô và chèn dấu gạch ngang trước nó.

Giả sử, vì lợi ích của ví dụ, ô A1 chứa BR27. Phần trong cùng của công thức, INDIRECT (“1: 100”), chuyển đổi văn bản 1: 100 thành một dải ô. Điều này được sử dụng để việc chèn hoặc xóa hàng không ảnh hưởng đến công thức. Phần tiếp theo của công thức, ROW (INDIRECT (“1: 100”)), về cơ bản tạo ra một mảng các giá trị 1-100: 1,2,3, …​, 99,100. Điều này được sử dụng để tác động lên từng ký tự trong ô.

Phần tiếp theo, MID (A1, ROW (INDIRECT (“1: 100”)), 1), đề cập đến từng ký tự riêng lẻ trong chuỗi. Điều này dẫn đến mảng: “B”, “R”, “2” và “7”. Nhân mảng với 1 (phần tiếp theo của công thức)

dẫn đến mỗi ký tự riêng lẻ được chuyển đổi thành một số. Nếu ký tự không phải là số, chuyển đổi này sẽ dẫn đến lỗi. Trong trường hợp chuỗi được chuyển đổi (BR27), kết quả là: #VALUE, #VALUE, 2 và 7.

Bước tiếp theo là áp dụng hàm ISERROR cho kết quả của phép nhân. Điều này chuyển đổi các lỗi thành TRUE và các lỗi không thành FALSE, tạo ra TRUE, TRUE, FALSE và FALSE. Hàm MATCH tìm kiếm trong mảng các giá trị TRUE và FALSE để có kết quả khớp chính xác FALSE. Trong ví dụ này, hàm MATCH trả về số 3, vì giá trị FALSE đầu tiên nằm ở vị trí thứ ba của mảng. Tại thời điểm này, về cơ bản chúng ta đã biết vị trí của số đầu tiên trong ô.

Hàm cuối cùng là REPLACE, được sử dụng để thực sự chèn dấu gạch ngang vào chuỗi nguồn, bắt đầu từ ký tự thứ ba.

Như bạn có thể nói, công thức để thực hiện phép biến đổi có thể hơi khó giải mã. Đối với những người có khuynh hướng như vậy, có thể dễ dàng hơn chỉ cần tạo một chức năng do người dùng xác định. Macro sau đây là ví dụ về một macro sẽ trả về một chuỗi có dấu gạch ngang ở vị trí thích hợp:

Function DashIn(myText As String)

Dim i As Integer     Dim myCharCode As Integer     Dim myLength As Integer

Application.Volatile     myLength = Len(myText)

For i = 1 To myLength         myCharCode = Asc(Mid(myText, i, 1))

If myCharCode >= 48 And myCharCode <= 57 Then             Exit For         End If     Next i     If i = 1 Or i > myLength Then         DashIn = myText     Else         DashIn = Left(myText, i - 1) & "-" _           & Mid(myText, i, myLength - 1)

End If End Function

Macro kiểm tra từng ký tự trong chuỗi gốc và khi tìm thấy ký tự số đầu tiên, nó sẽ chèn một dấu gạch ngang tại điểm đó. Bạn sẽ sử dụng hàm theo cách này:

=DashIn(A1)

_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 (2613) á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: