Text Functions

จัดการข้อความให้อยู่หมัด ด้วยฟังก์ชั่นกลุ่ม TEXT

co-create บทความนี้คือส่วนหนึ่งของโครงการ ร่วมสร้าง “หนังสือคู่มือ Excel ที่เจ๋งที่สุด” ใครที่มี comment เพื่อแนะนำ ปรับปรุงหนังสือได้ คุณจะได้เครดิตในฐานะผู้ร่วมเขียน ลงในหนังสือที่จะพิมพ์จริงๆ ด้วย! อ่านรายละเอียด และดูสารบัญหนังสือ คลิ๊กที่นี่


  ก่อนจะนำข้อมูลไปสรุปผลในบทต่อๆ ไปได้ คุณอาจจำเป็นต้องจัดการข้อมูล ให้ออกมาอยู่ในรูปแบบที่คุณต้องการก่อน เพื่อให้สามารถสรุปข้อมูลได้มีประสิทธิภาพมากขึ้น เช่น แยกชื่อและนามสกุลออกจากกัน หรือมีการแปลงคำบางอย่าง เช่น แปลงตัวพิมพ์เล็กพิมพ์ใหญ่ หนึ่งในฟังก์ชั่นที่มีบทบาทในการแปลงข้อมูลหรือจัดการข้อมูลให้เรียบร้อยมากขึ้นมากที่สุดตัวหนึ่งก็คือ ฟังก์ชั่นกลุ่ม TEXT นั่นเอง ซึ่งคุณจะต้องรู้จักฟังก์ชั่นพื้นฐานเหล่านี้ก่อนครับ คุณอาจต้องใช้มันผสมผสานกันพอสมควรเลยล่ะ

สรุปฟังก์ชั่นกลุ่ม Text ที่ใช้บ่อย

LEN

นับจำนวนตัวอักษร (นับทั้งอักษร ช่องว่าง สระ และวรรณยุกต์ด้วย) len =LEN(text) =LEN(ข้อความที่จะนับอักษร) ผลลัพธ์ : เป็น Number ตัวอย่าง : =LEN("มดแดง") =5

LEFT

ตัดคำจากทางซ้าย (ระบุได้ว่าตัดกี่ตัวอักษร) left =LEFT(text, [fusion_builder_container hundred_percent="yes" overflow="visible"][fusion_builder_row][fusion_builder_column type="1_1" background_position="left top" background_color="" border_size="" border_color="" border_style="solid" spacing="yes" background_image="" background_repeat="no-repeat" padding="" margin_top="0px" margin_bottom="0px" class="" id="" animation_type="" animation_speed="0.3" animation_direction="left" hide_on_mobile="no" center_content="no" min_height="none"][num_chars]) =LEFT(ข้อความต้นฉบับ, [จำนวนอักษรที่จะตัด]) ผลลัพธ์ : เป็น Text ตัวอย่าง : =LEFT("มดแดง",2) =“มด”

MID

ตัดคำโดยระบุได้ว่าเริ่มตัดที่ตำแหน่งไหน เป็นจำนวนกี่ตัวอักษร mid-fix =MID(text,start_num,num_chars) =MID(ข้อความต้นฉบับ,อักษรที่เริ่มตัด,จำนวนอักษรที่จะตัด) ผลลัพธ์ : เป็น Text ตัวอย่าง : =MID("มดแดง",3,2) =“แด”

RIGHT

ตัดคำจากทางขวา (ระบุได้ว่าตัดกี่ตัวอักษร) right =RIGHT(text, [num_chars]) =RIGHT(ข้อความต้นฉบับ, [จำนวนอักษรที่จะตัด]) ผลลัพธ์ : เป็น Text ตัวอย่าง : =RIGHT("มดแดง",2) =“ดง”

TRIM

ตัดช่องว่างส่วนเกินออก (ลบช่องว่างที่อยู่หน้า หลัง รวมถึงช่องว่างตรงกลางที่เกิน 1 เคาะ) trim =TRIM(text) =TRIM(ข้อความที่จะตัด) ผลลัพธ์ : เป็น Text ตัวอย่าง : =TRIM("  inw  excel ") ="inw excel" (เหลือช่องว่างกลาง 1 space)

FIND 

ค้นหาคำที่ต้องการ ว่าอยู่ตำแหน่งที่เท่าไหร่ของข้อความที่กำหนด (สนใจตัวพิพม์เล็กพิมพ์ใหญ่) ระบุได้ว่าให้เริ่มหาตั้งแต่อักษรที่เท่าไหร่ find =FIND(find_text, within_text, [start_num]) =FIND(ส่วนของคำที่ต้องการหา, หาในคำไหน, [ตำแหน่งที่เริ่มหา]) ผลลัพธ์ : เป็น Number ตัวอย่าง : =FIND("Excel","inwexcel is Excellent") =13

SEARCH

ค้นหาคำที่ต้องการ ว่าอยู่ตำแหน่งที่เท่าไหร่ของข้อความที่กำหนด โดยใช้การหาแบบ Wildcard (ไม่สนใจตัวพิพม์เล็กพิมพ์ใหญ่) ระบุได้ว่าให้เริ่มหาตั้งแต่อักษรที่เท่าไหร่ search =SEARCH(find_text,within_text,[start_num]) =SEARCH(ส่วนของคำที่ต้องการหา, หาในคำไหน, [ตำแหน่งที่เริ่มหา]) ผลลัพธ์ : เป็น Number ตัวอย่าง : =SEARCH("Excel","inwexcel is Excellent") =4

SUBSTITUTE

แทนที่คำที่ต้องการด้วยอีกคำหนึ่ง ใช้เมื่อรู้คำที่จะถูกแทนที่ substitute =SUBSTITUTE(text, old_text, new_text, [instance_num]) =SUBSTITUTE(คำที่ต้องการแทนที่, ส่วนของคำเดิมที่จะถูกแทนที่, ส่วนของคำใหม่ที่จะมาแทนที่, [เจาะจงอันที่ถูกแทนที่ได้]) ผลลัพธ์ : เป็น Text ตัวอย่าง : =SUBSTITUTE("ผม like มาก","like","ชอบ") = "ผม ชอบ มาก"

REPLACE

แทนที่ตำแหน่งที่ต้องการด้วยอีกคำหนึ่ง ใช้เมื่อรู้ตำแหน่งและจำนวนตัวอักษรที่จะถูกแทนที่ replace =REPLACE(old_text, start_num, num_chars, new_text) =REPLACE(คำที่ต้องการแทนที่, ตำแหน่งเริ่มที่จะแทนที่, จำนวนอักษรที่จะแทนที่, คำใหม่ที่จะมาแทนที่) ผลลัพธ์ : เป็น Text ตัวอย่าง : =REPLACE("081-234-5678",5,3, "ไม่บอก") = "081-ไม่บอก-5678"

TEXT

เปลี่ยนรูปแบบ Number Format โดยใช้สูตร text =TEXT(value, format_text) ในช่อง format_text ให้ใส่รูปแบบคล้ายการทำ Custom Format ซึ่งจะอธิบายต่อไปในบทหลัง ผลลัพธ์ : เป็น Text ตัวอย่าง : =TEXT(1234.5678,"0.00")= "1234.57"

UPPER

แปลงข้อความให้กลายเป็นตัวพิมพ์ใหญ่ทั้งหมด upper = UPPER(text) ผลลัพธ์ : เป็น Text ตัวอย่าง : =UPPER(“mOnKey”)= "MONKEY"

LOWER

แปลงข้อความให้กลายเป็นตัวพิมพ์เล็กทั้งหมด lower = LOWER(text) ผลลัพธ์ : เป็น Text ตัวอย่าง : =LOWER(“mOnKey”)= "monkey"

PROPER

แปลงข้อความแต่ละคำให้ขึ้นต้นด้วยตัวพิมพ์ใหญ่ นอกนั้นตัวพิมพ์เล็กทั้งหมด proper = PROPER(text) ผลลัพธ์ : เป็น Text ตัวอย่าง : =PROPER(“mOnKey”)= "Monkey" (more…)

By Sira Ekabut, ago
Date Functions

สุดยอดฟังก์ชั่นช่วยจัดการข้อมูลให้เป็นไปตามต้องการ

บทความนี้ถูกแยกเขียนใหม่เป็น 3 หัวข้อดังนี้ เข้าไปใน link ได้เลยครับ Level 14. จัดการข้อความให้อยู่หมัด ด้วยฟังก์ชั่นกลุ่ม TEXT Level 15 : เดินทางข้ามเวลาด้วยฟังก์ชั่นกลุ่ม Date & Time Level 16 : VLOOKUP และ MATCH คู่ซี้ค้นหาข้อมูล

By Sira Ekabut, ago
สารพันปัญหา Excel

แนวทางแก้ไขเวลา Excel เอ๋อ ไม่ยอมคำนวณค่าในสูตร

เมื่อ Excel เอ๋อ… หลายๆครั้งเวลาเพื่อนๆทำงานใน Excel อาจพบกับอาการที่ Excel เอ๋อๆ อาจมีหลายอาการ เช่น การคำนวณไม่ยอม Update เช่น ค่าที่อ้างอิงเปลี่ยนไปแล้ว แต่ค่าในผลลัพธ์ไม่ยอมเปลี่ยน มีการค้างเครื่องหมายเท่ากับไว้เลย เช่น =30*5 ดันไม่ยอมคำนวณออกมาเป็น 150 แต่ขึ้นค้างเป็น =30*5 อยู่อย่างนั้น วิธีแก้ไขอาการ Excel ไม่ Update ค่า ลองกด F9 เพื่อ Re-Calculate ดูก่อน เพราะบางทีเราอาจไปเผลอตั้งค่าให้ Excel ทำ Read more…

By Sira Ekabut, ago
Text Functions

เปิดโปง! เทคนิคการเชื่อม Text จากหลายๆ Cell (มากๆ) เข้าด้วยกัน

ผมเชื่อว่าหลายคนต้องเคยเจอปัญหาในการเชื่อม Text จากหลายๆช่องเข้าด้วยกันมาก่อนแน่ๆ เพราะมันไม่มีสูตรสำเร็จรูปเช่นเดียวกับปัญหาอื่นๆ เช่น การบวกหลายๆช่องเข้าด้วยกัน (ฟังก์ชั่น  SUM ทำได้) หรือการ คูณหลายๆช่องเข้าด้วยกัน (ฟังก์ชั่น PRODUCT ทำได้) แต่ทำไม พอจะเชื่อม Text เข้าด้วยกันดันทำไม่ได้!! แม้ว่า Excel จะมีฟังก์ชั่น CONCATENATE มาให้ แต่มันก็ไม่สามารถใช้แบบ =CONCATENATE(A1:H1) ได้ แต่กลับต้องใส่เป็น =CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1) หรือ =A1&B1&C1&D1&E1&F1&G1&H1 แทน ซึ่งเหนื่อยมากๆ โดยเฉพาะหากต้องเชื่อมมากกว่า 10 Cell ขึ้นไป!! (และอาจทำผิดโดยไม่รู้ตัวด้วย)

วันนี้ผมมีเทคนิคแก้ปัญหาดังกล่าวมานำเสนอ โดยไม่จำเป็นต้องใช้ VBA เข้าช่วยด้วย

ทำยังไงมาดูกันครับ ลองทำตามดูนะครับ ดูเหมือนจะเยอะ แต่ ทำจริงๆไม่กี่วินาทีเอง (more…)

By Sira Ekabut, ago
Date Functions

เล่นแร่แปรสูตร : การแปลงวันที่ Text ให้เป็นวันที่ Date

ปกติแล้ว Excel จะมีฟังก์ชั่นที่ชื่อว่า DATEVALUE ในการเปลี่ยนวันที่ในรูปแบบ Text ให้กลายเป็นรูปแบบ Date จริงๆ ที่เป็นตัวเลขอยู่แล้ว… แต่ฟังก์ชั่นนี้มีข้อจำกัดอยู่มาก คือ มันจะ Convert Text ได้แค่ในรูปแบบที่มันรู้จักเท่านั้น (ซึ่งมีไม่กี่แบบ คล้ายๆตอนที่เราพิมพ์ลงไปใน cell ปกติ แหละครับ ว่า 31/1/2014 หรือ 31-Jan-2014 หรือ 31-01-2014 แล้ว excel มันจะฉลาดแปลงเป็นวันที่ได้เอง) ดังนั้น ถ้าหากเรามี Date ในรูปแบบแปลกไปจากที่มันรู้จัก เช่น 31012014 หรือ 20140131 Read more…

By Sira Ekabut, ago
Basic Formula

การแปลงค่าจาก ตัวหนังสือ <=> ตัวเลข

ทำไมต้องแปลงค่า? หากเราใช้ข้อมูลที่มีรูปแบบไม่ตรงตามที่ต้องการ อาจเปิดปัญหาในการใช้สูตร เช่น Lookup ค่าไม่เจอ ก็เป็นไปได้ครับ วิธีแปลงค่า จากตัวหนังสือ => ตัวเลข ใน Excel วิธีที่ง่าย และได้ผลที่สุด คือ เอาไปคูณ 1 ครับ เช่น ในช่อง A1 มีตัวหนังสือว่า 000540 หากจะแปลงไปเป็นตัวเลข และแสดงผลในช่อง B1 ในช่อง B1 ให้เขียนว่า =A1*1 ซึ่งจะได้ค่าเป็น 540 แบบเป็น Number นั่นเอง Read more…

By Sira Ekabut, ago