Hàm Xóa, Kết Hợp, Thay Thế Chuỗi/ký Tự Trong Excel - CTG Blog

Trang chủ » Xóa Bớt Ký Tự Trong Excel » Hàm Xóa, Kết Hợp, Thay Thế Chuỗi/ký Tự Trong Excel - CTG Blog

Có thể bạn quan tâm

February 26, 2015

Hàm xóa, kết hợp, thay thế chuỗi/ký tự trong Excel Tags: Góc máy tính Thủ thuật văn phòng On February 26, 2015 Author: CTG blog Hàm xóa, kết hợp, thay thế chuỗi/ký tự trong Excel 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.
  • Ô B2: caytamgui
  • Mình muốn lấy chữ "tam" (gồm 3 ký tự và bắt đầu lấy từ ký tự thứ 4), còn lại không lấy.
  • Công thức hàm sẽ như sau: C2=MID(B2,4,3)
  • Kết quả ô C2: tam
- Ví dụ 2: Xóa bớt các ký tự ở cuối ô dữ liệu. Hàm xóa chuỗi/ký tự trong Excel
  • Ô B2: http://caytamgui.blogspot.com
  • Mình muốn bỏ bớt đi ".com", đếm ở đây là 4 ký tự, và lấy hết các phần còn lại tính từ trái qua đến chỗ bị xóa đi 4 ký tự thì ngừng không lấy nữa.
  • Công thức hàm sẽ như sau: C2=LEFT(B2, LEN(B2)-4)
  • Kết quả ô C2: http://caytamgui.blogspot
- Ví dụ 3: Xóa bớt các ký tự ở đầu ô dữ liệu. Hàm xóa chuỗi/ký tự trong Excel
  • Ô B3: http://caytamgui.blogspot.com
  • Mình muốn bỏ bớt đi "http://", đếm ở đây là 7 ký tự, và lấy hết các phần còn lại tính từ phải qua đến chỗ bị xóa đi 7 ký tự thì ngừng không lấy nữa.
  • Công thức hàm sẽ như sau: C3=RIGHT(B3, LEN(B3)-7)
  • Kết quả ô C3: caytamgui.blogspot.com

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

- Công thức: =SUBSTITUTE(text,"old_text","new_text",instance_num) - Trong đó:
  • text: Chuỗi, ký tự hay ô chứa chuỗi dữ liệu cần thay thế.
  • old_text: Chuỗi, ký tự muốn thay thế.
  • new_text: Chuỗi, ký tự mới muốn thay thế cho old_text.
  • instance_num: Mình không biết định nghĩa sao, nhưng ví dụ đơn giản thế này, có 7 số 4 trong chuỗi dữ liệu, bạn chỉ muốn thay thế số 4 xuất hiện lần thứ 2 trong chuỗi thành số khác thì bạn để instance_num là 2. Còn nếu bạn bỏ qua instance_num thì mặc định 7 số 4 trong chuỗi dữ liệu này đều bị thay thế hết.
VÍ DỤ: - Ví dụ 1: Thay thế một chuỗi ký tự. Hàm thay thế chuỗi/ký tự trong Excel
  • Ô B2: http://caytamgui.blogspot.com
  • Mình muốn thay thế "http://" thành "Site: "
  • Công thức hàm sẽ như sau: C2=SUBSTITUTE(B2,"http://","Site: ")
  • Kết quả ô C2: Site: caytamgui.blogspot.com
- Ví dụ 2: Kết hợp nhiều hàm SUBSTITUTE. Hàm thay thế chuỗi/ký tự trong Excel
  • Ô B3: http://pipi-wallpaper.blog.com
  • Mình muốn thay thế "http://" thành "Site: " & "pipi-wallpaper" thành "caytamgui" & "blog" thành "blogspot"
  • Công thức hàm sẽ như sau: C3=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"http://","Site: "),"pipi-wallpaper","caytamgui"),"blog","blogspot")
  • Kết quả ô C3: Site: caytamgui.blogspot.com
** Ở 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: Hàm dùng để kết hợp chuỗi dữ liệu, phép tính, text trong Excel
  • Ô 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 ^_^ Hàm xóa, kết hợp, thay thế chuỗi/ký tự trong Excel - 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. Bản quyền bài viết thuộc về/ CTG blog DMCA.com Protection Status
  • Share with Facebook
BÀI VIẾT LIÊN QUAN:

6 comments:

  1. LeduongAugust 27, 2015 at 5:51 PM

    Hay quá, cảm ơn bạn nhé!

    ReplyDeleteReplies
      Reply
  2. UnknownSeptember 28, 2017 at 9:00 AM

    GBD___03662.6__ Mình muốn bỏ hết các ký tự ___ để thành nguyên GBD03662.6 thì làm cách nào. Bạn nào chỉ giúp mình với mình cảm ơn

    ReplyDeleteReplies
    1. CTG blogSeptember 28, 2017 at 2:50 PM

      Dùng hàm thì cũng được nhưng cái này không cần thiết bạn. - Bạn bấm một lúc 2 phím Ctrl + H - Ở ô Find what: bạn gõ ký tự _ - Xong bạn nhấn Replace All › OK. Vậy là mất hết các ký tự _ rồi đó bạn.

      DeleteReplies
        Reply
      2. Reply
  3. tungmt2November 20, 2017 at 9:17 PM

    Mình muốn dùng hàm duy nhất (để click đúp) có thể xoá các dấu phẩy sau các chữ, nhưng không xoá dấu phẩy ở giữa các chữ thì làm thế nào nhỉ.VD có các ký tự sau:A,B,C,,,,,D,G,,S,H,O,Chỉ sử dụng 1 hàm để xoá hết các dấu phẩy sau chữ C G O thì làm thế nào nhỉ ( giữ nguyên giấu phẩy ở giữa )

    ReplyDeleteReplies
      Reply
  4. CTG blogNovember 21, 2017 at 7:24 PM

    Theo ví dụ của bạn:- A1: A,B,C,- B1: Dùng công thức: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"C,","C"),"G,","G"),"O,","O")- Kết quả ô B1: A,B,CCòn với "C,," hay "O,,," thì bạn lại thêm hàm substitue vào nhé.

    ReplyDeleteReplies
      Reply
  5. UnknownJanuary 23, 2018 at 8:58 AM

    NGUYỄN THỊ NGỌC BÍCH阮氏玉碧mình muốn bỏ phần chữ hoa thì làm sao ạ, tại vì danh sách có ng tên 2 chữ, 3 chữ,4 chữ.v.vvcảm ơn ạ

    ReplyDeleteReplies
      Reply
Add commentLoad more...

Xem nhiều

  • Hướng dẫn Auto click chuột nhiều vị trí bất kỳ trên máy tính Hướng dẫn Auto click chuột nhiều vị trí bất kỳ trên máy tính Auto Click là chương trình click chuột tự động trên màn hình desktop. Đây là chương trình hoàn toàn miễn phí, siêu gọn nhẹ và không cần ...
  • Cách bấm thổi các nốt nhạc trên sáo trúc 6 lỗ Cách bấm thổi các nốt nhạc trên sáo trúc 6 lỗ Sau một thời gian tập tành thổi sáo mình đã cho ra đời bài viết này với góc nhìn của một newbie ^^. Tài liệu trên mạng tuy có nhiều nhưng...
  • Hướng dẫn gộp nhiều file TXT (text) thành một Hướng dẫn gộp nhiều file TXT (text) thành một Bạn đang có nhiều file văn bản và bạn muốn gộp tất cả nội dung trong từng file văn bản đó vào một file duy nhất. Cách làm thông thường là...
  • Hướng dẫn tạo ảnh thẻ 3x4 và 4x6 bằng Photoshop Hướng dẫn tạo ảnh thẻ 3x4 và 4x6 bằng Photoshop Chỉ cần bạn có một chiếc điện thoại có camera và một máy tính có Photoshop, thì việc tự tay bạn tạo ảnh thẻ 3x4 hay 4x6 bằng Photoshop c...
  • Nguyên do chó hay ăn đất, cạp tường - Cách điều trị Nguyên do chó hay ăn đất, cạp tường - Cách điều trị Nếu bạn thấy một con chó thường xuyên ra sân đào bới rồi ăn luôn đất, cạp tường, ăn xi măng, vào toilet ăn xà bông, ăn phân, thì khả năng ...
  • Bảng chạy thử mã HTML, JavaScript (giống w3schools) Bảng chạy thử mã HTML, JavaScript (giống w3schools) Bảng chạy thử mã HTML/JavaScript (Tool test code HTML/JavaScript) là bảng dùng để chạy thử mã code HTML, hay mã code JavaScript xem có hoạ...
  • Hướng dẫn chơi game Neogeo trên máy tính Hướng dẫn chơi game Neogeo trên máy tính Neogeo là chương trình giả lập giúp bạn chơi game Neogeo trên máy tính . Nếu tuổi thơ bạn đã từng chơi qua các game thùng bỏ thẻ thì sẽ kh...
  • [$] Mặt nạ giấy 3D Cữu vĩ hồ ly - Kitsune - Demon Fox [$] Mặt nạ giấy 3D Cữu vĩ hồ ly - Kitsune - Demon Fox Chiếc mặt nạ hồ ly được làm bằng giấy theo phong cách low poly. Với chiếc mặt nạ hồ ly này, bạn có thể dùng để trang trí, phục vụ trong...
  • Hướng dẫn sử dụng Pen Tool vẽ một hình bất kỳ trong Photoshop Hướng dẫn sử dụng Pen Tool vẽ một hình bất kỳ trong Photoshop Pen Tool là một công cụ hỗ trợ vẽ tuyệt vời trong Photoshop. Chỉ cần nắm rõ cách sử dụng và thành thạo Pen Tool , bạn sẽ vẽ được bất cứ...
  • Thải Urat ở rùa là gì? Thải Urat quan trọng như thế nào? Thải Urat ở rùa là gì? Thải Urat quan trọng như thế nào? Thải Urat ở rùa là gì? Thải urat ở rùa nghĩa là khi rùa đi vệ sinh (đi tiểu hoặc đi ị), sẽ thải kèm theo một lượng nhỏ urat có màu trắng...
Rùa Chip

Từ khóa » Xóa Bớt Ký Tự Trong Excel