Bạn hãy thật kiên nhẫn, đọc hết câu chuyện cổ tích ngày nay sau đây nhé:

Thuở xưa, ở vương quốc Excel xa xôi, có hai anh em là T2Column và SUBSTITUTE. T2Column, người anh chiếm đoạt hết của cải tài sản do cha mẹ để lại, đồng thời lại lấy được vợ giàu sang nên cuộc sống rất dư dả, rất sung túc. Trái lại người em SUBSTITUTE vì không muốn tranh giành với người anh, nên đời sống khá chật vật. Hàng ngày, SUBSTITUTE vẫn phải lên rừng đốn củi đem về để bán. Thấy em nghèo túng, T2Column không những không thương xót mà còn khinh ra mặt.

Hôm ấy như thường lệ, SUBSTITUTE lên rừng đi lấy củi. Xong việc, chàng đang định lên đường về lại nhà bỗng nhìn thấy cát bụi mịt mù từ đàng xa, rồi kế đến là tiếng vó ngựa dồn dập mỗi lúc một gần. SUBSTITUTE hoảng hốt, dắt lừa vào núp trong một bụi rậm, nhưng vẫn để ý nghe ngóng.

SUBSTITUTE đếm được trước sau 40 người và 40 ngựa. Người nào cũng tháo gỡ một chiếc túi lớn từ trên lưng hay từ hai bên hông ngựa xuống; có người thì hai túi, kẻ ba túi… Đoàn người cùng nhau tiến tới sát bên tảng đá. Người đi đầu, có lẽ là thủ lãnh của đám người bỗng ra dấu cho tất cả dừng lại trước rồi giơ hai tay lên trời nói lớn: “– VỪNG ƠI MỞ RA!“

Lạ lùng thay, tảng đá lâu nay, SUBSTITUTE đi ngang qua không biết bao nhiêu lần mà không bao giờ để ý như có một cánh cửa tự động mở ra, và bên trong sâu hun hút tựa như là một hang động. Đoàn người ôm hành trang cùng vào tất cả bên trong. Người thủ lãnh là người đi sau cùng. Y lại giơ hai tay lên trời nói: “– Vừng ơi, hãy đóng lại!“. Lập tức cánh cửa đóng lại…

SUBSTITUTE trong câu chuyện trên có giống Alibaba không? Vậy thì câu chuyện cổ tích này có dùng được gì trong Excel?

• Xem nhanh

  Toggle

  SUBSTITUTE là ai?

[alert-success]SUBSTITUTE(text, old_text, new_text, [instance_num])[/alert-success]

Cú pháp hàm SUBSTITUTE có các đối số sau đây:

Text: Bắt buộc. Văn bản hoặc tham chiếu đến ô chứa văn bản mà bạn muốn thay thế các ký tự trong đó.

Old_text: Bắt buộc. Văn bản mà bạn muốn được thay thế.

New_text: Bắt buộc. Văn bản mà bạn muốn thay thế cho old_text.

Instance_num: Tùy chọn. Xác định số lần xuất hiện của old_text mà bạn muốn thay bằng new_text. Nếu bạn xác định instance_num, thì số lần xuất hiện đó của old_text được thay thế. Nếu không, mọi lần xuất hiện của old_text trong văn bản được đổi thành new_text.

• SUBSTITUTE thường đi đốn củi, nên sẽ biết được có bao nhiêu củi.

Với một danh sách Họ tên có chiều dài khác nhau, số chữ trong tên cũng khác nhau.
Làm thế nào bạn biết tên họ có bao nhiêu chữ?
Dùng đếm tay đại pháp ta có thể tính được, “Nguyễn A” có 2 chữ, “Trần Văn B”, có 3 chữ. Vậy Excel làm thế nào để đếm?
Xin giới thiệu các bạn, anh SUBSTITUTE nghèo khó vừa bị trục xuất khỏi vương quốc xa xôi vì quá tỉnh, và quá đẹp trai.

– SUBSTITUTE đã làm gì trong hàm trên? Chàng ấy làm việc đi đốn củi, đơn giản là tìm khoảng trắng và chặt đi. SUBSTITUTE(“Nguyễn A”) sẽ trả về kết quả là: “NguyễnA”.

– Vậy còn Hàm LEN là gì? Có giống nàng Bân dệt áo len cho chồng không? Không, chắc chắn rồi, LEN có tác dụng đếm số ký tự trong chuỗi. LEN(“Nguyễn A”) sẽ trả về 8 ký tự, vậy thì LEN(“NguyễnA”) sẽ là 7. 8-7=1

• SUBSTITUTE và 40 tên cướp sẽ như thế nào?

Hàm REPT là gì? REPT là thần chú đại pháp, dùng để lặp lại số từ đã cho. Trong ảnh, là lặp lại 40 lần khoảng trắng.

Cụ thể diễn giải thế nào? SUBSTITUTE tìm khoảng trắng trong chuỗi, và thay thế nó bằng 40 ký tự khoảng trắng. Giống như Alibaba, thấy 40 tên cướp đi vào hàng động vậy thôi.

• SUBSTITUTE dùng câu thần chú để mở ra hang động, vào đó lấy vàng bạc ra, sau đó thì đóng lại.

  

TRIM là vợ của SUBSTITUTE, người đã giúp hạ đi những tên cướp trong truyện cổ tích. TRIM loại bỏ đi 40 tên cướp, chừa lại 1 tên thủ lĩnh. TRIM(“chữ[40 khoảng trắng]số”) => “chữ[1 khoảng trắng]số”. Nếu nhóm 40 tên cướp đứng đầu hoặc cuối thì thanh lý sạch sẽ: TRIM(“[40 khoảng trắng]ký tự bất kỳ”) => “ký tự bất kỳ”.

Vậy còn hàm MID làm gì? MID làm nhiệm vụ chia gia tài, khi chuỗi ký tự, bạn đã thêm vào lần lượt 40 ký tự khoảng trắng, ví dụ: “Nguyễn[40 khoảng trắng]A”, khi đó sẽ là 47 ký tự, và để lấy họ ra, ta dùng hàm MID(“Nguyễn[40 khoảng trắng]A”,1,40) => kết quả ta có, “Nguyễn[34 khoảng trắng]”. Dùng hàm TRIM, chúng ta bỏ hết khoảng trắng, ra kết quả chỉ là “Nguyễn”.

Ngoài ra, ta có cách vận dụng hàm SUBSTITUTE kết hợp LEFT/RIGHT để tách Họ và tên ra khỏi chuỗi.

• Lời kết

Và lời kết, chúng ta dùng SUBSTITUTE trong trường hợp nào? Rất nhiều áp dụng, tương ứng khi vận dụng hàm. Bạn còn nhớ anh T2Column của SUBSTITUTE chứ? Anh ta chính là “Text to Columns” trong Excel, với nhiều tính năng “giàu có”. Và còn người vợ của anh ta, bạn biết Flash Fill chứ?

Nhưng, hãy xem SUBSTITUTE làm được gì nhé!

1. Tách chữ theo vị trí đầu tiên với LEFT, cuối với RIGHT, bất kỳ vị trí nào với MID:
   Ví dụ bạn có dãy số “55 77 99”, muốn tách bên trái, và bên phải ra thì ta vận dụng hàm LEFT/RIGHT. Còn dấu trừ trừ (–, hai dấu trừ) dùng để chuyển đổi chữ thành số, nếu dữ liệu cần tách là chữ, thì bạn không cần phải có 2 dấu trừ trừ này. Với hàm MID, trước tiên ta ghép vào phía trước một khoảng trắng, để có thể dễ dàng tách ra vị trí mong muốn từ trái sang (index).
   
2. Tách cụm chữ giữa các ký tự phân cách:
   Ví dụ ta muốn lấy cụm chữ “123456789” từ cụm sau: 190309-123456789-012345, thì làm thế nào? Đầu tiên, ta ghép một dấu gạch nối vào đầu chuỗi, và mọi việc sẽ đơn giản như sau:
   
3. Tách địa chỉ, ra từng phần Xã/Phường, Huyện/Quận, Tỉnh/Thành phố.
   Ủa, sao chỗ này lại là 80? Bạn đã quên 40 con ngựa rồi.
4. Đếm số lượng từ xuất hiện trong ô, ví dụ như đếm có bao nhiêu Bò, Gà, Chó trong ô.