Cách tách 2 chữ cái đầu tiên trong excel

Excel có một bộ Hàm TEXT có thể làm nên điều kỳ diệu. Bạn có thể thực hiện tất cả các loại thao tác cắt lát văn bản và xúc xắc bằng các chức năng này

Một trong những nhiệm vụ phổ biến đối với những người làm việc với dữ liệu văn bản là trích xuất một chuỗi con trong Excel [i. e. , lấy một phần văn bản từ một ô]

Thật không may, không có hàm chuỗi con nào trong Excel có thể thực hiện việc này một cách dễ dàng. Tuy nhiên, điều này vẫn có thể được thực hiện bằng cách sử dụng công thức văn bản cũng như một số tính năng Excel tích hợp khác

Hướng dẫn này bao gồm

• Hàm văn bản Excel
• Trích xuất một chuỗi con trong Excel bằng hàm
  • Ví dụ 1 – Trích xuất tên người dùng từ Id email
  • Ví dụ 2 – Trích xuất Tên miền từ Id Email
  • Ví dụ 3 – Trích xuất Tên miền từ Id Email [không có. com]
• Sử dụng Văn bản thành Cột để Trích xuất Chuỗi con trong Excel
• Sử dụng TÌM và THAY THẾ để trích xuất văn bản từ một ô trong Excel
  • Ví dụ 1 – Trích xuất tên người dùng từ Id email
  • Ví dụ 2 – Trích xuất Tên miền từ Id Email

Trước tiên chúng ta hãy xem một số chức năng văn bản mà chúng ta sẽ sử dụng trong hướng dẫn này

Hàm văn bản Excel

Excel có nhiều hàm văn bản giúp dễ dàng trích xuất một chuỗi con từ văn bản gốc trong Excel. Dưới đây là các hàm Văn bản Excel mà chúng tôi sẽ sử dụng trong hướng dẫn này

• chức năng QUYỀN. Trích xuất số lượng ký tự được chỉ định từ bên phải của chuỗi văn bản
• hàm TRÁI. Trích xuất số lượng ký tự được chỉ định từ bên trái của chuỗi văn bản
• chức năng MID. Trích xuất số lượng ký tự được chỉ định từ vị trí bắt đầu được chỉ định trong chuỗi văn bản
• chức năng TÌM. Tìm vị trí bắt đầu của văn bản được chỉ định trong chuỗi văn bản
• LEN chức năng. Trả về số ký tự trong chuỗi văn bản

Trích xuất một chuỗi con trong Excel bằng hàm

Giả sử bạn có một tập dữ liệu như hình bên dưới

Đây là một số id email ngẫu nhiên [nhưng siêu anh hùng] [ngoại trừ của tôi] và trong các ví dụ bên dưới, tôi sẽ chỉ cho bạn cách trích xuất tên người dùng và tên miền bằng các hàm Văn bản trong Excel

Ví dụ 1 – Trích xuất tên người dùng từ Id email

Trong khi sử dụng các hàm Văn bản, điều quan trọng là phải xác định một mẫu [nếu có]. Điều đó làm cho nó thực sự dễ dàng để xây dựng một công thức. Trong trường hợp trên, mẫu là ký hiệu @ giữa tên người dùng và tên miền và chúng tôi sẽ sử dụng mẫu này làm tham chiếu để lấy tên người dùng

Đây là công thức để có được tên người dùng

=LEFT[A2,FIND["@",A2]-1]

Công thức trên sử dụng hàm LEFT để trích xuất tên người dùng bằng cách xác định vị trí của dấu @ trong id. Điều này được thực hiện bằng cách sử dụng hàm FIND, trả về vị trí của ký tự @

Ví dụ: trong trường hợp của brucewayne@batman. com, FIND[“@”,A2] sẽ trả về 11, là vị trí của nó trong chuỗi văn bản

Bây giờ chúng ta sử dụng hàm LEFT để trích xuất 10 ký tự từ bên trái của chuỗi [ít hơn một ký tự so với giá trị được trả về bởi hàm LEFT]

Ví dụ 2 – Trích xuất Tên miền từ Id Email

Logic tương tự được sử dụng trong ví dụ trên có thể được sử dụng để lấy tên miền. Một điểm khác biệt nhỏ ở đây là chúng ta cần trích xuất các ký tự từ bên phải của chuỗi văn bản

Đây là công thức sẽ làm điều này

=RIGHT[A2,LEN[A2]-FIND["@",A2]]

Trong công thức trên, chúng tôi sử dụng logic tương tự, nhưng điều chỉnh nó để đảm bảo rằng chúng tôi đang nhận được chuỗi chính xác

Một lần nữa hãy lấy ví dụ về brucewayne@batman. com. Hàm FIND trả về vị trí của dấu @, trong trường hợp này là 11. Bây giờ, chúng ta cần trích xuất tất cả các ký tự sau @. Vì vậy, chúng tôi xác định tổng độ dài của chuỗi và trừ đi số ký tự cho đến dấu @. Nó cho ta số ký tự bao ngoài tên miền bên phải

Bây giờ chúng ta chỉ cần sử dụng hàm RIGHT để lấy tên miền

Ví dụ 3 – Trích xuất Tên miền từ Id Email [không có. com]

Để trích xuất một chuỗi con từ giữa một chuỗi văn bản, bạn cần xác định vị trí của dấu ngay trước và sau chuỗi con

Chẳng hạn như trong ví dụ bên dưới, để lấy tên miền mà không cần. phần com, dấu sẽ là @ [ngay trước tên miền] và. [ngay sau nó]

Đây là công thức sẽ chỉ trích xuất tên miền

=MID[A2,FIND["@",A2]+1,FIND[".",A2]-FIND["@",A2]-1] 

Hàm Excel MID trích xuất số lượng ký tự được chỉ định từ vị trí bắt đầu được chỉ định. Trong ví dụ trên, FIND[“@”,A2]+1 chỉ định vị trí bắt đầu [ngay sau dấu @] và FIND[“. ”,A2]-FIND[“@”,A2]-1 xác định số ký tự giữa ‘@‘ và ‘. ‘

Cập nhật. Một trong những độc giả William19 đã đề cập rằng công thức trên sẽ không hoạt động trong trường hợp có dấu chấm[. ] trong id email [ví dụ: bruce. wayne@batman. com]. Vì vậy, đây là công thức để đối phó với những trường hợp như vậy

=MID[A1,FIND["@",A1]+1,FIND[".",A1,FIND["@",A1]]-FIND["@",A1]-1]

Sử dụng Văn bản thành Cột để Trích xuất Chuỗi con trong Excel

Sử dụng các hàm để trích xuất một chuỗi con trong Excel có ưu điểm là động. Nếu bạn thay đổi văn bản gốc, công thức sẽ tự động cập nhật kết quả

Nếu đây là thứ bạn có thể không cần, thì sử dụng tính năng Văn bản thành Cột có thể là một cách nhanh chóng và dễ dàng để chia văn bản thành các chuỗi con dựa trên các điểm đánh dấu đã chỉ định

Đây là cách để làm điều này

• Chọn các ô nơi bạn có văn bản
• Chuyển đến Dữ liệu -> Công cụ dữ liệu -> Văn bản thành cột
• Trong Text to Column Wizard Bước 1, chọn Delimited và nhấn Next
• Ở Bước 2, hãy chọn tùy chọn Khác và nhập @ vào ô bên phải tùy chọn đó. Đây sẽ là dấu phân cách mà Excel sẽ sử dụng để chia văn bản thành các chuỗi con. Bạn có thể xem bản xem trước Dữ liệu bên dưới. Nhấp vào Tiếp theo
• Ở Bước 3, Cài đặt chung hoạt động tốt trong trường hợp này. Tuy nhiên, bạn có thể chọn một định dạng khác nếu bạn đang tách số/ngày. Theo mặc định, ô đích là nơi bạn có dữ liệu gốc. Nếu bạn muốn giữ nguyên dữ liệu ban đầu, hãy thay đổi dữ liệu này sang một số ô khác
• Nhấp vào Kết thúc

Điều này sẽ ngay lập tức cung cấp cho bạn hai bộ chuỗi con cho mỗi id email được sử dụng trong ví dụ này

Nếu bạn muốn chia nhỏ văn bản hơn nữa [ví dụ: chia tách batman. com thành batman và com], lặp lại quy trình tương tự với nó

Sử dụng TÌM và THAY THẾ để trích xuất văn bản từ một ô trong Excel

TÌM và THAY THẾ có thể là một kỹ thuật hiệu quả khi bạn làm việc với văn bản trong Excel. Trong các ví dụ bên dưới, bạn sẽ học cách sử dụng TÌM và THAY THẾ bằng các ký tự đại diện để làm những điều tuyệt vời trong Excel

See Also: Learn All about Wildcard Characters in Excel.

Hãy cùng lấy ví dụ về id Email

Ví dụ 1 – Trích xuất tên người dùng từ Id email

Dưới đây là các bước để trích xuất tên người dùng từ Id Email bằng chức năng Tìm và Thay thế

• Sao chép và dán dữ liệu gốc. Vì Tìm và Thay thế hoạt động và thay đổi dữ liệu mà nó được áp dụng, tốt nhất là bạn nên sao lưu dữ liệu gốc
• Chọn dữ liệu và vào Home -> Editing -> Find & Select -> Replace [hoặc dùng phím tắt Ctrl + H]
• Trong hộp thoại Tìm và Thay thế, hãy nhập thông tin sau
  • Tìm gì. @*
  • Thay thế bằng. [để trống phần này]
• Nhấp vào Thay thế tất cả

Điều này sẽ ngay lập tức xóa tất cả văn bản trước @ trong id email. Bạn sẽ có kết quả như hình bên dưới

Làm thế nào điều này hoạt động ?? . Dấu hoa thị [*] là ký tự đại diện đại diện cho bất kỳ số lượng ký tự nào. Do đó, @* có nghĩa là, một chuỗi văn bản bắt đầu bằng @ và có thể có bất kỳ số lượng ký tự nào sau nó. Ví dụ như trong brucewayne@batman. com, @* sẽ là @batman. com. Khi chúng tôi thay thế @* bằng khoảng trống, nó sẽ xóa tất cả các ký tự sau @ [bao gồm cả @]

Ví dụ 2 – Trích xuất Tên miền từ Id Email

Sử dụng logic tương tự, bạn có thể sửa đổi tiêu chí 'Tìm gì' để lấy tên miền

Dưới đây là các bước

• Chọn dữ liệu
  
• Vào Trang chủ -> Chỉnh sửa -> Tìm & Chọn -> Thay thế [hoặc sử dụng phím tắt Ctrl + H]
• Trong hộp thoại Tìm và Thay thế, hãy nhập thông tin sau
  • Tìm gì. *@
  • Thay thế bằng. [để trống phần này]
• Nhấp vào Thay thế tất cả

Điều này sẽ ngay lập tức xóa tất cả văn bản trước @ trong id email. Bạn sẽ có kết quả như hình bên dưới

Bạn cũng có thể thích các Hướng dẫn Excel sau

• Cách đếm các ô chứa chuỗi văn bản
• Trích xuất tên người dùng từ Id email trong Excel [2 phương pháp]
• Hàm Excel [Ví dụ + Video]
• Khai thác thêm tính năng Tìm và Thay thế trong Excel
• Cách viết hoa chữ cái đầu tiên của chuỗi văn bản trong Excel
• Cách trích xuất từ ​​đầu tiên từ chuỗi văn bản trong Excel
• Tách văn bản và số trong Excel

14 suy nghĩ về "Cách trích xuất chuỗi con trong Excel [Sử dụng công thức TEXT]"

1. Nitin

   Tháng Tư 2020 lúc 1. 36 giờ chiều

   Bajlahlah/ljahlkja/12345/jakj;akj/abc
   bất kỳ công thức nào để trích xuất 12345 từ chuỗi

2. moidul haque

   Tháng mười hai 2019 tại 1. 33 giờ chiều

   Cột A Cột B Cột C

   Inv Không. PC Làm cách nào tôi có thể nhận được câu trả lời trên cột B
   bằng cách sử dụng Công thức Excel trong Cột C
   18JH42A330EN00710076 A330 = Công thức sẽ là gì??
   18CH2S011S0047 S011
   18JK2T007G0111 T007
   18TN2O004G0280 O004
   18PM080154WB0648 PM08

3. Akinsanya Oyebode

   Tháng Mười 2019 lúc 6. 29 giờ tối

   Làm thế nào để bạn kiểm tra một văn bản cụ thể trong một ô; . Cảm ơn

4. JunHan Yee

   Tháng Giêng 2019 lúc 3. 28 giờ chiều

   Cảm ơn vì điều này đã giúp nhóm của tôi thực hiện tìm kiếm từ văn bản đến dữ liệu

5. Thiyagarajan Devarajan

   Tháng hai 2016 tại 5. 05 giờ chiều

   Tôi đang sử dụng menu Chuyển văn bản thành cột cho việc này, nhưng tôi không biết sử dụng công thức, nó rất phức tạp, Cảm ơn Sumit, Nó rất hữu ích

   • Sumit Bansal

     Tháng Ba 2016 lúc 1. 06 giờ sáng

     Cảm ơn Thiyagarajan. Rất vui vì bạn thấy điều này hữu ích 🙂

6. Anand Kumar

   Tháng hai 2016 lúc 11. 30 giờ sáng

   Bí quyết đẹp………………

   • Sumit Bansal

     Tháng hai 2016 tại 8. 23 giờ chiều

     Cảm ơn đã bình luận Anand. Rất vui vì bạn thích nó 🙂

7. Rudra

   Tháng hai 2016 tại 9. 46 giờ sáng

   Sumit,
   Tôi sử dụng các thủ thuật này hầu như hàng ngày. Rất vui khi bạn chia sẻ với thế giới.

   • Sumit Bansal

     Tháng hai 2016 tại 8. 23 giờ chiều

     Cảm ơn vì đã nhận xét Rudra 🙂

8. William19

   Tháng hai 2016 lúc 10. 15 giờ chiều

   Xuất sắc. Thêm một lưu ý rằng “Ví dụ 3 – Trích xuất Tên miền từ Id Email [không có. com]” sẽ không hoạt động nếu địa chỉ email có dấu chấm [. ] trong đó

   • Sumit Bansal

     Tháng hai 2016 lúc 11. 17 giờ chiều

     Cảm ơn vì đã chỉ ra điều này William. Tôi đã cập nhật bài viết với một ghi chú và công thức có liên quan