Tách excel bằng công thức dấu phân cách

Một kịch bản là bạn cần nối nhiều chuỗi văn bản thành một chuỗi văn bản duy nhất. Mặt trái của điều đó là tách một chuỗi văn bản thành nhiều chuỗi văn bản. Nếu dữ liệu trong tay được sao chép từ đâu đó hoặc được tạo bởi một người nào đó hoàn toàn bỏ sót điểm của cột Excel, bạn sẽ thấy rằng mình cần phải tách một khối văn bản để phân loại nó

Việc tách văn bản phần lớn phụ thuộc vào dấu phân cách trong chuỗi văn bản. Dấu phân cách là một ký tự hoặc biểu tượng đánh dấu đầu hoặc cuối của một chuỗi ký tự. Ví dụ về dấu phân cách là ký tự khoảng trắng, dấu gạch ngang, dấu chấm, dấu phẩy

Trường hợp ví dụ của chúng tôi cho hướng dẫn này liên quan đến việc chia chi tiết sách thành tên sách, tác giả và thể loại. Dấu phân cách chúng tôi đã sử dụng là dấu phẩy

Hướng dẫn này sẽ hướng dẫn bạn cách tách văn bản trong Excel bằng các tính năng Văn bản thành Cột và Flash Fill, công thức và VBA. Phương pháp công thức bao gồm tách văn bản theo một ký tự cụ thể. Đó là thực đơn hôm nay

Hãy chia tách

Mục lục

Sử dụng văn bản thành cột

Tính năng này đúng như tên gọi của nó. Văn bản thành Cột chia một cột văn bản thành nhiều cột với các điều khiển được chỉ định. Văn bản thành Cột có thể phân tách văn bản bằng dấu phân cách và vì dữ liệu của chúng tôi chỉ chứa dấu phẩy làm dấu phân cách nên việc sử dụng tính năng này trở nên rất dễ dàng. Xem các bước sau để tách văn bản bằng Văn bản thành Cột

• Chọn dữ liệu bạn muốn tách
• Chuyển đến tab Dữ liệu và chọn biểu tượng Văn bản thành Cột từ Công cụ Dữ liệu

• Chọn nút radio Được phân tách và sau đó nhấp vào Tiếp theo

• Trong phần Dấu phân cách, chọn Dấu phẩy
• Sau đó chọn nút Tiếp theo

• Bây giờ bạn cần chọn nơi bạn muốn tách văn bản. Bấm vào trường Đích, sau đó chọn ô đích trên trang tính ở chế độ nền mà bạn muốn văn bản phân tách bắt đầu
• Chọn nút Kết thúc để đóng Văn bản thành Cột

Sử dụng dấu phân cách bằng dấu phẩy để phân tách chuỗi văn bản, Văn bản thành Cột đã chia văn bản từ trường hợp ví dụ của chúng tôi thành ba cột

May mắn thay, dữ liệu của chúng tôi không chứa sách có dấu phẩy trong tên sách. Nếu chúng tôi có cuốn sách Cows, Pigs, Wars and Witches của Marvin Harris trong tập dữ liệu, văn bản sẽ được chia thành 5 cột thay vì 3 như phần còn lại. Nếu dấu phân cách trong dữ liệu của bạn xuất hiện trong chuỗi văn bản nhiều hơn dấu phân cách, thì bạn sẽ gặp may mắn hơn khi tách văn bản bằng các phương pháp khác. Bây giờ đến một quan sát khác

Sử dụng chức năng TRIM để cắt khoảng trắng thừa

Hãy xem xét kỹ hơn đầu ra của Văn bản thành Cột. Lưu ý cách hai cột cuối cùng mang một không gian hàng đầu?

Hai cột cuối cùng có khoảng trắng ở đầu vì Văn bản thành Cột chỉ lấy dấu phân cách làm dấu từ nơi chia văn bản. Ký tự khoảng trắng sau dấu phẩy được mang theo đơn vị văn bản tiếp theo và có dữ liệu của chúng tôi với dấu cách ở đầu

Cách khắc phục nhanh cho vấn đề này là sử dụng chức năng TRIM để xóa các khoảng trống thừa. Đây là công thức chúng tôi đã sử dụng để xóa khoảng trắng ở đầu cột D và E

=TRIM(C4)

Hàm TRIM xóa tất cả các khoảng trắng khỏi một chuỗi văn bản trừ một ký tự khoảng trắng giữa hai từ. Mọi dấu cách ở đầu, ở cuối hoặc thừa sẽ bị xóa khỏi giá trị của ô. Chúng tôi chỉ đơn giản sử dụng công thức với tham chiếu ô chứa khoảng trắng ở đầu

Và điều đó đã dọn sạch không gian hàng đầu cho chúng tôi. Dữ liệu – tốt để đi

Sử dụng công thức để tách văn bản trong Excel

Chúng ta có thể sử dụng các hàm Excel để xây dựng các công thức có thể giúp chúng ta chia một chuỗi văn bản thành nhiều

Tách chuỗi bằng dấu phân cách

Sử dụng một công thức cũng có thể chia một chuỗi văn bản thành nhiều chuỗi nhưng nó sẽ yêu cầu kết hợp các hàm. Chúng ta hãy tóm tắt một chút về các chức năng tạo thành một phần của công thức này mà chúng ta sẽ sử dụng để tách văn bản

Hàm SUBSTITUTE thay thế văn bản cũ trong chuỗi văn bản bằng văn bản mới

Hàm REPT lặp lại văn bản một số lần nhất định

Hàm LEN trả về số lượng ký tự trong một chuỗi văn bản

Hàm MID trả về một số ký tự nhất định từ giữa chuỗi văn bản có vị trí bắt đầu được chỉ định

Hàm TRIM xóa tất cả khoảng trắng, ngoại trừ khoảng trắng đơn giữa các từ, khỏi chuỗi văn bản

Bây giờ, hãy xem cách kết hợp các hàm này có thể được sử dụng để phân chia văn bản bằng một công thức duy nhất

=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))

Trong ví dụ của chúng tôi, ô đầu tiên chúng tôi đang sử dụng công thức này là ô B5. Số lượng ký tự trong B5 được đếm bởi hàm LEN là 49. Hàm REPT lặp lại khoảng trắng (được biểu thị bằng “ “ trong công thức) trong B5 cho số lượng ký tự được cung cấp bởi hàm LEN i. e. 49

Hàm SUBSTITUTE thay thế dấu phẩy “,” trong B5 bằng 49 ký tự khoảng trắng do hàm REPT cung cấp. Vì có hai dấu phẩy ở B5, một sau tên sách và một sau tác giả, 49 dấu cách sẽ được nhập sau tên sách và 49 dấu cách sau tác giả, tạo ra một khoảng cách vừa phải giữa văn bản mà chúng ta muốn tách

Bây giờ hãy xem các phép tính cho hàm MID. Bit đầu tiên là (C$4-1). Ở hàng 4, chúng tôi đã thêm đánh số sê-ri cho từng cột cho danh mục của mình. Hàng đã được khóa trong công thức bằng ký hiệu $ nên hàng không thay đổi khi công thức được sao chép xuống. Nhưng chúng tôi đã để trống cột này để số sê-ri thay đổi cho các cột tương ứng được sử dụng trong công thức

Trong công thức, 1 được trừ khỏi C4 (1-1=0) và kết quả được nhân với số ký tự trong B5 i. e. LEN($B5) và sau đó 1 được thêm vào biểu thức. Phép tính cho vị trí bắt đầu trong hàm MID, i. e. (C$4-1)*LEN($B5)+1, trở thành (1-1)*49+1 bằng 1

Hàm MID trả về văn bản từ giữa B5, vị trí bắt đầu là 1 (nghĩa là văn bản được trả về bắt đầu từ ký tự đầu tiên) và số ký tự được trả về là LEN($B5) i. e 49 ký tự. Vì chúng tôi đã thêm 49 khoảng trắng vào vị trí của mỗi dấu phẩy, điều đó mang lại cho chúng tôi nhiều diện tích để trả lại một đoạn văn bản một cách an toàn cùng với một số khoảng trắng thừa. Kết quả cho đến khi chức năng MID là Bài hát về băng và lửa với nhiều khoảng trắng ở cuối

Không gian thêm không có vấn đề. Hàm TRIM xóa mọi khoảng trắng thừa để lại các khoảng trắng đơn lẻ giữa các từ và vì vậy cuối cùng chúng ta có tên sách được trả về là A Song of Ice and Fire

Bây giờ đối với cột tiếp theo và do đó là danh mục tiếp theo, phép tính cho vị trí bắt đầu trong hàm MID sẽ thay đổi như sau (D$4-1)*LEN($B5)+1. Biểu thức rút gọn thành (2-1)*49+1 bằng 50. Nếu hàm MID trả về các ký tự bắt đầu từ ký tự thứ 50, với tất cả các khoảng trắng thừa do hàm REPT thêm vào, thì hàm MID sẽ trả về giá trị nào theo mẫu này. không gian không gian tác giả

Các khoảng trắng ở đầu và cuối sẽ được cắt bớt bởi hàm TRIM và kết quả sẽ là George RR Martin

“+1” trong đối số vị trí bắt đầu của hàm MID không liên quan đến các cột tiếp theo, chỉ liên quan đến cột đầu tiên. Đó là bởi vì, nếu không có “+1” trong phép tính của cột đầu tiên, nó sẽ là 0*49 và sẽ kết thúc bằng #VALUE. lỗi

Công thức được sao chép dọc theo cột E cung cấp cho chúng tôi thể loại từ văn bản kết hợp trong cột B và hoàn thành bộ của chúng tôi

Tách chuỗi ở ký tự cụ thể

Nếu chỉ có một dấu phân cách duy nhất là một ký tự cụ thể thì sẽ không cần dùng công thức dài dòng như trên để tách văn bản. Giả sử, như ví dụ trường hợp của chúng tôi bên dưới, chúng tôi phải tách mã sản phẩm và loại sản phẩm được nối với nhau bằng dấu gạch nối

Bây giờ điều này sẽ dễ dàng hơn nếu mã sản phẩm có một số ký tự cố định; . Nhưng niềm vui trong đó là gì?

Chúng ta sẽ để hàm FIND thực hiện một chút công việc tìm kiếm cho chúng ta và tìm dấu gạch nối trong văn bản để hàm LEFT và hàm RIGHT có thể trả về văn bản xung quanh. Đây là công thức có hàm LEFT để trả về phần trích xuất đầu tiên

=LEFT(B3,FIND("-",B3)-1)

Hàm FIND tìm kiếm B3 cho vị trí của dấu gạch ngang “-“ trong chuỗi văn bản, là 6. Hàm LEFT sau đó trả về các ký tự bắt đầu từ bên trái của văn bản và số ký tự cần trả về là 6-1. “-1” ở cuối đảm bảo rằng các ký tự được trả về không bao gồm dấu gạch ngang. Đây là kết quả của công thức này để trả về đoạn đầu tiên của văn bản được phân tách

Bây giờ đối với đoạn văn bản thứ hai, hàm RIGHT hoạt động với công thức này

=RIGHT(B3,LEN(B3)-FIND("-",B3))

Hàm FIND lại được sử dụng để tìm vị trí của dấu gạch nối trong B3 mà chúng ta biết đó là ký tự thứ 6. Hàm LEN trả về số ký tự trong B3 là 23. Hàm RIGHT trích xuất 23-6 ký tự từ B3 và trả về loại sản phẩm “Loa Bluetooth”. Đây là cách nó đã hoạt động cho ví dụ của chúng tôi

Sử dụng Flash Fill

Tính năng Flash Fill trong Excel sẽ tự động điền vào các giá trị dựa trên một vài ví dụ được cung cấp thủ công. Tính dễ dàng của Flash Fill là bạn không cần nhớ bất kỳ công thức nào, sử dụng bất kỳ trình hướng dẫn nào hoặc thao tác với bất kỳ cài đặt nào. Nếu dữ liệu của bạn nhất quán, Flash Fill sẽ là cách nhanh nhất để tiếp nhận những gì bạn đang cố gắng hoàn thành. Hãy xem các bước sử dụng Flash Fill để tách văn bản và cách nó hoạt động trong trường hợp ví dụ của chúng tôi

• Nhập văn bản đầu tiên làm ví dụ để Flash Fill chọn mẫu và nhấn Enter

• Từ nhóm Chỉnh sửa của tab Trang chủ, nhấp vào biểu tượng Điền và chọn Flash Điền từ menu
• Ngoài ra, sử dụng các phím tắt Ctrl + E

• Chọn ví dụ được cung cấp, Flash Fill sẽ chia văn bản và điền vào cột theo cùng một mẫu

• Lặp lại các bước tương tự cho mỗi cột được lấp đầy bằng Flash

Flash Fill sẽ giải quyết vấn đề phải cắt bớt khoảng trắng ở đầu và cuối nhưng như đã đề cập, nếu có bất kỳ sự bất thường hoặc không nhất quán nào trong dữ liệu (e. g một khoảng trắng trước và sau dấu phẩy), Flash Fill sẽ không phải là phương pháp đáng tin cậy để tách văn bản và do phần lớn dữ liệu, sự cố có thể bị bỏ qua. Nếu bạn nghi ngờ dữ liệu có sự không nhất quán, hãy sử dụng các phương pháp khác để tách văn bản

Sử dụng chức năng VBA

Phương pháp cuối cùng mà chúng ta sẽ thảo luận hôm nay để tách văn bản sẽ là một hàm VBA. Để tự động hóa các tác vụ trong các ứng dụng MS Office, có thể tạo và sử dụng macro bằng VBA. Nhiệm vụ của chúng ta là tách văn bản trong Excel và dưới đây là các bước thực hiện việc này bằng VBA

• Nếu bạn đã thêm tab Nhà phát triển vào thanh công cụ Ribbon, bấm vào tab Nhà phát triển rồi chọn biểu tượng Visual Basic trong nhóm Mã để khởi chạy Visual Basic
• Bạn cũng có thể sử dụng các phím Alt + F11

• Trình soạn thảo Visual Basic sẽ được mở

• Mở tab Chèn và chọn Mô-đun từ danh sách. Một cửa sổ Mô-đun sẽ mở ra

• Trong cửa sổ Mô-đun, sao chép-dán đoạn mã sau để tạo macro có tiêu đề SplitText

Sub SplitText()
Dim MyArray() As String, Count As Long, i As Variant
For n = 4 To 16
 MyArray = Split(Cells(n, 2), ",")
 Count = 3
 For Each i In MyArray
 Cells(n, Count) = i
 Count = Count + 1
 Next i
Next n
End Sub

Chỉnh sửa các phần sau của mã theo dữ liệu của bạn

• 'For n = 4 To 16' – 4 và 16 đại diện cho hàng đầu tiên và cuối cùng của tập dữ liệu
• 'MyArray = Split(Cells(n, 2), ",")' – Dấu phẩy kèm theo dấu ngoặc kép là dấu phân cách
• 'Count = 3' – 3 là số cột của cột đầu tiên mà dữ liệu kết quả sẽ được trả về trong

• Để chạy mã, nhấn F5

Dữ liệu sẽ được chia theo các giá trị được cung cấp

• Dọn sạch các khoảng trống ở đầu cột D và E bằng hàm TRIM

Bây giờ, hãy tách hướng dẫn tích cực khỏi phần kết luận của nó. Hôm nay bạn đã học được một số cách về cách tách văn bản trong Excel. Nếu lần sau bạn thấy khó hiểu về khả năng tách văn bản của mình, hãy bỏ túi cái này và sẵn sàng thử. Chúng tôi sẽ trở lại với nhiều tính năng Excel hơn để lấp đầy túi tiền của bạn. Tạo một số không gian

Công thức Excel để tách văn bản là gì?

Bạn có thể tách ô trong Excel bằng công thức không?