Bạn sẽ không phải là người đầu tiên có dữ liệu được nhập hoặc quản lý kém. Một ví dụ như vậy là văn bản và số trong cùng một ô. Tất cả những hàng và cột đó có ích lợi gì? . g. số lượng, giá cả, kích thước, số kiểu, tuổi. Vì mục đích tổ chức, rõ ràng và khá quan trọng để sắp xếp dữ liệu, sẽ phù hợp để tách văn bản và số thành các cột riêng biệt. Bằng cách đó, bạn có thể sắp xếp dữ liệu dễ dàng theo văn bản hoặc số
Hướng dẫn hôm nay của chúng tôi sẽ cung cấp cho bạn các phương pháp chi tiết về cách tách các số khỏi văn bản bằng cách sử dụng các tính năng, công thức và VBA của Văn bản thành Cột và Flash Fill. Chúng tôi đã giải thích cách hoạt động của từng phương pháp và cách bạn có thể tùy chỉnh từng phương pháp phù hợp với dữ liệu của mình
Hãy tách ra
Mục lục
Sử dụng tính năng chuyển văn bản thành cột
Văn bản thành Cột là một công cụ rất hữu ích có thể chia văn bản từ một ô thành nhiều ô theo thông số kỹ thuật mà bạn cung cấp trong trình hướng dẫn Văn bản thành Cột. Điều này hữu ích cho chúng tôi vì chúng tôi có thể sử dụng Văn bản thành Cột để tách tên và mã nhân viên trong dữ liệu mẫu của mình thành các cột riêng biệt. Trong dữ liệu mẫu của chúng tôi, tên và mã được phân tách bằng dấu gạch ngang dưới dạng dấu phân cách, vì vậy chúng tôi sẽ chỉ định rằng văn bản được phân tách thành văn bản trước và sau dấu phân cách. Dưới đây là các bước để tách một chuỗi văn bản thành hai cột bằng tính năng Chuyển văn bản thành cột
- Chọn các ô chứa văn bản và số
- Chuyển đến tab Dữ liệu và chọn nút Văn bản thành Cột trong Công cụ Dữ liệu
- Chọn nút radio Được phân tách từ hai tùy chọn và sau đó nhấn nút lệnh Tiếp theo
- Chọn hộp kiểm Khác và nhập dấu gạch nối "-" vào trường được cung cấp. Nhấn lại nút lệnh Tiếp theo
- Đích đến sẽ tự động là ô được chọn đầu tiên. Nếu bạn muốn ghi đè lên cột đầu tiên, chỉ cần nhấp vào nút Kết thúc. Chúng tôi muốn dữ liệu của mình được chia thành hai cột mới, vì vậy chúng tôi sẽ đặt đích là ô đầu tiên của cột tiếp theo
- Đặt đích của hai cột mới bằng cách nhập tham chiếu ô của ô đầu tiên của đích trong Destination
- Chúng tôi muốn dữ liệu của mình bắt đầu từ cột bên cạnh cột được chọn để đích cho ví dụ của chúng tôi sẽ là "$C$3"
- Khi hoàn tất, nhấn nút lệnh Kết thúc
- Nhấp vào nút OK trên lời nhắc xuất hiện
Cột đã chọn sẽ được chia thành văn bản và số đến đích đã đặt, bỏ qua dấu phân cách
Sử dụng tính năng Flash Fill
Đây có lẽ sẽ là phương pháp nhanh nhất, dễ dàng nhất và được tùy chỉnh nhất để tách số khỏi văn bản. Flash Fill tự động điền các giá trị vào một cột theo một hoặc hai ví dụ do bạn cung cấp theo cách thủ công. Cái hay của tính năng này là bạn không cần phải truy cập bất kỳ hộp thoại hoặc cài đặt nào hoặc thậm chí không nhớ bất kỳ công thức nào
Ví dụ chúng tôi có, tính năng này đặc biệt hữu ích vì chúng tôi có thể chỉ cần trích xuất tên và mã mà không bao gồm bất kỳ khoảng trắng ở đầu hoặc cuối hoặc thậm chí bất kỳ dấu phân cách nào. Vì chúng tôi đang điền vào hai cột, Flash Fill sẽ phải được áp dụng hai lần cho hai cột khác nhau. Dưới đây là các bước để tách số khỏi văn bản bằng Flash Fill
- Trong một cột mới bên cạnh cột có dữ liệu, hãy nhập phần đầu tiên của văn bản bạn muốn tách và nhấn phím Enter. Trong ví dụ của chúng tôi, chúng tôi sẽ nhập tên nhân viên
- Chọn tính năng Flash Fill từ nút Fill trong Editing của tab Home Ngoài ra, bạn có thể sử dụng phím tắt cho Flash Fill là Ctrl + E
- Cột đầu tiên sẽ flash-fill chỉ với tên
- Bây giờ hãy làm tương tự cho cột tiếp theo và điền thủ công vào ô đầu tiên của cột mới bằng phần thứ hai của văn bản. Chúng tôi sẽ điền mã nhân viên
- Sử dụng tính năng Flash Fill từ menu ribbon hoặc bằng cách nhấn Ctrl + E
- Các số cũng sẽ được điền vào nhờ Flash Fill
Sử dụng Công thức dựa trên Hàm TRÁI, PHẢI & TÌM KIẾM
Ngay lập tức, các công thức chúng tôi đang sử dụng được điều chỉnh theo ví dụ trường hợp của chúng tôi trong đó văn bản và số được phân tách bằng dấu cách, dấu gạch nối và một dấu cách khác. Nếu văn bản và số trong tập dữ liệu của bạn được phân tách bằng dấu phân cách không có ký tự khoảng trắng, bạn sẽ phải điều chỉnh các công thức được sử dụng bên dưới [thay đổi "-2" trong công thức đầu tiên thành "-1" và loại bỏ "-1" . Ngoài ra, dấu phân cách của chúng tôi là dấu gạch nối và chúng tôi đã sử dụng dấu gạch nối trong các công thức. Thay thế dấu gạch ngang trong công thức bằng dấu phân cách trong dữ liệu của bạn. Ngoài ra, bạn có thể sử dụng tính năng Tìm và Thay thế để thay đổi các dấu phân cách của mình
Một con trỏ khác, nếu văn bản hoặc số trong dữ liệu của bạn là một số ký tự cố định, bạn không cần sử dụng các công thức chi tiết bên dưới
Đối với một số ký tự cố định trong văn bản bắt đầu, bạn có thể sử dụng công thức LEFT với số ký tự e. g
=LEFT[B3,5]
Đối với một số ký tự cố định trong phần thứ hai của văn bản, như ví dụ trường hợp của chúng tôi, bạn có thể sử dụng hàm RIGHT e. g
=RIGHT[B3,5]
Nhưng nếu chúng ta thay đổi một chút trong dữ liệu của mình, với một số cách đánh số bất thường, thì các công thức sẽ phải thay đổi đáng kể. Chúng tôi đã thay đổi một vài số trong ví dụ của mình để bạn biết cách các công thức áp dụng cho số lượng ký tự khác nhau trong các số
Hãy chuyển sang các công thức. Chúng tôi đang sử dụng 2 công thức khác nhau để trích xuất từng công thức;
Công thức đầu tiên chúng tôi có để tách tên nhân viên khỏi cột B
=LEFT[B3,SEARCH["-",B3]-2]
Chúng tôi đang sử dụng công thức này để tìm tất cả văn bản trong ô B3 bắt đầu từ bên trái và cho đến dấu phân cách "-" trừ 2 ký tự. Hàm TÌM KIẾM xác định vị trí của dấu phân cách trong chuỗi văn bản ở B3. Hàm LEFT trả về văn bản từ đầu chuỗi văn bản trở lên, số lượng ký tự được trích xuất được xác định bởi vị trí của dấu phân cách bằng hàm SEARCH
Nếu công thức kết thúc ở đây, kết quả sẽ là "Adrian Adamson -". Phần "-2" của công thức rất hữu ích ở đây, hai ký tự trả về ít hơn 2 ký tự để ký tự khoảng trắng và dấu phân cách không biến nó thành kết quả cuối cùng của hàm. Do đó, chúng ta có "Adrian Adamson" là kết quả
Đối với phần số của văn bản, chúng tôi lấy công thức sau
=RIGHT[B3,LEN[B3]-SEARCH["-",B3]-1]
Chức năng TÌM KIẾM đã được sử dụng chính xác như trước đây, để xác định vị trí của dấu phân cách. Hàm RIGHT trả về các ký tự từ cuối chuỗi văn bản trong B3. Hàm RIGHT phải trả về bao nhiêu ký tự được xác định bởi hàm LEN và SEARCH
Hàm LEN trả về tổng số ký tự trong chuỗi văn bản i. e. 22 và hàm TÌM KIẾM xác định vị trí của dấu phân cách i. e. 16. LEN trừ TÌM KIẾM trở thành 22-16=6. Số lượng ký tự được trả về từ B3 sẽ là 6, bao gồm khoảng trắng ở đầu. Để tránh các khoảng trắng ở đầu, chúng tôi đã thêm "-1" vào công thức, sau đó sẽ trả về 5 ký tự cuối cùng là "93114"
Sử dụng VBA
VBA [Visual Basic for Applications] là một công cụ lập trình ngôn ngữ cho các ứng dụng Office. Với VBA, chúng tôi có thể tự động hóa các tác vụ ứng dụng Office của mình, điều này đặc biệt hữu ích cho các tập dữ liệu lớn. Để tách số khỏi văn bản trong Excel, chúng tôi sẽ viết mã trong trang tính của mình bằng VBA, mã này sẽ đóng vai trò là hàm do người dùng xác định để trả về các giá trị số từ chuỗi văn bản. Và sau đó chúng ta sẽ sử dụng hàm ngược lại để trả về tất cả văn bản không phải là giá trị số
Vì vậy, điều này có ý nghĩa gì đối với các dấu phân cách? . Mặc dù bạn có thể sử dụng các kết quả bao gồm các dấu phân cách và xóa chúng sau, nhưng để dễ áp dụng, chúng tôi sẽ sử dụng Tìm và Thay thế để chuẩn bị sẵn dữ liệu cho VBA và xóa các dấu phân cách. Hãy nhanh chóng thay thế các dấu phân cách
Chọn cột có dữ liệu và nhấn tổ hợp phím Ctrl + H. Thay thế dấu phân cách [" – " trong trường hợp của chúng tôi] bằng một ký tự khoảng trắng
Chọn nút Replace All và đây là dữ liệu của chúng ta sẽ trông như thế nào bây giờ
Bây giờ hãy xem các bước về cách VBA hoạt động để tách các số khỏi văn bản
- Nhấn tổ hợp phím Alt + F11 để mở VB Nếu bạn đã bật tab Nhà phát triển, bạn có thể mở trình soạn thảo VB từ đó. Đây là trình soạn thảo VB
- Để khởi chạy cửa sổ Mô-đun, hãy mở menu Chèn và chọn Mô-đun
- Ở đây chúng tôi có cửa sổ Mô-đun để nhập mã của chúng tôi
- Sao chép và dán đoạn mã sau, để tách các số khỏi chuỗi văn bản, vào trường có thể chỉnh sửa trong cửa sổ Mô-đun
Public Function SplitText[pWorkRng As Range, pIsNumber As Boolean] As String
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len[pWorkRng.Value]
For i = 1 To xLen
xStr = VBA.Mid[pWorkRng.Value, i, 1]
If [[VBA.IsNumeric[xStr] And pIsNumber] Or [Not [VBA.IsNumeric[xStr]] And Not [pIsNumber]]] Then
SplitText = SplitText + xStr
End If
Next
End Function
Mã này được viết dưới dạng một hàm do người dùng định nghĩa, cần hai đối số để hoạt động;
- Đóng trình soạn thảo VB để quay lại trang tính
- Nhập công thức sau vào một cột riêng để trích xuất văn bản từ các giá trị trong cột B
=SplitText[B3,FALSE]
Với FALSE là một phần của hàm, tất cả các giá trị trong chuỗi văn bản không phải là giá trị số đều được trả về
- Áp dụng công thức này cho một cột riêng cho các giá trị số từ chuỗi văn bản
=SplitText[B3,TRUE]
Với TRUE trong hàm, hàm trả về tất cả các giá trị số từ chuỗi văn bản
Thời gian để tách phần cuối khỏi hướng dẫn. Đó là những mẹo hay nhất của chúng tôi về tách số khỏi văn bản trong Excel. Đừng nghi ngờ hay sợ hãi về việc thay đổi mọi thứ xung quanh, điều quan trọng là bạn cố gắng làm cho mọi thứ hiệu quả với chính mình. Nếu không, luôn có Ctrl + Z. Chúng tôi hy vọng bạn thấy giải thích của chúng tôi sâu sắc và mẹo của chúng tôi dễ dàng áp dụng cho kịch bản Excel của bạn. Và cùng với đó, Excel sayonara