สูตร Excel: แปลงตัวอักษรเป็นตัวเลข
บางครั้งคุณต้องการแปลง a เป็น 1, b เป็น 2, c เป็น 3 และอื่น ๆ ในแผ่นงาน Excel อย่างไรก็ตามการแปลงทีละไฟล์นั้นเสียเวลา ในบทช่วยสอนนี้ฉันแนะนำสูตร Excel เพื่อแก้ปัญหานี้
แปลงตัวอักษรเดี่ยวเป็นตัวเลขในแต่ละเซลล์
ในการแปลงตัวอักษรเดี่ยวเป็นตัวเลขในเซลล์ Excel แต่ละเซลล์คุณสามารถใช้สูตรด้านล่างนี้ได้
สูตรทั่วไป:
| COLUMN(INDIRECT(cell_reference&1)) |
ข้อโต้แย้ง
| Cell_reference: the cell that contains the letter you want to convert to number. |
สูตรนี้ทำงานอย่างไร
ยกตัวอย่างเพื่ออธิบายสูตร ที่นี่คุณจะแปลงตัวอักษรในเซลล์ C3 เป็นตัวเลข โปรดใช้สูตรนี้
| =COLUMN(INDIRECT(C3&1)) |
ข่าวประชา เข้าสู่ กุญแจ
คำอธิบาย
INDIRECT ฟังก์ชัน: ฟังก์ชันนี้ใช้ในการแปลงสตริงข้อความเป็นการอ้างอิงที่ถูกต้อง ที่นี่ INDIRECT (C3 & 1) คุณสามารถเห็นได้ดังนี้:
ทางอ้อม (C3 & 1)
= ทางอ้อม (s & 1)
= ทางอ้อม (s1)
COLUMN ฟังก์ชัน: COLUMN ฟังก์ชันส่งคืนจำนวนคอลัมน์ของการอ้างอิงที่ระบุ ตอนนี้สูตรคือ คอลัมน์ (s1) ซึ่งส่งกลับ 19.
หมายเหตุ:
1. สูตรนี้จะส่งคืนจำนวนคอลัมน์ซึ่งหมายความว่าหากมีตัวอักษรมากกว่าหนึ่งตัวในเซลล์ก็จะส่งกลับดังตัวอย่างด้านล่างที่แสดง
2. สูตรไม่คำนึงถึงขนาดตัวพิมพ์
แปลงตัวอักษรหลายตัวเป็นสตริงตัวเลขในแต่ละเซลล์
หากคุณต้องการแปลงตัวอักษรในแต่ละเซลล์เป็นตัวเลขดังภาพด้านล่างนี้คุณต้องมีสูตรอาร์เรย์ที่ซับซ้อน
สูตรทั่วไป:
| TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(cell_refer,ROW(INDIRECT("1:"&LEN(cell_refer))),1))),Rtable,2,0)) |
ข้อโต้แย้ง
| 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. |
สูตรนี้ทำงานอย่างไร
สมมติว่าคุณต้องการแปลงตัวอักษรในช่วง B3: B8 เป็นตัวเลขโปรดทำดังนี้:
ประการแรกสร้างตารางเพื่อแสดงรายการตัวอักษรทั้งหมดและหมายเลขสัมพัทธ์
จากนั้นเลือกช่วงของตารางและไปที่ กล่องชื่อ (ข้างแถบสูตร) เพื่อตั้งชื่อช่วงให้ชื่อช่วงคือ Rtable ดูภาพหน้าจอ:
ตอนนี้ใช้สูตรนี้:
| =TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)) |
ข่าวประชา เข้าสู่ คีย์ตัวอักษรตัวแรกทั้งหมดของแต่ละคำในเซลล์ A1 จะถูกแยกออกมา
คำอธิบาย
แถว (ทางอ้อม ("1:" & LEN (B3))): ฟังก์ชัน ROW ส่งคืนจำนวนแถวมีสูตรอาร์เรย์แล้วส่งกลับ {1; 2; 3}. MID (B3, ROW (ทางอ้อม ("1:" & LEN (B3))), 1): ฟังก์ชัน MID ส่งคืนอักขระในตำแหน่งเฉพาะของสตริงที่กำหนด สูตรนี้สามารถดูได้ตามด้านล่าง
=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): ฟังก์ชัน VLOOKUP ใช้เพื่อค้นหาตัวเลขที่ตรงกันของ "a", "c", "d" ในคอลัมน์ที่สองของช่วง Rtable จากนั้นจะกลับมา {1; 3; 4}.
TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)): ฟังก์ชัน TEXTJOIN เป็นฟังก์ชันใหม่ใน Excel 2019 และ 365 ซึ่งใช้เพื่อเชื่อมข้อความทั้งหมดเข้าด้วยกันด้วยตัวคั่น นี่มันกลับมา 134.
หมายเหตุ:
สูตรนี้ไม่คำนึงถึงตัวพิมพ์เล็กและใหญ่
หมายเหตุ
สมมติว่า a = 1, b = 2, c = 3, d = 4 คุณสามารถใช้สูตรดังต่อไปนี้:
| =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"a",1),"b",2),"c",3),"d",4) |
ข่าวประชา เข้าสู่ กุญแจ
สูตรนี้คำนึงถึงขนาดตัวพิมพ์
ไฟล์ตัวอย่าง
คลิกเพื่อดาวน์โหลดไฟล์ตัวอย่าง
สูตรสัมพัทธ์
- ลบอักขระ N ตัวแรกออกจากเซลล์
ที่นี่แนะนำสูตรการลบอักขระจากด้านซ้ายของสตริงข้อความใน Excel - ลบจากด้านขวาของข้อความ
ที่นี่แนะนำสูตรการลบอักขระจากด้านขวาของสตริงข้อความใน Excel - แยกคำสุดท้ายจากสตริงข้อความใน Excel
บทช่วยสอนนี้มีสูตรในการแยกคำสุดท้ายจากสตริงข้อความให้ - แยกคำแรกจากสตริงข้อความใน Excel
กวดวิชานี้มีสูตรที่จะแยกคำแรกจากสตริงข้อความให้
ฟังก์ชันสัมพัทธ์
- ฟังก์ชัน INDIRECT
แปลงสตริงข้อความเป็นการอ้างอิงที่ถูกต้อง - ฟังก์ชัน COLUMN
ส่งคืนจำนวนคอลัมน์ที่สูตรปรากฏหรือหมายเลขคอลัมน์ของการอ้างอิงที่ระบุ - ฟังก์ชัน ROW
ส่งคืนหมายเลขแถวของการอ้างอิง - MID
ส่งกลับอักขระเฉพาะจากตรงกลางของสตริงข้อความ
เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน
Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...
แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)
- หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
- ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
- เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
- นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox
