ไปยังเนื้อหาหลัก

นับค่าที่คั่นด้วยลูกน้ำในเซลล์

ในบทช่วยสอนนี้คุณจะได้เรียนรู้วิธีการใช้ฟังก์ชัน LEN, TRIM และ SUBSTITUTE ร่วมกันเพื่อนับค่าที่คั่นด้วยจุลภาคในเซลล์ใน Excel


วิธีการนับค่าที่คั่นด้วยจุลภาคในเซลล์ใน Excel

ตามภาพหน้าจอด้านล่างนี้มีรายการสตริงที่คั่นด้วยจุลภาคในคอลัมน์ B ซึ่งคุณต้องการนับค่าที่คั่นด้วยจุลภาคในแต่ละเซลล์โปรดทำดังนี้เพื่อให้บรรลุ

สูตรทั่วไป

=IF(ISBLANK(B3),"",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",""))+1)

ข้อโต้แย้ง

A1: หมายถึงเซลล์ที่มีสตริงที่คั่นด้วยลูกน้ำซึ่งคุณจะนับค่าที่คั่นด้วยลูกน้ำ

จะใช้สูตรนี้ได้อย่างไร?

1. เลือกเซลล์ว่างเพื่อส่งออกผลลัพธ์ ในกรณีนี้ฉันเลือกเซลล์ D3

2. ใส่สูตรด้านล่างลงไปแล้วกด เข้าสู่ สำคัญ. จากนั้นลาก Fill Handle ลงไปจนสุดเพื่อใช้กับเซลล์อื่น

=IF(ISBLANK(B3),"",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",""))+1)

หมายเหตุ: ในสูตรนี้ B3 คือเซลล์ที่มีสตริงที่คั่นด้วยลูกน้ำซึ่งคุณจะนับค่าที่คั่นด้วยลูกน้ำภายใน คุณสามารถเปลี่ยนได้ตามต้องการ

สูตรนี้ทำงานอย่างไร?

=IF(ISBLANK(B3),"",LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3),",",""))+1)

1. LEN(TRIM(B3)): ฟังก์ชัน TRIM จะลบช่องว่างของสารสกัดทั้งหมดออกจากสตริงที่คั่นด้วยเครื่องหมายจุลภาค "AA, BB, CC, DD" และเก็บเฉพาะช่องว่างระหว่างคำเท่านั้น จากนั้นฟังก์ชัน LEN จะคำนวณความยาวทั้งหมดของ "AA, BB, CC, DD" และส่งกลับผลลัพธ์เป็น 14;

2. LEN(SUBSTITUTE(TRIM(B3),",",""))

  • SUBSTITUTE(TRIM(B3),",",""): ตามคำอธิบายข้างต้นฟังก์ชัน TRIM ที่นี่จะลบพื้นที่แยกทั้งหมดจาก "AA, BB, CC, DD" และส่งคืนผลลัพธ์เป็น "AA, BB, CC, DD" จากนั้นฟังก์ชัน SUBSTITUTE จะแทนที่เครื่องหมายจุลภาคทั้งหมดใน "AA, BB, CC, DD" โดยไม่มีอะไรเลยและได้ผลลัพธ์เป็น "AA BB CC DD";
  • LEN("AA BB CC DD"): ฟังก์ชัน LEN ส่งคืนจำนวนอักขระในสตริงข้อความ "AA BB CC DD" ผลลัพธ์คือ 11

3. 14-11 1 +: ในที่นี้ใช้ความยาวทั้งหมดของ "AA, BB, CC, DD" ลบความยาวทั้งหมดของ "AA BB CC DD" เพื่อให้ได้จำนวนจุลภาคทั้งหมดในสตริง (14-11 = 3) เนื่องจากมักจะมีคำหลังเครื่องหมายจุลภาคสุดท้ายคุณต้องเพิ่ม 1 ถึงหมายเลข 3 เพื่อให้ได้คำที่คั่นด้วยจุลภาคทั้งหมดในสตริง ผลลัพธ์คือ 4

4. IF(ISBLANK(B3),"",14): ฟังก์ชัน If ในที่นี้จะบอกว่าถ้า B3 ว่างเปล่า ก็จะไม่ส่งคืนค่าใด ๆ มิฉะนั้นให้ส่งคืนหมายเลข 14 ในที่นี้ B3 มีค่า ดังนั้นฟังก์ชัน IF จึงส่งคืนหมายเลข 14


ฟังก์ชันที่เกี่ยวข้อง

ฟังก์ชัน Excel LEN
ฟังก์ชัน Excel LEN ส่งคืนจำนวนอักขระในสตริงข้อความ

ฟังก์ชัน Excel SUBSTITUTE
ฟังก์ชัน Excel SUBSTITUTE จะแทนที่ข้อความหรืออักขระภายในสตริงข้อความด้วยข้อความหรืออักขระอื่น

ฟังก์ชัน Excel TRIM
ฟังก์ชัน Excel TRIM จะลบช่องว่างพิเศษทั้งหมดออกจากสตริงข้อความและเว้นช่องว่างระหว่างคำเท่านั้น


สูตรที่เกี่ยวข้อง

นับจำนวนอักขระที่ระบุในเซลล์ Excel
บทช่วยสอนนี้แนะนำวิธีการใช้สูตรตามฟังก์ชัน LEN และ SUSTITUTE เพื่อนับจำนวนครั้งที่อักขระเฉพาะปรากฏในเซลล์ Excel

นับอักขระเฉพาะในช่วงของเซลล์
บทช่วยสอนนี้แนะนำสูตรบางสูตรเพื่อนับจำนวนครั้งที่อักขระเฉพาะปรากฏในช่วงใน Excel

นับคำเฉพาะในเซลล์ใน Excel
บทความนี้จะแนะนำสูตรในการนับจำนวนครั้งที่คำใดคำหนึ่งปรากฏในเซลล์ใน Excel

นับคำเฉพาะในช่วงใน Excel
บทความนี้อธิบายสูตรในการนับจำนวนครั้งที่คำใดคำหนึ่งปรากฏในช่วงของเซลล์ใน Excel

นับจำนวนอักขระในเซลล์ใน Excel
อักขระทั้งหมดไม่เพียง แต่รวมถึงตัวอักษรทั้งหมด แต่ยังรวมถึงช่องว่างเครื่องหมายวรรคตอนและสัญลักษณ์ทั้งหมดในเซลล์ด้วย ทำตามบทช่วยสอนนี้เพื่อนับจำนวนอักขระทั้งหมดในเซลล์ด้วยฟังก์ชัน LEN ใน Excel

นับจำนวนอักขระในช่วงใน Excel
บทความนี้อธิบายสูตรการนับอักขระทั้งหมดที่ปรากฏในช่วงของเซลล์ใน Excel


เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด

Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน  |  ลบแถวว่าง  |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล  |  รอบโดยไม่มีสูตร ...
ซุปเปอร์วีลุคอัพ: หลายเกณฑ์  |  หลายค่า  |  ข้ามหลายแผ่น  |  การค้นหาที่ไม่ชัดเจน...
โฆษณา รายการแบบหล่นลง: รายการแบบหล่นลงอย่างง่าย  |  รายการแบบหล่นลงขึ้นอยู่กับ  |  เลือกหลายรายการแบบหล่นลง...
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  เปรียบเทียบคอลัมน์กับ เลือกเซลล์เดียวกันและต่างกัน ...
คุณสมบัติเด่น: กริดโฟกัส  |  มุมมองการออกแบบ  |  บาร์สูตรใหญ่  |  สมุดงานและตัวจัดการชีต | ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)  |  เลือกวันที่  |  รวมแผ่นงาน  |  เข้ารหัส/ถอดรหัสเซลล์  |  ส่งอีเมลตามรายการ  |  ซุปเปอร์ฟิลเตอร์  |  ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ ... )  |  50 + แผนภูมิ ประเภท (แผนภูมิ Gantt ... )  |  40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด ... )  |  19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง ... )  |  12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน ... )  |  7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์ Excel ... )  |  ... และอื่น ๆ

Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...

รายละเอียด


แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)

  • หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
  • ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
  • เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
  • นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox
Comments (6)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thanks for this formula. I had been trying a different formula: =LEN(A1)-LEN(SUBSTITUTE(A1),",",""))+1 but if the cell was blank it would have a 1 in it. This doesn't, so it works for counting up numbers. In my case, I'm trying to count the number of dates such as Oct. 6, 13, 20, 27. However, it's not working when it comes to multiplying the count by the cost of the lesson. For instance, if John had lessons on Oct. 6, 13, 20, 27, I have a column for dates of lessons, then a column for number of lessons, a column for the cost of each lesson (30 or 45), and a column to calculate monthly tuition due (number of lessons x cost). For the student with blank dates/no lessons, it gives an error value, and therefore affects my total tuition for the month. Please advise. Thanks!
This comment was minimized by the moderator on the site
Hi sir, formula is not working properly. I have numerical data in cells. It works correctly in cells where numbers consists upon two digits like 22,43,59 etc, but it fails when there comes data like 103,144 or 165,45 etc. Please help
This comment was minimized by the moderator on the site
Hi,
The formula works fine for me and do not reproduce the error you mentioned. May I ask what version of Excel you are using?
This comment was minimized by the moderator on the site
Office 2021
This comment was minimized by the moderator on the site
thanks! this worked very well, except for that it also returns "1" when the cell is blank. I'm trying to count the number of concatenated dates. There are some cells that don't have any dates, and those are coming back with the number '1'. However, the formula is still counting correctly the number of dates otherwise.
This comment was minimized by the moderator on the site
Hi Alicia Majcher,
Thank you for your feedback. If you want to return nothing when the reference cell is blank, apply the following formula.
=IF(ISBLANK(A30),"",LEN(TRIM(A30))-LEN(SUBSTITUTE(TRIM(A30),",",""))+1)
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations