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:

Lập luận

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.

Ấ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:

Lập luận

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:

Ấ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:

Ấ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ẫu

Cô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.