Một số hàm tách kí tự trong excel – P2

Ở phần 1 mình đã hướng dẫn cách tách phần đầu và phần cuối một chuỗi, trong bài này, mình sẽ hướng dẫn cách tách phần giữa chuỗi ký tự.

Hàm thường dùng nhất để tách phần giữa chuỗi kí tự là hàm Mid.

Cú Pháp:MID(text,start_num,num_chars)
– Trong Đó:

+ Text: Chuỗi ký tự gốc
+ start_num: Vị trí bắt đầu cần cắt chuỗi ký tự
+ num_chars: Số ký tự cần cắt ra từ chuỗi ký tự

– Chức Năng: Cắt ra n ký tự trong chuỗi text bắt đầu từ vị trí nhất định.

Ví dụ: Cắt ra 4 ký tự từ chuỗi “Microsoft Office” bắt đầu từ vị trí thứ 6.

Công thức cắt chuỗi sẽ là: MID(“Microsoft Office”,6,4) = soft

hoặc =MID(A1,6,4) như trong hình:

ex-ham-mid

Dùng hàm Mid để tách chuỗi kí tự

Chú ý:

  • Nếu số bắt đầu lớn hơn độ dài văn bản, thì hàm MID trả về văn bản trống
  • Nếu số bắt đầu nhỏ hơn độ dài văn bản, nhưng số bắt đầu cộng với số ký tự vượt quá độ dài văn bản, thì hàm MID trả về các ký tự đến cuối của văn bản
  • Nếu số bắt đầu nhỏ hơn 1, thì hàm MID trả về giá trị lỗi #VALUE!

*** Cách lấy nhóm kí tự giữa 2 kí tự đặc biệt trong chuỗi kí tự:

Để lấy nhóm kí tự đặc biệt giữa 2 dấu cách trong chuỗi, ta có thể kết hợp hàm Left, Right, Len, Find

Công thức:

=LEFT(RIGHT(A1,LEN(A1)-FIND(” “,A1)),FIND(” “,RIGHT(A1,LEN(A1)-FIND(” “,A1)))-1)

ex-ket-hop-ham-de-lay-nhom-ki-tu-giua-2-ki-tu-dac-biet-trong-chuoi

Kết hợp hàm để lấy nhóm kí tự giữa 2 kí tự đặc biệt trong chuỗi

Bạn có thể thay A1 bằng vị trí chuỗi, và dấu cách ” ” bằng các kí tự đặc biệt khác, chẳng hạn “*”, “(“, “)”….. tùy trường hợp.

*** Cách tách nhóm kí tự trong dấu ngoặc kép trong excel:

Công thức:

=MID(A1,SEARCH(“”””,A1)+1,SEARCH(“”””,A1,SEARCH(“”””,A1)+1)-SEARCH(“”””,A1)-1)

ex-ket-hop-ham-de-lay-nhom-ki-tu-giua-2-ki-tu-dac-biet-trong-chuoi-2

Kết hợp hàm để lấy nhóm kí tự giữa dấu ngoặc kép trong chuỗi

*** Hàm Search:

Công thức: SEARCH(find_text,within_text,[start_num])

Trong đó:

– find_text: là ký tự, văn bản mà các bạn muốn hàm SEARCH() tìm.

– within_text: văn bản, chuỗi ký tự để các bạn tìm kiếm find_text.

– start_num: vị trí bắt đầu tìm trong within_text.

Các chú ý với hàm Search:

– Hàm SEARCH() tìm kiếm không phân biệt chữ hoa chữ thường.

– Start_num được bỏ qua sẽ có giá trị mặc định là 1.

– Nếu start_num nhỏ hơn 0 hoặc lớn hơn độ dài của within_text, hàm trả về giá trị lỗi.

– Nếu không tìm thấy giá trị find_text hàm sẽ trả về giá trị lỗi.

– Các bạn có thể dùng các ký tự đại diện: dấu hỏi chấm (?) và dấu sao (*) trong đối số find_text.

– Sử dụng start_num để bỏ qua số ký tự đã xác định. Hàm luôn trả về số ký tự tính từ ký tự đầu tiên của within_text, nếu start_num lớn hơn 1 hàm sẽ đếm cả số ký tự các bạn bỏ qua.

Băng Hạ – 17/12/16

Một số hàm tách kí tự trong excel – P1

Bạn có thể dùng một số hàm để tách chuỗi kí tự trong chuỗi excel một cách dễ dàng.

1. Lấy nhóm kí tự đầu tiên trong chuỗi ký tự:

Để lấy n kí tự đầu tiên trong chuỗi ký tự bạn dùng hàm Left

Công thức: LEFT(text, [num_chars])

  • Text là chuỗi ký tự cần tách hoặc vị trí chuỗi kí tự cần tách
  • [num_chars] là số ký tự bạn muốn lấy (điền số)

Ví dụ: Muốn lấy 3 kí tự đầu tiên trong chuỗi “Password and Access Code” trong excel, bạn dùng hàm

=left(“Password and Access Code”,3)

hoặc =left(A1,3) để tách kí tự như trong ảnh:

ex-ham-left

Ví dụ về hàm LEFT – tách nhóm kí tự bên trái chuỗi kí tự

***Tách nhóm kí tự trước kí tự đặc biệt đầu tiên trong chuỗi

Cách 1: Khi bạn muốn lấy một nhóm kí tự trước kí tự đặc biệt đầu tiên bạn có thể dùng kết hợp hàm Left và hàm Find:

Công thức: =LEFT(Text,FIND([ki-tu-dac-biet],Text)-1)

Ví dụ: Tách lấy nhóm kí tự trước dấu cách đầu tiên trong chuỗi kí tự “Password and Access Code” bạn dùng hàm:

=left(“Password and Access Code”,find(” “,”Password and Access Code”)-1)

hoặc =left(A1,find(” “,A1)-1) để tách kí tự như trong ảnh:

ex-ket-hop-ham-left-va-ham-find

Kết hợp hàm left và hàm find để lấy nhóm kí tự đầu tiên trước dấu cách ” ” trong chuỗi

*** Thêm về hàm Find:

Hàm Find định vị một chuỗi văn bản nằm trong chuỗi văn bản gốc và trả về số thứ tự của vị trí bắt đầu của chuỗi văn bản đó tính từ ký tự thứ nhất của chuỗi văn bản gốc

Công thức: FIND(find_text, within_text, [start_num])

  • Find_text là kí tự muốn tìm
  • Within_text  là chuỗi kí tự gốc
  • Start_num là vị trí ký tự bắt đầu tìm. Ký tự thứ nhất của within_text là ký tự số 1. Nếu bỏ qua start_num, thì nó được mặc định là 1.

Cách 2: Khi bạn muốn lấy một nhóm kí tự trước kí tự đặc biệt đầu tiên bạn có thể dùng kết hợp hàm Mid và hàm Find:

Công thức: =MID(Text,1,FIND([ki-tu-dac-biet],Text)-1)

Ví dụ: Tách lấy nhóm kí tự trước dấu cách đầu tiên trong chuỗi kí tự “Password and Access Code” bạn dùng hàm:

=MID(“Password and Access Code”,1,FIND(” “,”Password and Access Code”,1)-1)

hoặc =MID(A1,1,FIND(” “,A1,1)-1) như trong ảnh:

ex-ket-hop-ham-mid-va-ham-find

Kết hợp hàm Mid và hàm Find để lấy nhóm ký tự trước kí tự đặc biệt đầu tiên trong chuỗi kí tự

2. Lấy nhóm kí tự cuối cùng trong chuỗi ký tự:

Để lấy n kí tự đầu tiên trong chuỗi ký tự bạn dùng hàm Right

Công thức: RIGHT(text, [num_chars])

  • Text là chuỗi ký tự cần tách hoặc vị trí chuỗi kí tự cần tách
  • [num_chars] là số ký tự bạn muốn lấy (điền số)

Ví dụ: Muốn lấy 4 kí tự cuối cùng trong chuỗi “Password and Access Code” trong excel, bạn dùng hàm

=right(“Password and Access Code”,4)

hoặc =right(A1,4) để tách kí tự như trong ảnh:

ex-ham-right

Ví dụ về hàm right – tách nhóm kí tự cuối cùng trong chuỗi kí tự

***Tách nhóm kí tự sau kí tự đặc biệt đầu tiên trong chuỗi

Khi bạn muốn lấy một nhóm kí tự sau kí tự đặc biệt đầu tiên bạn có thể dùng kết hợp hàm Right, hàm Len và hàm Find:

Công thức: =RIGHT(Text,LEN(Text)-FIND([ki-tu-dac-biet],Text))

Ví dụ: Tách lấy nhóm kí tự sau dấu cách đầu tiên trong chuỗi kí tự “Password and Access Code” bạn dùng hàm:

=RIGHT(“Password and Access Code”,LEN(“Password and Access Code”)-find(” “,”Password and Access Code”))

hoặc =RIGHT(A1,LEN(A1)-FIND(” “,A1)) để tách kí tự như trong ảnh:

ex-ket-hop-ham-right-ham-lend-va-ham-find

Kết hợp hàm Right, hàm Len và hàm Find để lấy nhóm kí tự sau kí tự đặc biệt đầu tiên

*** Thêm về hàm Len:

Hàm Lend dùng để đếm độ dài chuỗi kí tự

Công thức: LEN(text)

Text là chuỗi kí tự cần đếm, trong đó dấu cách (khoảng trống) vẫn được đếm là 1 kí tự

Băng Hạ – 12/9/16

Cách nhóm dòng, nhóm cột trong Excel

Khi bạn muốn ẩn dòng, ẩn cột và sau đó để hiện lại dòng và cột bạn, ngoài cách thông thường là ấn chuột phải vào dòng/cột chọn Hide/Unhide, bạn còn thể dùng cách nhóm dòng, nhóm cột.

Tình cờ, bạn mình gặp rắc rối khi tự dưng trong file excel đang dùng tự dưng xuất hiện thêm 2 dòng đánh số 1 và 2 phía trên thanh địa chỉ, trên đó còn có thêm dấu (-) hoặc (+). Khi ấn vào số 1 hoặc dấu (-) thì có một số cột bị ẩn đi, ngược lại khi ấn vào số 2 hoặc dấu (+) thì các cột vừa bị ẩn lại hiện trở lại. Bạn mình phân vân không biết làm sao để xử lý “lỗi” này và hỏi mình.

Mới đầu mình vào Options để tìm cách để gỡ bỏ nhưng không được. Sau đó, qua tìm hiểu mình biết được lý do 2 cột này xuất hiện là do bảng excel đang thực hiện lệnh nhóm cột. Và cách thực hiện việc nhóm cột cũng như gỡ bỏ nhóm cột này khá đơn giản.

I/ Cách nhóm dòng, nhóm cột

Đầu tiên bạn bôi đen các dòng/ cột cần ẩn sau đó vào Data > Group > Group.

cach-nhom-cot-trong-excel

Chọn Group để nhóm các cột

cach-nhom-dong-trong-excel

Chọn Group để nhóm các dòng

Khi đó màn hình exel của bạn sẽ hiện lên dấu (-) tại các dòng/cột mà bạn vừa chọn. Muốn ẩn dòng/cột thì bạn click chuột vào dấu (-) hoặc click chuột vào số 1 ở góc trên cùng bên trái màn hình.

an-vao-dau-tru-hoac-so-1-de-an-cot

Ấn vào dấu (-) hoặc số 1 để ẩn cột

an-vao-dau-tru-hoac-so-1-de-an-dong

Ấn vào dấu (-) hoặc số 1 để ẩn dòng

Nếu bạn muốn hiện lại các cột, bạn click chuột vào dấu (+) hoặc click chuột vào số 2 ở góc trên cùng bên trái màn hình.

an-vao-dau-cong-hoac-so-2-de-hien-thi-cac-cot

Ấn vào dấu (+) hoặc số 2 để hiển thị cột

an-vao-dau-cong-hoac-so-2-de-hien-thi-cac-dong

Ấn vào dấu (+) hoặc số 2 để hiển thị dòng

Lợi ích của phương pháp này là bạn có thể làm ẩn hiện cùng một lúc nhiều nhóm cột, dòng tuỳ theo thiết lập của bạn.

II/ Cách bỏ nhóm dòng, nhóm cột

Để bỏ nhóm cột, các bạn thực hiện như sau:

Đầu tiên bạn bôi đen các dòng/ cột cần ẩn sau đó vào Data > Ungroup > Ungroup.

cach-bo-nhom-cot-trong-excel

Cách bỏ nhóm cột

cach-bo-nhom-dong-trong-excel

Cách bỏ nhóm dòng

Chúc các bạn thành công!

Ấn Ctrl+F không ra Find and Replace?

Khi đang sử dụng Excel, do một lý do vô tình nào đó, khi bạn ấn tổ hợp phím Ctrl+F, trên màn hình không hiện ra hộp thoại Find and Replace mà lại hiện ra hộp thoại Find Computers.

loi-khi-an-Ctrl+F-Find-Computer

Hộp thoại Find Computers

Dù bạn đã đóng file Excel và mở lại nhưng khi ấn tổ hợp phím Ctrl+F, tình trạng trên vẫn xảy ra. Vậy trong trường hợp này, phải làm sao để vào hộp thoại Find and Replace?

Rất đơn giản, bạn chỉ cần ấn tổ hợp phím Windows+Ctrl+F, sau đó ấn lại tổ hợp phím Ctrl+F để mở ra hộp thoại Find and Replace. Nếu thực hiện một lần không được, bạn có thể thực hiện lại – một lần nữa ấn tổ hợp phím Windows+Ctrl+F, sau đó ấn lại tổ hợp phím Ctrl+F nhé.

loi-khi-an-Ctrl+F-Find-and-Replace

Hộp thoại Find and Replace

Chúc các bạn thành công!

Giải thích thêm về tổ hợp phím Windows+Ctrl+F: Tổ hợp phím tìm kiếm máy trính trong mạng nội bộ

Nếu một máy tính đang hoạt động trong hệ thống mạng nội bộ (mạng nội bộ của một công ty chẳng hạn) và bạn muốn tìm một máy tính khác theo tên máy, bạn có thể ấn tổ hợp phím Windows+Ctrl+F.

Sau khi ấn tổ hợp phím này, sẽ có một hộp thoại mới là Find Computers xuất hiện, bạn gõ tên máy tính vào mục Computer name và ấn Enter.

Trong danh sách máy tính trả về, bạn có thể ấn phím mũi tên Lên hoặc Xuống để chọn máy tính mình muốn truy cập, sau đó ấn Enter để xem danh sách các thư mục và máy in được chia sẻ có sãn trên máy.

Nice day!

Cách xóa tất cả các hyperlink trong Excel

Thay vì tỉ mẩn xóa từng hyperlink trong mỗi sheet excel, bạn có thể xóa cả loạt hyper link trong từng trang Excel một cách đơn giản.

Hyperlink có nghĩa là một siêu liên kết, là nguồn dẫn tài liệu để khi người dùng click vào nó sẽ được dẫn đến một trang khác hoặc một vị trí nào đó trên trang web. Hypertext là văn bản với các siêu liên kết. Văn bản, nội dung bao gồm có siêu liên kết “Hyperlink” được gọi với tên là Anchor text.

Hyperlink nhiều lúc rất tiện dụng nhưng cũng có khi chúng làm ta bực mình khi đang đọc lại kích nhầm vào liên kết trong excel, vì ngay khi kích vào liên kết, ta sẽ đến trang chứa địa chỉ này. Thông thường, ta có thể loại bỏ (remove) những liên kết này đi bằng cách ấn chuột phải vào ô có liên kết, chọn “Remove Hyperlink“. Nhưng với rất nhiều liên kết thì cách thủ công trên không còn hữu dụng vì rất tốn thời gian. Bạn có thể thử cách sau để nhanh chóng xóa cả loạt hyperlink trong 1 trang Excel.

cach-xoa-tat-ca-cac-hyperlink-trong-excel-1

File ban đầu với nhiều hyperlink

Đầu tiên tại giao diện của Excel bạn nhấn tổ hợp phím Alt + F11 để vào chức năng Visual Basic.

cach-xoa-tat-ca-cac-hyperlink-trong-excel-2

Tiếp theo hãy chọn “sheet” cần “remove link” chuột phải sheet > Insert  > Module

cach-xoa-tat-ca-cac-hyperlink-trong-excel-3

Sau đó hãy copy đoạn code bên dưới và dán vào cửa sổ mới hiện ra:

Sub RemoveHyperlink()
ActiveSheet.Hyperlinks.Delete
End Sub

cach-xoa-tat-ca-cac-hyperlink-trong-excel-4

Cuối cùng, chọn Run (hoặc phím tắt F5) để thực hiện quá trình xóa các hyperlink.

cach-xoa-tat-ca-cac-hyperlink-trong-excel-5

Kết quả đây nhé

Khi quay lại bảng Excel ban đầu đã không còn các hyperlink nữa.

Phương pháp này có thể áp dụng cho tất cả các phiên bản Microsoft Excel. Tuy nhiên, nó có hạn chế là chỉ xóa được hyperlink trong từng sheet excel. Do đó để tiếp tục bạn phải trở lại Visual Basic và lặp lại các thao tác như trên.

Chúc bạn thành công ^^

Nếu bạn có phương pháp khác hãy comment lại nhé!

-ST-

Cách gõ chỉ số trên, chỉ số dưới trong Word và Excel

Khi muốn gõ công thức hoá học, hàm tính toán toán học phức tạp, với các chỉ số trên hoặc chỉ số dưới, thay vì sử dụng công cụ gõ công thức trong Word, bạn có thể dùng phím tắt để gõ chỉ số trên, chỉ số dưới dễ dàng hơn.

Cách làm này áp dụng cho tất cả các phiên bản Word, Excel.

Gõ chỉ số trên trong Word

Nhấn tổ hợp phím Ctrl + Shift + phím +/= (gần phím Backspace) để gõ chỉ số trên. Khi gõ xong, nhấn lại tổ hợp phím này 1 lần nữa để trở về bình thường. Với tổ hợp phím tắt này bạn có thể chuyển đổi qua lại giữa trên và dưới ngay lập tức.

Gõ chỉ số dưới trong Word

Nhấn tổ hợp phím Ctrl + phím +/= (gần phím Backspace) để gõ chỉ số dưới. Khi gõ xong, nhấn lại tổ hợp phím này 1 lần nữa để trở về bình thường. Với tổ hợp phím tắt này bạn có thể chuyển đổi qua lại giữa trên và dưới ngay lập tức.

Gõ chỉ số trên trong Excel

Excel thì phức tạp hơn 1 chút, bạn cần gõ hết nội dung đó ra, cả chữ to, chữ nhỏ. Rồi bôi đen kí tự muốn đưa lên hay hạ xuống. Mở Format Cells (Ctrl + 1), chọn Superscript (Alt + E) để đưa kí tự lên trên. Chọn OK.

go-chi-so-tren-trong-excel

Gõ chỉ số trên trong Excel

Gõ chỉ số dưới trong Excel

Excel thì phức tạp hơn 1 chút, bạn cần gõ hết nội dung đó ra, cả chữ to, chữ nhỏ. Rồi bôi đen kí tự (số 2 trong hình) muốn đưa lên hay hạ xuống. Mở Format Cells (Ctrl + 1), chọn Subscript (Alt+B) cho để đưa kí tự xuống dưới. Chọn OK.

go-chi-so-duoi-trong-excel

Gõ chỉ số dưới trong Excel

-ST-

Cách chuyển phông chữ từ VnTime sang Times New Roman trong Word / Excel

Word và Excel là 2 trong số những ứng dụng văn phòng được dùng phổ biến hàng ngày, tuy nhiên, cách chuyển đổi font chữ vẫn còn bỡ ngỡ với một số người dùng. Bài viết này sẽ giới thiệu với bạn cách chuyển phông chữ từ VnTime sang Time New Roman Word và Excel.

 Bài viết này áp dụng cho Word, Excel: 2003, 2007, 2010, 2013 bằng cách chỉ sử dụng phần mềm gõ tiếng Việt Unikey để chuyển đổi phông chữ từ .Vntime sang Time new Roman.

Chuẩn bị
* Phần mềm gõ tiếng Việt Unikey.

* Đoạn văn bản cần chuyển đổi.

Bước 1: Chọn văn bản, đoạn văn bản.
Bạn bôi đen toàn bộ văn bản bằng cách nhấn tổ hợp phím Ctrl + A sau đó nhấn Ctrl + C để Copy hoặc Ctrl + X để cắt.

 

Bước 2: Bật Unikey Toolkit.
Bạn bật Unikey Toolkit bằng cách nhấn tổ hợp phím Ctrl + Shift + F6 hoặc Click chuột phải vào biểu tượng Unikey chọn Công cụ..[CS+F9] tuy nhiên bạn nên nhấn tổ hợp phím sẽ nhanh hơn.

 

Bước 3: Thiết lập Unikey Toolkit
Khi hộp thoại Unikey Toolkit mở ra các bạn chọn Bảng mã nguồn là: TCVN3(ABC) và bảng mã đích là Unicode (1). Sau đó tích vào Chuyển mã clipboard (2)

 

 Chú ý
Bạn phải tích vào Chuyển mã clipboard để thực hiện chuyển phông.

Sau đó nhấn Chuyển mã (3)

– Thông báo chuyển bảng mã thành công

 

Bước 4: Quay trở lại văn bản bạn dán đoạn vừa Copy vào và bôi đen chúng.
Sau khi chuyển mã thành công bạn quay lại văn bản mà bạn vừa Copy hoặc Cut chúng, bạn dán đoạn văn bản đó vào rồi bôi đen chúng sau đó chọn phông chữ Times New Roman nhấn Enter để xem kết quả.

-ST-