THUTHUATEXCEL.COM - THỦ THUẬT EXCEL, THỦ THUẬT WORD, TIN HỌC VĂN PHÒNG
Facebook
THUTHUATEXCEL.COM - THỦ THUẬT EXCEL, THỦ THUẬT WORD, TIN HỌC VĂN PHÒNG
2020-05-14T15:17:00+07:00
2020-05-14T08:17:31Z
Ở bài viết nãy sẽ hướng dẫn các bạn cách kết hợp hàm excel để giải quyết một số yêu cầu nho nhỏ trong quá trình thao tác với bảng tính...
Ở bài viết nãy sẽ hướng dẫn các bạn cách kết hợp hàm excel để giải quyết một số yêu cầu nho nhỏ trong quá trình thao tác với bảng tính excel.
Cụ thể chúng ta có một chuỗi như hình vẽ, đặc điểm chung của chúng là đều có một dấu gạch "-" nối ở giữa, và nhiệm vụ của chúng ta là tách các số ở bên phải, và bên trái đó ra, bạn nên tin rằng trong công việc sẽ có lúc bạn gặp trường hợp này.
Tách số sau dấu gạch nối
Để tách một ký tự, hay một chuỗi ra khỏi một chuỗi lớn hơn, chúng ta sẽ dùng các hàm như left, right, mid...và ở bài toán này chúng ta sẽ kết hợp hàm như sau: =MID[B1;[FIND["-";B1;1]+1];100]
Phân tích hàm sự kết hợp này như sau:
Đầu tiên chúng ta sẽ xác định hàm dùng để cách đó là hàm MID, và ký tự cần lấy sẽ nằm sau dấu gạch nối nên chúng ta sử dụng hàm FIND để tìm vị trí của ký tự gạch nối này, và công thức như bạn thấy ở trên rồi đấy
Enter
Copy công thức qua các ô còn lại để xem chúng ta đã lấy đúng yêu cầu chưa nhé.
Tách số trước dấu gạch nối
Tương tự với suy luận như ban đầu, chúng sẽ sử dụng công thức kết hợp như sau:
=LEFT[B1;LEN[B1]-LEN[MID[B1;FIND["-";B1;1];100]]]
Kết quả
Để hiểu công thức trên hoạt động như thế nào, hay để biết khi kết hợp các công thức trong excel hoạt động ra sao các bạn có thể tham khảo bài kiểm tra hoạt động hàm trong excel
Chúc các bạn thành công
5 THUTHUATEXCEL.COM - THỦ THUẬT EXCEL, THỦ THUẬT WORD, TIN HỌC VĂN PHÒNG: Tách Chuỗi Dựa Vào Ký Tự Cố Định Trong Excel Ở bài viết nãy sẽ hướng dẫn các bạn cách kết hợp hàm excel để giải quyết một số yêu cầu nho nhỏ trong quá trình thao tác với bảng tính...
Ở bài viết nãy sẽ hướng dẫn các bạn cách kết hợp hàm excel để giải quyết một số yêu cầu nho nhỏ trong quá trình thao tác với bảng tính... Click vào link để xem chi tiết về bài này và các bài viết liên quan trên website >>
Thao tác tách nội dung cột trong Excel là một trong những thao tác cơ bản mà ai cũng nên biết để xử lý dữ liệu.
Tách dữ liệu trong một trường Excel sang những cột khác là thao tác thông dụng khi xử dữ liệu. Chẳng hạn như khi tách thông tin khách hàng, đối tác. Khi đó các dữ liệu được nhập chung vào trong một cột, và bạn cần phải tách các thông tin sang những cột khác nhau. Trên phiên bản Excel cũ, bạn sẽ phải sử dụng hàm tách chuỗi khá phức tạp. Tuy nhiên từ bản Excel 2013 trở lên, chúng ta có thể sử dụng các công cụ khác để tách các giá trị trong một trường nhất định vào các ô trên bảng tính. Mời bạn theo dõi bài viết sau để hiểu rõ hơn về cách tách nội dung cột trong Excel nhé.
Ví dụ ta có bảng dữ liệu như hình dưới. Yêu cầu cần tách riêng họ tên, ngày sinh, giới tính của từng người. Để thực hiện thì các bạn hãy làm theo các bước sau nhé.
Bước 1: Đầu tiên, các bạn cần bôi đen toàn bộ vùng dữ liệu cần tách nội dung. Sau đó các bạn chọn thẻ Data trên thanh công cụ rồi chọn mục Text to Columns.
Bước 2: Lúc này, hộp thoại Convert Text to Columns Wizard sẽ hiện ra. Tại mục Choose the file type that best describes your data sẽ có hai lựa chọn cho các bạn chọn đó là:
- Delimited: tiến hành tách cột với những ký tự ngăn cách ví dụ như tab, dấu gạch ngang, dấu phẩy, khoảng trắng…
- Fixed with: tách cột theo chiều rộng của dữ liệu, ví dụ dữ liệu gồm 2 cột dù từng hàng có chiều dài khác nhau, nhưng chia đều chiều rộng thành 2 khoảng giống nhau.
Trong bài sử dụng dấu gạch ngang để ngăn cách nội dung, nên tôi sẽ tích chọn vào Delimited. Sau đó nhấn Next ở bên dưới.
Bước 3: Tiếp theo, tại mục Delimiters các bạn lựa chọn dấu phân tách nội dung trong cột muốn tách. Sẽ có các lựa chọn gồm:
- Tab: nội dụng được tách bằng 1 khoảng trắng tab.
- Semicolon: cách bằng dấu chấm phảy.
- Comma: sử dụng tách bằng dấu phẩy.
- Space: sử dụng tách bằng khoảng trắng.
- Other: sử dụng phân cách khác để tách nội dung.
Trong nội dung do sử dụng dấu gạch ngang để tách nội dung, nên nhấp chuột vào mục Other và sau đó điền dấu gạch ngang đã dùng trong file Excel vào ô bên cạnh. Tiếp đến nhấn Next để sang bước cuối.
Bước 4: Tiếp theo, tại mục Column data format các bạn tiến hành chọn định dạng cho dữ liệu ở từng cột. Sau đó các bạn nhấn Finish để tách dữ liệu.
Kết quả ta sẽ thu được là họ tên, ngày sinh, giới tính của từng người đã được tách riêng biệt một cách nhanh chóng.
Như vậy, bài viết trên đã hướng dẫn các bạn cách tách nội dung cột trong Excel. Hy vọng bài viết trên sẽ hữu ích với các bạn trong quá trình làm việc.
[Nguồn thuthuatplus]
Tách lấy chuỗi/nhóm ký tự phía trước hoặc phía sau kí tự đặc biệt nào đó kiểu như: * - / | } { ? > < @ ! $ % ^ & ] [ ~ ...
Giả sử ô Excel A1 của bạn có nội dung là:
N659.K1*17H45-3/7-27/6
[có nhóm ký tự đặc biệt * - / ]
Bạn muốn lấy chuỗi kí tự phía trước dấu * - / và muốn lấy chuỗi kí tự đứng sau dấu * - / nhưng phải dùng thông qua công thức mà không phải chia tách trực tiếp
*
Để lấy chuỗi kí tự đứng trước dấu * hoặc ký tự khác thì áp dụng theo công thức sau:
=Left[A1,[Find["*",A1,1]-1]]
Nếu dữ liệu của bạn có ký tự đặc biệt khác như
- / | } { ? > < @ ! $ % ^ & ] [ ~ thì thay dấu "*" bằng các ký tự đó là ok
*
Để lấy chuỗi kí tự đứng sau dấu * phải theo công thức sau
=Right[A1,[Len[A1]-[Find["*",A1,1]]]]
Xong*
1 số câu hỏi cùng chủ đề như: Hàm Excel lấy chữ sau ký tự đặc biệt; Cách lấy 1 nhóm ký tự sau 1 ký tự đặc biệt; Lấy 1 ký tự trước 1 ký tự trong Excel; Lấy 1 ký tự phía sau 1 ký tự khác
ĐỂ DỄ HIỂU HƠN HÃY XEM VIDEO:
Tách Chuỗi Ký Tự Trong Excel Ko Cần Dùng Hàm
Trong Excel khi làm việc với chuỗi kí tự có rất nhiều trường hợp phải tách chuỗi kí tự mới sử dụng được dữ liệu mà số lượng dữ liệu cần tách khá lớn, nếu đơn thuần làm thủ công bằng tay tốn rất nhiều thời gian và công sức. Bài viết dưới đây hướng dẫn tới các bạn cách tách chuỗi trong Excel.
Một số hàm sử dụng khi tách chuỗi kí tự:
- Left: Hàm thực hiện lấy phần chuỗi con nằm bên trái phần chuỗi gốc
- Right: Hàm thực hiện lấy phần chuỗi con nằm bên phải phần chuỗi gốc
- Mid: Hàm thực hiện lấy phần chuỗi con nằm ở giữa của chuỗi gốc
Cú pháp:
- LEFT[text,[num_char]]
- RIGHT[text,[num_char]]
- MID[text,start_num, num_char]
Trong đó:
+ text: Chuỗi kí tự gốc chứa chuỗi cần cắt
+ num_char: số kí tự cần cắt.
+ start_num: vị trí cần bắt đầu lấy chuỗi kí tự con.
Để tách chuỗi kí tự có rất nhiều cách:
- Sử dụng hàm cắt chuỗi left, right, mid kết hợp tính năng thay thế trong Excel.
- Sử dụng hàm left, right, mid, …. Kết hợp 1 số hàm tìm kiếm xác định vị trí trong Excel.
Ở 2 ví dụ dưới đây hướng dẫn chi tiết tới các bạn cách tách chuỗi trong Excel ứng dụng nhiều trong thực tế hiện nay.
Ví dụ 1: Tách trường họ và tên thành 3 trường riêng rẽ: họ, tên lót và tên
Bước 1: Copy toàn bộ dữ liệu cột họ và tên sang cột tên:
Bước 2: Bôi đen toàn bộ dữ liệu trong cột Tên -> nhấn tổ hợp phím Ctrl + H nhập nội dung như sau:
- Mục Find what: nhập kí tự “*” và dấu cách.
- Mục Replace with: Để trống
Nhấn Replace all để thực hiện thay thế toàn bộ kí tự trước dấu cách bằng kí tự trắng:
Bước 3: Hộp thoại thông báo xuất hiện đã thay thế kích chọn OK:
Kết quả đã lấy được tên từ cột họ và tên:
Bước 4: Lấy giá trị họ và tên từ cột họ và tên bằng cách nhập công thức: = LEFT[C6,LEN[C6]-LEN[G6]-1]
Nhấn Enter được kết quả:
Bước 5: Sao chép toàn bộ cột họ vừa lấy được dán sang cột tên lót, chú ý trong quá trình dán sang cột tên lọt thực hiện chuột phải chọn dán theo kiểu giá trị như hình:
Bước 6: Thực hiện tương tự như cột tên để lấy giá trị tên lót bằng cách sử dụng tính năng replace trong Excel:
Kết quả lấy được giá trị tên lót từ cột họ:
Bước 7: Lấy giá trị cho cột họ.
- Tạo thêm 1 cột bên cạnh cột Họ tại ô cần lấy giá trị họ nhập công thức: = LEFT[E6,LEN[E6]-LEN[G6]-1]
Nhấn Enter -> sao chép công thức cho các giá trị còn lại được kết quả:
Bước 8: Thực hiện sao chép và paste lại giá trị cột họ theo giá trị -> thực hiện xóa bỏ cột họ thừa ngoài cùng [chú ý nếu không copy và dán lại theo kiểu giá trị cho cột Họ khi xóa cột họ thừa sẽ báo lỗi]
Kết quả bạn đã tách 3 trường họ, tên lót và tên từ cột họ và tên. Đây là cách nhanh và giúp bạn tiết kiệm thời gian và công sức khi cần tách chuỗi họ và tên:
Ví dụ 2: Từ giá trị khi nhập vật tư tách chuỗi kí tự thành 2 phần tên sản phẩm và xuất xứ.
- Ở bài này khi nhập vật tư gồm tên vật tư và xuất xứ cách nhau bởi dấu gạch ngang. Để thực hiện tách chuỗi sử dụng 2 hàm Left và Right. Tuy nhiên vì mỗi vật tư có tên và độ dài khác nhau nên không biết trước số kí tự cần lấy trong hàm Left và Right:
- Vì trong tất cả giá trị nhập vật tư gồm tên vật tư và xuất xứ có cách nhau bởi dấu gạch ngang căn cứ dấu gạch ngang để xác định số kí tự cần lấy cho hàm Left và Right bằng cách sử dụng hàm Find.
Cách tách tên sản phẩm và xuất xứ sản phẩm thực hiện các bước sau:
Bước 1: Tại ô cần lấy tên sản phẩm nhập công thức: =LEFT[C6,FIND[“-“,C6]-1]
Bước 2: Nhấn Enter đã lấy được tên sản phẩm từ vật tư đã nhập:
Bước 3: Sao chép công thức cho các giá trị còn lại được kết quả:
Bước 4: Tại ô cần lấy xuất xứ của vật tư nhập công thức: =RIGHT[C6,LEN[C6]-FIND[“-“,C6]]
Bước 5: Nhấn Enter -> sao chép công thức cho các giá trị còn lại được kết quả:
Như vậy với các bước đơn giản bạn đã có thể tách riêng tên sản phẩm và xuất xứ từ mã vật tư đã nhập:
Trên đây là hướng dẫn chi tiết cách tách chuỗi trong Excel dựa trên các ví dụ cụ thể. Chúc các bạn thành công!