Công thức Excel: Chuyển chữ cái thành số
Đôi khi, bạn muốn chuyển đổi a thành 1, b thành 2, c thành 3, v.v. trong trang tính Excel. Tuy nhiên, để chuyển đổi từng cái một thì rất lãng phí thời gian. Trong hướng dẫn này, tôi giới thiệu một số công thức Excel để giải quyết vấn đề này.
Chuyển một chữ cái thành một số trong mỗi ô
Để chuyển đổi một chữ cái thành một số trong mỗi ô Excel, bạn có thể sử dụng công thức dưới đây.
Công thức chung:
| COLUMN(INDIRECT(cell_reference&1)) |
Lập luận
| Cell_reference: the cell that contains the letter you want to convert to number. |
Công thức này hoạt động như thế nào
Lấy một ví dụ để giải thích công thức. Ở đây bạn sẽ chuyển chữ cái trong ô C3 thành số. Vui lòng sử dụng công thức này.
| =COLUMN(INDIRECT(C3&1)) |
Ấn Bản đăng ký hạng mục thi Chìa khóa.
Giải thích
INDIRECT chức năng: hàm này dùng để chuyển đổi một chuỗi văn bản thành một tham chiếu hợp lệ. Ở đây INDIRECT (C3 & 1) bạn có thể thấy nó như sau:
CHỈNH SỬA (C3 & 1)
= INDIRECT (s & 1)
= INDIRECT (s1)
COLUMN chức năng: Các COLUMN hàm trả về số cột của tham chiếu đã cho. Bây giờ công thức là COLUMN (s1) trả về 19.
Ghi chú:
1. Công thức này trả về số cột, có nghĩa là nếu có nhiều hơn một chữ cái trong ô, nó sẽ trả về như ví dụ minh họa bên dưới.
2. Công thức không phân biệt chữ hoa chữ thường.
Chuyển một số chữ cái thành một chuỗi số trong mỗi ô
Nếu bạn muốn chuyển đổi các chữ cái trong mỗi ô thành số như hình minh họa bên dưới, bạn cần một công thức mảng phức tạp.
Công thức chung:
| TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(cell_refer,ROW(INDIRECT("1:"&LEN(cell_refer))),1))),Rtable,2,0)) |
Lập luận
| Cell_refer: the cell that you want to convert the letters to numbers. |
| Rtable:a table with two columns, one lists all letters, one lists the relative numbers. |
Công thức này hoạt động như thế nào
Giả sử bạn muốn chuyển đổi các chữ cái trong phạm vi B3: B8 thành số, hãy làm như sau:
Đầu tiên, tạo một bảng để liệt kê tất cả các chữ cái và số tương đối của chúng.
Sau đó, chọn phạm vi bảng và chuyển đến Hộp tên (bên cạnh thanh Công thức) để đặt tên cho dải ô, ở đây tên dải ô là Rtable. xem ảnh chụp màn hình:
Bây giờ sử dụng công thức này:
| =TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)) |
Ấn Bản đăng ký hạng mục thi , tất cả các chữ cái đầu tiên của mỗi từ trong ô A1 sẽ được trích xuất.
Giải thích
ROW (INDIRECT ("1:" & LEN (B3))): Các Chức năng ROW trả về số hàng, có công thức mảng, sau đó trả về {1; 2; 3}. MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1): Các Hàm MID trả về ký tự ở vị trí cụ thể của chuỗi đã cho. Công thức này có thể được xem như bên dưới
=MID(B3,{1;2;3},1)
= MID (“acd”, {1; 2; 3}, 1)
= {"a"; "c"; "d"}
VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0): Các Hàm VLOOKUP được sử dụng để tìm các số phù hợp của "a", "c", "d" trong cột thứ hai của phạm vi Rtable. Sau đó, nó trở lại {1; 3; 4}.
TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)): Các Hàm TEXTJOIN là một hàm mới trong Excel 2019 và 365, nó được sử dụng để nối tất cả các văn bản bằng dấu phân cách. Ở đây nó trở lại 134.
Ghi chú:
Công thức này không phân biệt chữ hoa chữ thường.
Lưu ý:
Giả sử a = 1, b = 2, c = 3, d = 4, bạn có thể sử dụng công thức như sau:
| =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"a",1),"b",2),"c",3),"d",4) |
Ấn Bản đăng ký hạng mục thi Chìa khóa.
Công thức này phân biệt chữ hoa chữ thường.
Tệp mẫu
Nhấp để tải xuống tệp mẫuCông thức tương đối
- Xóa N ký tự đầu tiên khỏi ô
Ở đây giới thiệu công thức để loại bỏ các ký tự từ bên trái của một chuỗi văn bản trong Excel. - Xóa khỏi bên phải văn bản
Ở đây giới thiệu công thức để loại bỏ các ký tự từ bên phải của một chuỗi văn bản trong Excel. - Trích xuất từ cuối cùng từ chuỗi văn bản trong Excel
Hướng dẫn này cung cấp một công thức để trích xuất từ cuối cùng từ chuỗi văn bản cho sẵn. - Trích xuất từ đầu tiên từ chuỗi văn bản trong Excel
Hướng dẫn này cung cấp một công thức để trích xuất từ đầu tiên từ chuỗi văn bản cho sẵn.
Chức năng tương đối
- Hàm INDIRECT
Chuyển đổi một chuỗi văn bản thành một tham chiếu hợp lệ. - Hàm COLUMN
Trả về số cột mà công thức xuất hiện hoặc số cột của tham chiếu đã cho. - Chức năng ROW
Trả về số hàng của một tham chiếu. - MID
Trả lại các ký tự cụ thể từ giữa chuỗi văn bản.
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel - Giúp bạn nổi bật giữa đám đông
Kutools cho Excel tự hào có hơn 300 tính năng, Đảm bảo rằng những gì bạn cần chỉ là một cú nhấp chuột...
Tab Office - Bật tính năng Đọc và Chỉnh sửa theo Tab trong Microsoft Office (bao gồm Excel)
- Một giây để chuyển đổi giữa hàng chục tài liệu đang mở!
- Giảm hàng trăm cú click chuột cho bạn mỗi ngày, tạm biệt bàn tay chuột.
- Tăng năng suất của bạn lên 50% khi xem và chỉnh sửa nhiều tài liệu.
- Mang các tab hiệu quả đến Office (bao gồm Excel), giống như Chrome, Edge và Firefox.
