Các hàm Excel phân biệt chữ hoa và chữ thường

 Hầu hết các hàm và phép tính trong Excel đều không phân biệt chữ hoa và chữ thường. Tuy nhiên, Excel có một số hàm hoạt động có phân biệt chữ hoa và chữ thường.

HÀM FIND

Hàm FIND và hàm SEARCH là 2 hàm có cấu trúc và cách sử dụng giống nhau. Hai hàm này có điểm khác biệt như sau:

- Hàm FIND: Tìm kiếm phân biệt chữ hoa và chữ thường

- Hàm SEARCh: Tìm kiếm không phân biệt chữ hoa và chữ thường. Nhưng hàm này cho phép tìm kiếm theo ký tự đại diện kiểu Wildcard

Ví dụ: Ô A1 nhập chữ "Excel Thỉnh Vũ"

Nếu dùng hàm FIND với cấu trúc là: =FIND("T",A1) thì kết quả là 7 (ở vị trí ký tự số 7 tính từ trái sang phải). Nhưng nếu sửa lại thành =FIND("t",A1) thì kết quả là #VALUE! => Vì trong chuỗi ô A1 thì chữ T viết hoa

HÀM EXACT

Thông thường khi so sánh 2 giá trị văn bản với nhau thì chỉ cần dùng phép tính = để so sánh. Tuy nhiên cách so sánh này không phân biệt chữ hoa và chữ thường

Hàm EXACT hỗ trợ so sánh chữ hoa với chữ thường.

Ví dụ: A1 nhập là EXCEL Thỉnh vũ, B1 nhập là Excel Thỉnh Vũ

- Nếu đưa công thức so sánh bằng: =A1=B1 thì kết quả trả về là TRUE

- Nếu dùng hàm EXACT: =EXACT(A1,B1) thì kết quả trả về là FALSE

HÀM SUBSTITUTE

Hàm SUBSTITUTE là hàm thay thế đoạn văn bản trong chuỗi để xử lý số liệu được dùng rất phổ biến. Và hàm này khi thực hiện thay thế có phân biệt chữ hoa và chữ thường

Ví dụ: Ô A1 nhập: Excel Thỉnh Vũ

Tại ô B1 nhập công thức là: =SUBSTITUTE(A1,"e","f") thì kết quả trả về là Excfl Thỉnh Vũ. 

HÀM CODE

Hàm CODE bản chất là trả về mã số code của 1 ký tự trong bảng ASCII và hàm này có phân biệt chữ hoa và chữ thường.

Ví dụ: =CODE("A") thì kết quả trả về là 65 nhưng =CODE("a") thì kết quả trả về 97

Hàm UNICODE

Cách hoạt động của hàm UNICODE giống với hàm CODE, nhưng hàm UNICODE mạnh hơn ở điểm là tìm ra được mã số code của 1 ký tự có dấu

Ví dụ: =CODE("Ấ") thì kết quả trả về là 63 => Tức hàm CODE ko hiểu được chữ có dấu và tự động chuyển về ký tự của dấu ?. Nhưng với hàm UNICODE =UNICODE("Ấ") thì kết quả trả về là 7844, và nếu viết thường thì =UNICODE("ấ") thì kết quả trả về là 7845

Tương tự người dùng có thể test các chữ cái viết hoa, viết thường với hàm CODE và UNICODE

Hàm TEXTAFTER, TEXTBEFORE và TEXTSPLIT

Các hàm này có ở trong Microsoft 365. Trong đó:

- TEXTAFTER: lấy chuỗi đằng sau ký tự

- TEXTBEFORE: lấy chuỗi đằng trước ký tự

- TEXTSPLIT: Tách chuỗi ra thành mảng căn cứ theo ký tự

3 hàm này đều hoạt động phân biệt chữ hoa và chữ thường.

Ví dụ: Ô A1 nhập "Excel", ô B1 lập công thức là: =TEXTBEFORE(A1,"e") thì kết quả trả về là Exc

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Sự khác nhau giữa hàm SUBSTITUTE và REPLACE dùng thay thế chuỗi ký tự trong Excel

 Trong Excel thì việc xử lý thay thế chuỗi ký tự được dùng khá phổ biến. Trong đó có 2 hàm SUBSTITUTE và hàm REPLACE

HÀM SUBSTITUTE

Cấu trúc: =SUBSTITUTE(text,old_text,new_text,[instance_num])

Trong đó:

- Text là văn bản cần xử lý

- Old_text là đoạn ký tự bên trong cần thay thế (có phân biệt chữ hoa, chữ thường)

- New_text là đoạn ký tự thay thế cho old_text

- Instance_num: Thay thế lần thứ bao nhiêu. Nếu tham số này bỏ trống thì mặc định thay thế hết. Nếu không bỏ trống thì nó sẽ thay thế cho lần xuất hiện tương ứng

Ví dụ: ô A1 nhập chuỗi ký tự là "1 kilomet là 1000 met"

Công thức tại ô B1 thay thế "met" bằng "m": 

- Old_text là "met" và bỏ trống instance_num: =SUBSTITUTE(A1,"met","m") thì kết quả là "1 kilom là 1000 m"

- Old_text là "Met" và bỏ trống instance_num: =SUBSTITUTE(A1,"Met","m") thì kết quả như văn bản gốc. Do SUBSTITUTE có phân biệt chữ hoa và chữ thường

- Old_text là "met" và instance_num là 2: =SUBSTITUTE(A1,"met","m",2) thì kết quả là "1 kilomet là 1000 m" => Tức thay thế ở lần xuất hiện thứ 2

HÀM REPLACE

Cấu trúc: =REPLACE(old_text,start_num,num_chars,new_text)

Trong đó:

- Old_text: là văn bản cần xử lý.

- Start_num: Thay thế từ ký tự số mấy (tính từ trái sang phải)

- Num_chars: Số kỳ tự cần thay thế

- New_text: Đoạn ký tự thay thế vào

Ví dụ: Ô A1 nhập chuỗi VT-VLC-001, ô A2 nhập chuỗi VT-VLP-003. Cần thay thế 3 ký tự ở giữa thành NL thì công tức tại ô B1 là:

=REPLACE(A1,4,3,"NL")

Như vậy, sự khác nhau giữa hàm SUBSTITUTE và hàm REPLACE là:

- Hàm SUBSTITUTE sẽ tìm kiếm đoạn ký tự có trong văn bản hay không, nếu có sẽ thực hiện thay thế

- Hàm REPLACE thì sẽ thay thế từ vị trí nào trong chuỗi và thực hiện thay thế bao nhiêu ký tự

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Kết hợp hàm SUBSTITUTE và VND hoặc USD để tùy biến trong đọc số thành chữ

 Rất nhiều người dùng hàm VND, USD của các addin phổ biến để đọc số thành chữ như Acchelper, vntools,...Tuy nhiên, trong nhiều tình huống người dùng muốn tùy biến, chỉnh sửa nội dung của kết quả đọc. 

Cấu trúc hàm SUBSTITUTE

=SUBSTITUTE(text,old_text,new_text,[instance_num])

- text: Là văn bản cần chỉnh sửa

- old_text: là đoạn chuỗi cần thay thế trong văn bản

- new_text: là đoạn chuỗi thay thế cho old_text

- [instance_num]: Thay thế ở lần thứ mấy. Nếu không điền tham số này thì mặc định là thay thế hết.

Nếu trong chuỗi không có ký tự old_text thì hàm SUBSTITUTE trả về nguyên giá trị text ban đầu 

Cách dùng kết hợp với hàm VND

Ví dụ tình huống: Ô A1 nhập số tiền là: 123,000,000 thì hàm VND (=VND(A1)) đọc ra kết quả là: "Một trăm hai mươi ba triệu đồng chẵn."

Trường hợp 1: Bỏ chữ "chẵn"

Công thức: =SUBSTITUTE(VND(A1),"chẵn","")

=> Tức thay chữ "chẵn" bằng ký tự rỗng

Trường hợp 2: Thay thế chữ "đồng chẵn" bằng chữ "kg"

Công thức: =SUBSTITUTE(VND(A1),"đồng chẵn","kg")

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel tách ngày tháng ra khỏi chuỗi văn bản phức tạp

 Trong thực tế thường xuyên phát sinh những số liệu có nhiều thông tin nhập trong 1 ô (phần diễn giải) và để thuận tiện hơn cho thống kê theo dõi thì sẽ cần phải bóc tách những trường thông tin đó ra. Trong đó ngày tháng được nhập trong nội dung diễn giải cũng là tình huống hay gặp phải như.

Bài viết dưới đây sẽ chia sẻ công thức sử dụng để tách ngày tháng Date ra khỏi chuỗi văn bản phức tạp, có nhiều thông tin khác đi cùng

Giả lập: ô A1 chứa chuỗi phức tạp trong đó có dữ liệu ngày tháng DATE

Thì công thức tách ngày tháng Date là: 

=MID(A1,SEARCH(" ??/??/????",A1)+1,10)

Nhưng công thức trên tách ra thì vẫn là 1 giá trị văn bản. Để chuyển đổi sang giá trị đúng ngày tháng thì có 2 cách:

Cách 1: Dùng hàm DATEVALUE lồng thêm vào nếu định dạng của đồng hồ mày tính trùng với định dạng của chuỗi tách ra (ví dụ: cùng là dd/mm/yyyy) thì công thức lồng là:

=DATEVALUE(MID(A1,SEARCH(" ??/??/????",A1)+1,10))

Cách 2: Dùng hàm DATE

=DATE(MID(A1,SEARCH(" ??/??/????",A1)+7,4),MID(A1,SEARCH(" ??/??/????",A1)+4,2),MID(A1,SEARCH(" ??/??/????",A1)+1,2))

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

NHÓM HÀM VĂN BẢN VÀ XỬ LÝ CHUỖI TRONG EXCEL (PHẦN 3)

 Trong 2 phần trước về nhóm hàm văn bản và xử lý chuỗi có 2, 3 tham số trong hàm. Trong phần này sẽ tiếp tục chi sẻ về nhóm hàm văn bản có 3, 4 tham số trong hàm:

Hàm FIND

   *** Cấu trúc: =FIND(find_text,within_text,[start_num])

   *** Cách dùng hàm FIND: Dùng để xác định ký tự cần tìm trong chuỗi ở vị trí số bao nhiêu tính từ bên trái và tìm kiếm có phân biệt chữ hoa, chữ thường. Ví dụ: =FIND("CE",A1). Giả sử ô A1 là EXCEL thì kết quả trả về là 3 => Tức ở vị trí số 3 của chuỗi. Nếu không điền tham số Start_num của hàm FIND sẽ mặc định lấy là 1, tức tìm từ vị trí đầu tiên

   *** Ứng dụng: Dùng nhiều khi phối hợp với các hàm LEFT, MID, RIGHT để xác định điểm cần lấy trong chuỗi

HÀM SEARCH

   *** Cấu trúc: =SEARCH(find_text,within_text,[start_num])

   *** Cách dùng hàm SEARCH: Dùng để xác định ký tự cần tìm trong chuỗi ở vị trí số bao nhiêu tính từ bên trái và tìm kiếm không phân biệt chữ hoa, chữ thường. Ví dụ: =FIND("CE",A1). Giả sử ô A1 là EXCEL thì kết quả trả về là 3 => Tức ở vị trí số 3 của chuỗi. Nếu không điền tham số Start_num của hàm SEARCH sẽ mặc định lấy là 1, tức tìm từ vị trí đầu tiên

   *** Ứng dụng: Dùng nhiều khi phối hợp với các hàm LEFT, MID, RIGHT để xác định điểm cần lấy trong chuỗi. Ngoài ra, hàm SEARCH còn cho phép sử dụng ký tự đại diện như *, ? (hàm FIND không có). Ví dụ: =FIND("CE*",A1)

Hàm SUBSTITUTE

   *** Cấu trúc: =SUBSTITUTE(text,old_text,new_text,[instance_num])

   *** Cách dùng hàm SUBSTITUTE: Dùng để tìm kiếm và thay thế chuỗi ký tự trong 1 chuỗi. Ví dụ: SUBSTITUTE(A1,"E","A"). Giả sử ô A1 là EXCEL thì kết quả trả về là AXCAL. Nếu tham số instance_num để trống thì mặc định sẽ là thay thế hêt. Nhưng nếu điền 1 số nào đó thì sẽ thay thế bao nhiêu lần từ trái sang phải: SUBSTITUTE(A1,"E","A",1) thì kết quả trả về là AXCEL => Tức chỉ thay thế 1 lần

   *** Ứng dụng: Hàm SUBSTITUTE được dùng rất nhiều trong xử lý dữ liệu, thay thế trong văn bản

Hàm CONCATENATE

   *** Cấu trúc: =CONCATENATE(text1,text2,...)

   *** Cách dùng hàm CONCATENATE: Dùng để ghép các chuỗi ký tự trong hàm lại với nhau. Tương đương với dùng &. Ví dụ: =CONCATENATE(A1,B1) sẽ tương đương với =A1&B1

Hàm CONCAT - Có ở Office 365, 2019 trở lên

   *** Cấu trúc: =CONCAT(text1,...) 

   *** Cách dùng hàm CONCAT: Dùng để ghép các chuỗi trong 1 vùng, 1 mảng hoặc rời rạc lại với nhau. CONCAT mạnh hơn CONCATENATE. Ví dụ: =CONCAT(A1:A10) sẽ tương đương với =A1&A2&A3&A4&A5&A6&A7&A8&A9&A10

Hàm TEXTJOIN - Có ở Office 365, 2019 trở lên

   *** Cấu trúc: =TEXTJOIN(delimiter,ignore_empty,text1,...)

   *** Cách dùng hàm TEXTJOIN: Dùng để ghép các chuỗi trong 1 vùng, 1 mảng hoặc rời rạc lại với nhau nhưng có ký tự khi ghép. ví dụ: =TEXTJOIN(",",TRUE,A1:A5) sẽ tương đương với =A1&","&A2&","&A3&","&A4&","&A5

   *** Ứng dụng: Hàm TEXTJOIN được sử dụng nhiều trong ghép chuỗi ký tự để liệt kê danh sách vào 1 ô

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

NHÓM HÀM VĂN BẢN VÀ CHUỖI KÝ TỰ TRONG EXCEL (PHẦN 2)

 Trong phần 1 về nhóm hàm văn bản và chuỗi ký tự đã nói về các hàm xử lý text có 1 tham số đưa vào. Trong bài viết này sẽ tiếp tục chia sẻ về nhóm hàm văn bản sử dụng 2,3 tham số trong hàm:

Hàm LEFT

   *** Cấu trúc hàm: =LEFT(text,[num_chars])

   *** Cách sử dụng hàm LEFT: Hàm LEFT dùng để lấy đoạn ký tự bên trái của 1 chuỗi. Ví dụ: =LEFT(A1,3). Giả sử ô A1 là EXCEL thì hàm LEFT sẽ có kết quả là: EXC => Tức lấy bên trái 3 ký tự. Trường hợp nếu tham số thứ 2 của hàm LEFT không điền thì sẽ được mặc định là 1 => Tức lấy 1 ký tự bên trái

   *** Ứng dụng: Hàm LEFT là hàm phổ biến được ứng dụng nhiều trong tách dữ liệu, trong các đề thi test excel

Hàm RIGHT

   *** Cấu trúc hàm: =RIGHT(text,[num_chars])

   *** Cách sử dụng hàm RIGHT: Hàm LEFT dùng để lấy đoạn ký tự bên phải của 1 chuỗi. Ví dụ: =RIGHT(A1,3). Giả sử ô A1 là EXCEL thì hàm RIGHT sẽ có kết quả là: CEL => Tức lấy bên trái 3 ký tự. Trường hợp nếu tham số thứ 2 của hàm RIGHT không điền thì sẽ được mặc định là 1 => Tức lấy 1 ký tự bên phải

   *** Ứng dụng: Hàm RIGHT là hàm phổ biến được ứng dụng nhiều trong tách dữ liệu, trong các đề thi test excel

Hàm REPT

   *** Cấu trúc hàm: =REPT(text,number_times)

   *** Cách sử dụng hàm REPT: Hàm REPT dùng để nhân bản chuỗi ký tự lên nhiều lần. Ví dụ: REPT("A",5) thì kết quả trả về là: AAAAA

  *** Ứng dụng: Hàm REPT được ứng dụng nhiều trong lồng ghép xử lý ngắt chuỗi nâng cao

Hàm MID

   *** Cấu trúc hàm: =MID(text,start_num,num_chars)

   *** Cách sử dụng hàm MID: Hàm MID dùng để lấy đoạn ký tự ở giữa của 1 chuỗi. Ví dụ: MID(A1,2,3). Giả sử A1 là EXCEL thì hàm MID sẽ có kết quả là XCE => Tức là lấy 3 ký tự ở giữa nhưng lấy từ ký tự số 2. Nếu tham số thứ 2 là 1 thì sẽ tương đương với hàm LEFT. 

   *** Ứng dụng: Hàm MID là hàm phổ biến được dùng nhiều trong tách dữ liệu, đề thi excel

Hàm TEXT

   *** Cấu trúc hàm TEXT: =TEXT(value,format_text)

   *** Cách sử dụng hàm TEXT: Hàm Text dùng để chuyển dữ liệu là con số, ngày tháng, thời gian sang dữ liệu là TEXT. Ví dụ: =TEXT(A1,"#,##0"). Giả sử ô A1 là 1000000 thì kết quả trả về là: 1,000,000. Khi sử dụng hàm TEXT thì phải nắm bắt được các mẫu định dạng của Excel. Chia sẻ 1 số mẫu hay dùng trong tham số thứ 2 của hàm TEXT:

      "#,##0" => Đưa con số không có phần thập phân về chuỗi text

      "#,##0.00" => Đưa con số có 2 số thập phân về chuỗi text

      "dd/mm/yyyy" => Đưa ngày tháng về chuỗi Text

   *** Ứng dụng: Ứng dụng rất nhiều trong ghép chuỗi mà vẫn giữ được format khi ghép hoặc dùng xử lý dữ liệu trước khi đưa vào trong Mailmerge

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Công thức Excel tách họ và tên ngắn gọn

 Trong quản trị thông tin nhân sự, nhân khẩu, học sinh, sinh viên...thì thường phát sinh tình huống cần tách tên và họ ra các cột riêng biệt để thuận tiện cho việc sắp xếp dữ liệu, đặt tên email,...

Bài viết sẽ chia sẻ 1 công thức tách họ và tên rất ngắn và dễ dùng như sau:

Giả sử ô A1 là Họ và tên. Ví dụ: Nguyễn Thị Mai Nở Hoa

Lập công thức ở ô B1 kết quả là: Nguyễn Thị Mai Nở

Lập công thức ở ô C1 kết quả là: Hoa

Công thức ở ô C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",9)),9))

Công thức ở ô B1: =LEFT(A1,LEN(A1)-LEN(C1)-1)

Trường hợp công thức ô B1 không phụ thuộc vào ô C1 để tách ra họ và tên lót thì công thức là:

=LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",9)),9)))-1)

Như vậy, người dùng chỉ cần thay địa chỉ A1 bằng chính ô dữ liệu trên bảng tính thì sẽ thực hiện xong việc tách họ và tên bằng công thức rất đơn giản, ngắn gọn và thuận tiện

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

NHÓM HÀM VĂN BẢN VÀ CHUỖI KÝ TỰ TRONG EXCEL (PHẦN 1)

Nhóm hàm văn bản và chuỗi ký tự trong Excel (Phần 1)

Nhóm hàm về chuỗi văn bản trong Excel là một nhóm hàm khá thú vị. Có những tình huống rất dễ để hiểu và nhớ hàm, nhưng có những tình huống rất khó như: Tách chuỗi ký tự, tìm kiếm...

Nhóm hàm văn bản trong Excel gồm có các hàm tiêu biểu như: LEFT, RIGHT, MID, LEN, TRIM, UPPER, PROPER, LOWER, FIND, SEARCH, SUBSTITUTE, REPLACE, REPT, T, TEXT. Đối với Office phiên bản cao hơn thì có các hàm bổ trợ như: TEXTJOIN, CONCAT, LET

1. Đặc điểm nhận dạng giá trị văn bản trong Excel

- Excel mặc định giá trị văn bản đứng bên trái của ô.

- Đối với dữ liệu con số, ngày tháng, thời gian mà nhập ở dạng văn bản thì thường có dấu nháy đơn (') ở trước hoặc Excel sẽ có cảnh báo màu xanh ở góc trên cùng bên tay trái của ô.

2. Các hàm văn bản có 1 tham số trong Excel

- Hàm LEN: Là hàm đo độ dài chuỗi ký tự của 1 ô. Kết quả hàm này trả về là 1 con số => Tức số ký tự trong ô đó. Cấu trúc của hàm LEN là: =LEN(text). 

Ví dụ: =LEN("Excel Thỉnh Vũ") sẽ được kết quả trả về là 15. Trong đó khoảng trống (Space) cũng là ký tự

- Hàm UPPER: Là hàm chuyển đổi chuỗi ký tự của 1 ô sang chữ in hoa. Kết qua rhàm này trả về là 1 chuỗi ký tự được viết hoàn toàn bằng chữ in hoa. Cấu trúc của hàm UPPER là: =UPPER(text).

Ví dụ: =UPPER("Excel Thỉnh Vũ") sẽ được kết quả trả về là: EXCEL THỈNH VŨ

- Hàm PROPER: Là hàm chuyển đổi chuỗi ký tự của 1 ô sang chuỗi văn bản  có chữ cái đầu tiên của mỗi từ được viết hoa, các chữ cái còn lại sẽ được viết thường. Cấu trúc hàm PROPER là: =PROPER(text)

Ví dụ: =PROPER("eXcel thỉnh vũ") sẽ được kết quả trả về là: Excel Thỉnh Vũ

- Hàm LOWER: Là hàm chuyển đổi chuỗi ký tự của 1 ô sang toàn bộ là chữ thường. Kết quả hàm này trả về là một chuối kỹ tự hoàn toàn được viết thường. Cấu trúc của hàm LOWER là: =LOWER(text)

Ví dụ: =LOWER("EXCEL Thỉnh Vũ") sẽ được kết quả trả về là: excel thỉnh vũ

- Hàm TRIM: Là hàm loại bỏ khoảng trống thừa trong chuỗi ký tự. Hàm này có tính ứng dụng rất cao trong thực tế khi dùng để xử lý dữ liệu do người dùng gõ thừa dấu cách nên khi dùng các hàm excel khác như VLOOKUP, SUMIFS...làm kết quả tính toán bị sai thiếu hoặc lỗi. Cấu trúc hàm TRIM: =TRIM(text)

Ví dụ: =TRIM("   Excel   Thỉnh   Vũ    ") sẽ được kết quả trả về là: Excel Thỉnh Vũ

- Hàm T: Là hàm xét giá trị là chuỗi hay là không phải chuỗi văn bản. Kết quả hàm này trả về là chính chuỗi văn bản đó hoặc trả về rỗng. Tức là: Nếu giá trị là chuỗi văn bản thì hàm T trả về chính văn bản đó, nhưng nếu giá trị là con số, ngày tháng, thời gian thì hàm T trả về kết quả là rỗng

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết