Xu Hướng 2/2023 # Hàm Xóa, Kết Hợp, Thay Thế Chuỗi/Ký Tự Trong Excel # Top 10 View | Hoisinhvienqnam.edu.vn

Xu Hướng 2/2023 # Hàm Xóa, Kết Hợp, Thay Thế Chuỗi/Ký Tự Trong Excel # Top 10 View

Bạn đang xem bài viết Hàm Xóa, Kết Hợp, Thay Thế Chuỗi/Ký Tự Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.

Excel ngoài việc tính toán, vẽ biểu đồ ra thì còn rất nhiều ứng dụng cho các lĩnh vực khác nếu mình biết tận dụng để ứng dụng vào công việc.

Đây là 3 dạng hàm mình hay dùng để giải quyết hay chỉnh sữa một đoạn mã văn bản hay code dài. Nếu chỉnh sữa đơn giản với một vài con chữ thì mình chỉ cần dùng replace trong file txt là đủ, nhưng với việc xử lý số lượng nhiều và lằng nhằng thì với mình ứng dụng các hàm trong Excel là rất công dụng.

Chưa kể có một số thuật toán, nếu dùng code để giải quyết thì rất dài, khó và lằng nhằng, nhưng cũng với thuật toán đó, chỉ cần kết hợp một số hàm tính trong excel là đã có kết quả ngay, lại sử dụng được số lượng nhiều, rút ngắn thao tác lập lại.

1. Hàm dùng để xóa (delete) bớt chuỗi dữ liệu, ký tự trong Excel

– Cụ thể sẽ dùng hàm MID, hàm kết hợp hàm LEN, RIGHT/LEFT.

– Công thức:

=MID(value, number_1, number_2) =LEFT(value, LEN(value)-number) =RIGHT(value, LEN(value)-number)

– Trong đó:

value: Chuỗi dữ liệu hay ô chứa chuỗi dữ liệu cần xóa bớt.

number_1: Số ký tự bắt đầu lấy.

number_2: Số ký tự muốn lấy.

number: Số ký tự muốn xóa bớt đi.

RIGHT/LEFT: Phần ký tự cần lấy tính từ đâu qua.

VÍ DỤ:

– Ví dụ 1: Ví dụ về hàm MID.

– Ví dụ 2: Xóa bớt các ký tự ở cuối ô dữ liệu.

– Ví dụ 3: Xóa bớt các ký tự ở đầu ô dữ liệu.

2. Hàm dùng để thay thế chuỗi dữ liệu, ký tự trong Excel

=SUBSTITUTE(text,”old_text”,”new_text”,instance_num)

– Ví dụ 1: Thay thế một chuỗi ký tự.

– Ví dụ 2: Kết hợp nhiều hàm SUBSTITUTE.

** Ở cả 2 ví dụ trên mình đều bỏ qua instance_num. Thật ra thì tất cả các ký tự này đều nằm sát nhau nên mình có thể dùng thành một hàm duy nhất, tuy nhiên đây chỉ là một ví dụ để nhằm hiểu rõ hơn về cách kết hợp hàm SUBSTITUTE.

3. Hàm dùng để kết hợp chuỗi dữ liệu, phép tính, text trong Excel

– Chỉ đơn giản là thêm: &+value (với phép tính) hay &+”text” (với chữ) vào bên trong hàm.

– Ví dụ để hiểu rõ hơn:

Ô B2: CTG

Ô C2: 200

Ô D2: 300

Ô E2: 400

Mình muốn kết hợp 4 ô B2, C2, D2, E2 lại, đồng thời thêm phép tính tổng 3 ô C2,D2,E2 và chèn thêm chữ vào.

Công thức hàm sẽ như sau: F2=B2 &+ ” có lượt view trong 3 ngày là: ” &+C2+D2+E2 &+” View”

Kết quả ô F2: CTG có lượt view trong 3 ngày là: 900 View

4. Ví dụ về ứng dụng kết hợp cả 3 hàm trên

– Đây mới thật sự là phức tạp, thoạt nhìn vào thì thấy vậy nhưng phân tích kỹ từng bước thì cũng không đến nỗi rối lắm ^_^

– Nội dung hàm:

Thay thế: http://img thành http://i.img

Thay thế: image host thành nội dung của ô A2 nhưng bỏ bớt đi 4 ký tự đầu.

Chèn thêm: height=”180″ width=”200″ vào trước alt.

Như ví dụ trong hàm trên, nếu chỉ để xử lý vài ba dòng như thế bằng tay thì được, nhưng với dữ liệu có cả trăm hay hàng ngàn thì sao, thật sự là nan giải đó, nhưng nếu hiểu và tận dụng được các hàm trong Excel vào thì thật sự rất có ích.

Việc dùng các hàm đơn lẻ thì rất dễ, nhưng nếu phải kết hợp nhiều hàm để giải quyết thì có thể sẽ rối và hay báo sai hay thiếu công thức rất nhức đầu, cách đơn giản của mình là cứ làm dần từng cái một (hơi mất thời gian lúc đầu) rồi sau kết hợp lại sẽ dễ hơn. Có thể gặp khó khăn lúc đầu nhưng nếu làm tốt công thức hàm rồi thì công việc về sau sẽ nhàn hơn rất nhiều.

Hàm Replace Trong Excel, Thay Thế Một Phần Của Chuỗi Văn Bản

Hàm REPLACE và hàm SUBSTITUTE trong Excel

Hàm REPLACE trong Excel tương tự như hàm SUBSTITUTE, chỉ khác:

– Hàm Replace thay thế văn bản ở vị trí cụ thể của chuỗi được cung cấp.– Hàm Substitute thay thế một hoặc nhiều đối tượng của chuỗi văn bản cụ thể.

Mô tả hàm REPLACE trong Excel, thay thế một phần của chuỗi văn bản

Hàm Replace trong Excel thay thế toàn bộ hoặc một phần của chuỗi văn bản bằng một chuỗi văn bản khác.

Cú pháp hàm Replace trong Excel có dạng:

REPLACE( old_text, start_num, num_chars, new_text )

Trong đó:

– old_text: Chuỗi văn bản gốc mà bạn muốn thay thế một phần văn bản.– start_num: Vị trí của ký tự đầu tiên trong old_text mà bạn muốn thay thế.– num_chars: Số ký tự cần thay thế.– new_text: Văn bản thay thế.

Lưu ý hàm Replace trong Excel không phù hợp với các ngôn ngữ sử dụng bộ ký tự 2 byte (chẳng hạn như tiếng Trung, tiếng Nhật, tiếng Hàn). Với các ngôn ngữ này bạn nên sử dụng hàm ReplaceB.

Công thức:

Kết quả trả về:

Lỗi thường gặp với hàm REPLACE trong Excel, thay thế một phần của chuỗi văn bản

Nếu hàm Replace, thay thế một phần của chuỗi văn bản trả về thông báo lỗi, có thể là lỗi #VALUE!.

Lỗi #VALUE! xảy ra là do:

– Đối số start_num được cung cấp là giá trị âm hoặc giá trị không phải kiểu số.

Hoặc:

– Đối số num_chars được cung cấp là giá trị âm hoặc giá trị không phải kiểu số.

Lỗi phổ biến khi sử dụng hàm Replace với các giá trị số, ngày và giờ

Hàm Replace trong Excel được thiết kế để sử dụng với chuỗi văn bản và trả về chuỗi văn bản khác.

Vì vậy nếu sử dụng hàm Replace để thay thế các giá trị định dạng ngày giờ hoặc số, có thể bạn sẽ nhận được kết quả không mong muốn.

Sử dụng chuột để chọn các ô mà bạn muốn chuyển đổi giá trị sang định dạng văn bản (lưu ý không được nhiều hơn 1 cột).

Từ tab Data trên thanh Ribbon Excel, bạn chọn tùy chọn Text to Columns ….

Hàm Replace sẽ hoạt đông với các giá trị đã được chuyển đổi thành văn bản.

Hàm Left, Cách Dùng Hàm Cắt Chuỗi Ký Tự Bên Trái Trong Excel

Hướng dẫn dùng hàm LEFT trong Excel

Hàm LEFT có cú pháp là =LEFT(text;[num_chars]). Trong đó:

Text là chuỗi văn bản bắt buộc, đây là chuỗi văn bản hoặc tham chiếu ô tới chuỗi văn bản có chứa các ký tự mà bạn muốn trích xuất.

Num_chars là đối số tùy chọn, đây là số ký tự muốn hàm LEFT tìm kiếm bắt đầu từ vị trí đầu tiên bên trái text.

Num_chars phải lớn hơn hoặc bằng không, nếu num_chars < 0 thì hàm sẽ trả về lỗi #VALUE!.

Num_chars lớn hơn độ dài của văn bản thì hàm LEFT sẽ trả về toàn bộ văn bản.

Nếu bỏ qua num_chars thì mặc định num_chars = 1.

Ví dụ 1: Sử dụng hàm LEFT tìm ký tự

Kết quả chúng ta được 3 ký tự tính từ trái sang phải của chuỗi ký tự trong ô B2.

Hoặc trong công thức nhập có thể thay vị trí của ô chứa chuỗi ký tự bằng ký tự và nằm trong dấu ngoặc kép như hình.

Ví dụ 2: Hàm LEFT kết hợp hàm SEARCH

Khi kết hợp 2 hàm này với nhau, chúng ta sẽ dùng để tìm kiếm chuỗi ký t��� đứng trước một ký tự nhất định, chẳng hạn lấy họ trong cột họ tên đầy đủ, lấy mã quốc gia trừ số điện thoại. Trong cột họ tên đều được ngăn cách bởi dấu cách, nên chúng ta sử dụng công thức = =LEFT(B2,SEARCH(” “,B2)-1) rồi nhấn Enter.

Khi đó -1 để không trích xuất ký tự dấu cách khi tìm kiếm ký tự.

Kết quả bạn được chuỗi ký tự họ trong ô. Kéo xuống những ô bên dưới để có các kết quả khác.

Với dãy số điện thoại muốn lấy mã vùng quốc gia đằng trước dấu chấm thì bạn nhập công thức =LEFT(B5,SEARCH(“.”,B5)-1) và nhấn Enter.

Kết quả sẽ chỉ lấy mã vùng quốc gia trong dãy số điện thoại mà thôi.

Ví dụ 3: Kết hợp hàm LEFT với hàm LEN

Hàm LEN rất hay dùng kết hợp với những hàm tìm chuỗi ký tự. Với hàm LEFT khi kết hợp thêm hàm LEN dùng để loại bỏ một số ký tự nhất định từ phần cuối của chuỗi. Công thức kết hợp =LEFT(text,LEN(text)-ký tự muốn dời).

Hàm LEN lấy tổng số ký tự trong một chuỗi, rồi trừ đi số lượng kí tự muốn loại bỏ từ tổng độ dài của dãy. Hàm LEFT sẽ trả về số ký tự còn lại.

Chẳng hạn loại bỏ 5 ký tự của chuỗi ký tự trong ô B2, nhập công thức =LEFT(B2, LEN(B2)-5) và nhấn Enter.

Kết quả chúng ta đã được dãy ký tự còn lại khi đã bỏ 5 ký tự cuối cùng trong dãy ký tự, bao gồm dấu cách.

Ví dụ 4: Kết hợp hàm LEFT và hàm VALUE

Khi 2 hàm này kết hợp với nhau sẽ trả về ký tự số, thay vì chuỗi văn bản như khi dùng hàm LEFT. Chẳng hạn xuất 2 ký tự đầu tiên của chuỗi trong ô B5, nhập công thức =VALUE(LEFT(B2,2)).

Kết quả ta được số cần tìm như hình.

Hàm Right, Cách Dùng Hàm Cắt Chuỗi Ký Tự Bên Phải Trong Excel

Để tìm kiếm thông tin, dữ liệu trên bảng Excel chúng ta có thể sử dụng nhiều hàm khác như hàm VLOOKUP trên Excel, hàm LEFT tách chuỗi ký tự Excel,.. Và trong bài viết này người dùng sẽ biết thêm cách dùng hàm RIGHT để tìm ký tự trong một chuỗi ký tự lựa chọn.

Hướng dẫn dùng hàm RIGHT trên Excel

Hàm RIGHT có cú pháp sử dụng là =RIGHT(text,[num_chars]). Trong đó:

text: là chuỗi văn bản các bạn muốn trích xuất các ký tự.

num_chars: số lượng ký tự mà các bạn muốn hàm RIGHT trích xuất.

Lưu ý:

Nếu num_chars bỏ qua thì mặc định của nó là 1.

Num_chars luôn phải lớn hơn hoặc bằng 0.

Nếu num_chars lớn hơn độ dài của chuỗi văn bản thì hàm RIGHT trả về toàn bộ chuỗi văn bản.

Ví dụ 1: Dùng hàm RIGHT tìm ký tự

Ví dụ 2: Hàm RIGHT tìm ký tự số

2 hàm này kết hợp với nhau sẽ trả về kết quả là giá trị số. Do hàm RIGHT trong Excel luôn trả về chuỗi văn bản, ngay cả khi giá trị gốc là chữ số. Nếu dùng thêm hàm VALUE thì kết quả là số. Bạn tham khảo ví dụ kết hợp 2 hàm trong bài viết Cách sử dụng hàm VALUE trong Excel.

Ví dụ 3: Hàm RIGHT xuất ký tự sau 1 ký tự cụ thể

Khi muốn tách chuỗi ký tự theo sau một ký tự cụ thể cần dùng hàm SEARCH trong Excel hoặc hàm FIND để xác định vị trí ký tự đó, nhưng trừ đi vị trí ký tự được chọn trong tổng chuỗi ký tự được trả về bằng hàm LEN. Công thức kết hợp là =RIGHT(chuỗi văn bản, LEN(chuỗi văn bản) – SEARCH(kí tự, chuỗi văn bản)).

Với bảng ví dụ này cần tìm phần tên trong ô Họ tên, nhập công thức =RIGHT(B2,LEN(B2)-SEARCH(” “,B2)) và nhấn Enter.

Với việc kết hợp 3 hàm trên, chúng ta có thể áp dụng để xuất chuỗi ký tự theo sau bất kỳ một ký tự khác, như dấu phẩy, hai chấm, dấu gạch nối,…

Ví dụ 4: Hàm RIGHT loại n ký tự đầu tiên trong chuỗi

Công thức sử dụng RIGHT(string, LEN(string)-number of chars to remove).

Ví dụ 5: Hàm RIGHT xuất ký tự sau dấu phân cách cuối cùng

Áp dụng công thức vào bảng dữ liệu này chúng ta nhập =RIGHT(B6,LEN(B6)-SEARCH(“$”,SUBSTITUTE(B6,”:”,”$”,LEN(B6)-LEN(SUBSTITUTE(B6,”:”,””))))) và nhấn Enter. Trong đó:

LEN(B6): tổng chiều dài của chuỗi ký tự trong ô B6.

LEN(SUBSTITUTE(B6,”:”,””)): chiều dài của chuỗi ký tự không có dấu hai chấm.

LEN(B6)-LEN(SUBSTITUTE(B6,”:”,””)): tổng chiều dài ban đầu trừ đi chiều dài chuỗi ký tự không có dấu hai chấm.

SUBSTITUTE(B6,”:”,”$”,LEN(B6)-LEN(SUBSTITUTE(B6,”:”,””))): thay thế dấu hai chấm phân tách cuối cùng bằng ký tự mới $, hàm SUBSTITUTE cho phép thay thế ký tự được chỉ định trong chuỗi.

SEARCH(“$”,SUBSTITUTE(B6,”:”,”$”,LEN(B6)-LEN(SUBSTITUTE(B6,”:”,””)))): xác định vị trí của dấu phân tách cuối cùng, trong hình dấu hai chấm được thay bằng dấu $.

RIGHT(B6,LEN(B6)-SEARCH(“$”,SUBSTITUTE(B6,”:”,”$”,LEN(B6)-LEN(SUBSTITUTE(B6,”:”,””))))): trả về chuỗi bên phải dấu phân tách cuối cùng, lấy tổng chiều dài chuỗi trừ đi vị trí của dấu phân cách.

Cập nhật thông tin chi tiết về Hàm Xóa, Kết Hợp, Thay Thế Chuỗi/Ký Tự Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!