แยกตัวเลข,ข้อความในคอลัมท์เดียวให้เป็นสองคอลัมท์ Excel

เทคนิคการแยกตัวเลขและข้อความที่อยู่ในเซลล์เดียวกัน ให้เป็นสองคอลัมท์ใน Excel ด้วยฟังก์ชัน Find

จากรูป ต้องการแยกจำนวน 100 ออกจากหน่วย sachet ที่ถูกบันทึกไว้ในเซลล์เดียวกัน
ด้วยโจทย์นี้สามารถนำฟังก์ชันข้อความสองชุดมาประยุกต์ใช้ด้วยกัน คือฟังก์ชัน Find และกลุ่มฟังก์ชัน Left, Right, Len (ดูการใช้ Left, Right, Len)

ฟังก์ชัน Find จะคืนค่าตำแหน่งของข้อความในเซลล์ ที่ถูกกำหนดไว้ในฟังก์ชัน และจะนับรวมทุกอักขระในเซลล์อ้างอิงทั้งหมด โดยไม่แยกว่าเป็นเคาะวรรค, สระ หรือตัวอักษร

ไวยากรณ์ฟังก์ชัน FIND

FIND(find_text,within_text,start_num)

• find_text :ข้อความหรือตัวเลขที่ต้องการค้นหา ถ้าเป็นข้อความให้ใส่ "xx "
• within_text : คือข้อความที่มีสิ่งที่่คุณต้องการค้นหา (find_text)
• start_num : ใช้ระบุหมายเลขตำแหน่งใน within_text ที่ต้องการให้เริ่มค้นหา โดยอักขระตัวแรกใน within_text เป็นอักขระ
• หมายเหตุ : ถ้าไม่ได้ใส่ค่าให้กับ start_num ฟังก์ชันจะหมายถึงเริ่มตั้งแต่ 1 หรืออักขระตัวแรก

	D	H	J	K	K
		ผลลัพท์		แสดงการแทนค่าฟังก์ชัน
2	Contain	จำนวน	หน่วย	จำนวน	หน่วย
3	100 sachet	100	sachet	=LEFT(D3,FIND(" ",D3))*1	=RIGHT(D3,LEN(D3)-FIND(" ",D3))
4	1 kg	1	kg	=LEFT(D4,FIND(" ",D4))*1	=RIGHT(D4,LEN(D4)-FIND(" ",D4))
5	80 gram	80	gram	=LEFT(D5,FIND(" ",D5))*1	=RIGHT(D5,LEN(D5)-FIND(" ",D5))

ข้อสังเกตุ จากตัวอย่าง:

• ฟังก์ชัน FIND ใช้ระบุตำแหน่งของวรรคระหว่าง จำนวน กับ หน่วยวัด (FIND(" ",D3) ผลลัพท์เท่ากับ 4
• LEFT(D4,FIND(" ",D4))*1 คือให้สูตรคืนค่าข้อความจากด้านซ้ายจนถึงวรรค และเพื่อให้เป็นตัวเลขจึง คูณด้วย 1
• RIGHT(D5,LEN(D5)-FIND(" ",D5)) คือให้คืนค่าข้อความจากด้านขวาจนถึงวรรค (" ")

ฟังก์ชันแทนที่และตัดข้อความใน Excel SUBSTITUTE, CLEAN และ TRIM

การเอาช่องว่างและอักขระที่ไม่พิมพ์ออกจากข้อความ

บางครั้งการคัดลอกข้อมูลมาวางบน Excel worksheet หรือข้อมูลที่ได้จากการบันทึกอาจมีช่องว่างนำหน้าหรือต่อท้าย ข้อความ, วันที่ หรือ จำนวนตัวเลข ฯลฯ

ซึ่งไม่สามารถนำมาใช้งานได้ทันที ต้องมาแก้ไขเอาช่องว่างที่ไม่ต้องการออก และการที่จะแก้ไขทีละรายการกับข้อมูลที่มีปริมาณมากๆ จะเป็นการเสียเวลามาก


ซึ่งงานลักษณะนี้เราสามารถใช้บริการฟังก์ชันชุด TRIM, CLEAN และ SUBSTITUTE ช่วยตัดอักขระหรือช่องว่างเหล่านี้ออก จะใช้เดี่ยวๆ หรือใช้รวมกันได้ โดยที่อาจพ่วงการใช้งานร่วมกับตัวอักขระ ASCII ซึ่งมี 255 อักขระ

Function	ไวยากรณ์และการทำงานของฟังก์ชัน TRIM, CLEAN
TRIM	=TRIM(text), text คือ ข้อความที่ต้องการเอาช่องว่างออก
	ฟังก์ชัน Trim จะเอาช่องว่างออกจากข้อความ ยกเว้นช่องว่างระหว่างคำ 1 เคาะ
CLEAN	=CLEAN(text), text คือ ข้อความที่ต้องการเอาอักขระที่พิมพ์ไม่ได้ออก
	ฟังก์ชัน Clean จะตัดออกตัวอักขระ ASCII ซึ่งถูกกำหนดให้ใช้กับฟังก์ชั่น CHAR(0)-CHAR(31)

ตัวอย่างการใช้ TRIM และ CLEAN

	A	B	C	D
1	ข้อมูล	การใช้ฟังก์ชัน	ผลลัพท์	คำอธิบายการใช้ฟังก์ชัน TRIM, CLEAN
2	Year  2013	=TRIM(A2)	Year 2013	ช่องว่างก่อนและหลัง Year ลดลง 1 ช่อง
3	Hello	=CLEAN(A3)	Hello	เป็นอักขระที่ไม่พิมพ์หายไป (ค่า ASCll = 2, 7)

Function	ไวยากรณ์และการทำงานของฟังก์ชัน SUBSTITUTE
SUBSTITUTE	=SUBSTITUTE (text,old_text,new_text,instance_num)
	ฟังก์ชัน SUBSTITUTE ใช้แทนที่ข้อความเดิม old text ด้วยข้อความใหม่ new text ส่วน instance_num ใช้เพื่อระบุตำแหน่งการแทนที่ ซึ่งถ้าไม่ระบุ old text จะถูกเปลี่ยนด้วย new text ทั้งหมด

ตารางตัวอย่างการใช้ SUBSTITUTE

	A	B	C	D
1	Old Text	New Text	การใช้ฟังก์ชัน SUBSTITUTE	ผลลัพท์
2	Retail Sales	2013	=SUBSTITUTE(A2,A2,(A2&" "&B2))	Retail Sales 2013
3	Jan 2013	January	=SUBSTITUTE(A3,"Jan",B3)	January 2013
4	1/1/2012		=SUBSTITUTE(A4,2,3,1)	1/1/3012
5	1/1/2012		=SUBSTITUTE(A4,2,3,2)	1/1/2013

อธิบายตารางตัวอย่างการใช้ SUBSTITUTE

1. ต่อท้าย Retail Sales ด้วย 2013 โดยใช้ &" "& เชื่อมคำและเว้นระหว่าง "" ก็เพื่อให้มี เคาะ 1 เคาะ ก่อน 2013
2. แทนที่ Jan ด้วย January
3. เมื่อระบุ instant_num เป็น 1 ฟังก์ชันจะแทนที่เลข 2 ตัวแรก ผลลัพท์จึงเป็น 1/1/3012
4. เปลี่ยน instant_num เป็น 2 ฟังก์ชันแทนที่เลข 2 ตัวที่สอง ผลลัพท์กลายเป็น 1/1/2013

ทำตัวพิมพ์ใหญ่-พิมพ์เล็กด้วยฟังก์ชัน UPPER, PROPER, LOWER ใน Excel 2007

ตัวพิมพ์ใหญ่หรือตัวพิมพ์เล็กก็ควบคุมได้ใน Excel ฟังก์ชันเกี่ยวกับข้อความใน Excel หรือ Excel's Text Function ดูเป็นอะไรที่เรียบง่ายและเหมือนไม่มีความสำคัญสักเท่าไร แต่ถ้าได้ลองใช้งานแล้วจะรู้สึกว่ามันก็เวิร์คเหมือนกันนะ

UPPER, PROPER, LOWER

คือชุดฟังก์ชันก์ที่พูดถึง หน้าที่คือเปลี่ยน Text ให้เป็นตัวพิมพ์ใหญ่ หรือ ตัวพิมพ์เล็ก แต่ละตัวทำงานคล้ายกัน แต่คืนค่าต่างกัน



รูปแบบฟังก์ชัน

UPPER(text)	: แปลงข้อความใน (text) ไปเป็นตัวพิมพ์ใหญ่
PROPER(text)	: เปลี่ยนตัวอักษรตัวแรกในแต่ละคำใน (text) เป็นตัวพิมพ์ใหญ่ และอื่นเป็นตัวพิมพ์เล็ก
LOWER(text)	: แปลงข้อความใน (text) เป็นตัวพิมพ์เล็ก

ตัวอย่างการใช้งาน

1. ส่วนแรกใช้ฟังก์ชันกับ Text
2. ส่วนถัดไป สมมุติให้ A6 เป็นประโยค you must be the change You wish to see in the World. และจะใช้ประโยคนี้กับฟังก์ชันทั้งสาม

Row	Column
	A	B	C
1	ฟังก์ชันที่ใช้	แทนค่า	ผลลัพท์
2	UPPER(text)	UPPER ("Mahatama Gandhi")	MAHATAMA GANDHI
3	PROPER(text)	PROPER ("good manufacturing practice")	Good Manufacturing Practice
4	LOWER(text)	LOWER ("Swimsuit-Shopping Demons")	swimsuit-shopping demons
5
6	you must be the change You wish to see in the World.
7	UPPER(A6)	YOU MUST BE THE CHANGE YOU WISH TO SEE IN THE WORLD.
8	PROPER(A6)	You Must Be The Change. You Wish To See In The World.
9	LOWER(A6)	you must be the change. you wish to see in the world.

การใช้งานฟังก์ชันชุดนี้ก็ขึึ้นอยู่กับว่าเหมาะที่จะใช้กับข้อความแบบไหน

Excel2007 Text Functions การใช้ฟังก์ชั่นตัวอักษร

Text Function หรือ ฟังก์ชั่นที่ใช้กับตัวอักษร

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

No.	Function	ผลลัพท์
1.	LEN	ใช้นับ alphabet หรือ จำนวนตัวอักษรในเซลส์ ที่เราต้องการ
2.	UPPER	ทำให้เป็นตัวพิมพ์ใหญ่ สำหรับภาษาไทยจะแสดงผล เป็นตัวอักษรเดิม
3.	LOWER	ทำให้ตัวหนังสือ เป็นตัวพิมพ์เล็กทั้งหมด ตรงข้ามกับ UPPER
4.	LEFT	เลือกตัวอักษร โดยยึดตัวอักษรซ้ายสุด หรือ ตัวแรก เป็นตัวที่ 1
5.	RIGHT	ลักษณะเหมือน LEFT เพียงแต่ยึดตัวอักษรขวาสุด/ตัวสุดท้าย เป็นตัวที่ 1
6.	MID	เลือกตัวอักษรภายในเซลส์ช่วงใดก็ได้ แล้วกำหนดลำดับ ถัดไปกี่ตัวอักษร
7.	SEARCH	ค้นหาตัวอักษรที่เราต้องการว่าอยู่ที่ตำแหน่งที่เท่าไร
8.	BAHTTEXT	ใช้แปลงมูลค่าตัวเลข ให้เป็นมูลค่าเงินบาท

เราจะใช้ตัวอย่างการใช้สูตรต่างๆ กับคำว่า Excel Learning ซึ่งเราวางคำนี้ไว้ที่เซลส์ E4 และ ตัวเลข 500 ซึ่งอยู่ที่เซลส์ F4 

Row Numer	Column Letter
3	C	D	E	F
4	ใช้สูตรกับเซลส์ E4 และ F4		Excel Learning	500
5	No	Function	การใช้ฟังก์ชั่น	ผลลัพธ์
6	1	LEN	=LEN(E4)	14
7	2	UPPER	=UPPER(E4)	EXCEL LEARNING
8	3	LOWER	=LOWER(E4)	excel learning
9	4	LEFT	=LEFT(E4,5)	Excel
10	5	RIGHT	=RIGHT(E4,8)	Learning
11	6	MID	=MID(E4,7,5)	Learn
12	7	SEARCH	=SEARCH(c,E4)	3
13	8	BAHTTEXT	=BAHTTEXT(F4)	ห้าร้อยบาทถ้วน

รูปตัวอย่าง การใช้งานบน Worksheet ของ Excel 2007

ข้อ 1-7 เป็นการทดสอบฟังก์ชั่น 7 แบบโดยกำหนดให้ใช้กับเซลส์ E4 ที่มีคำว่า "Excel Learning"
ข้อ 8 เป็นการทดสอบฟังก์ชั่น BAHTTEXT โดยกำหนดให้ใช้กับเซลส์ F4 ที่มีตัวเลข "500"

การรวมข้อความใน Excel ด้วย & (ampersand) และ Concatenate

การบันทึกข้อมูลลูกค้าอาจเป็นแบบดังรูปข้างบนคือแยกคำนำหน้า, ชื่อ, สกุล ไว้คนละคอลัมท์ ซึ่งถ้าต้องการนำข้อมูลลูกค้ามาพิมพ์ label เพื่อส่งจดหมาย เราสามารถใช้การเชื่อมคำให้แต่ละคอลัมท์มาอยู่ในเซลส์เดียวเป็นกันโดยใช้ เครื่องหมาย & เชื่อมแต่ละคอลัมท์ หรือใช้ฟังก์ชัน Concatenate

ตารางแสดงการใช้ &(ampersand) กับ concatenate

Row	column
1	A	B	C	D	E
2	คำนำหน้า	ชื่อ	สกุล	การใช้ & เพื่อเชื่อมคำแต่ละคอลัมท์	ผลลัพท์
3	นาย	หนึ่ง	สกุลหนึ่งหนึ่ง	=A3&" "&B3&" "&C3	นาย หนึ่ง สกุลหนึ่งหนึ่ง
4	นส.	สอง	สกุลสองสอง	=CONCATENATE(A4," ",B4," ",C4)	นส. สอง สกุลสองสอง
5	นาย	สาม	สกุลสามสาม	=A5&B5&C5	นายสามสกุลสามสาม
6	ดญ.	สี่	สกุลสี่สี่	=CONCATENATE(A6,B6,"  "C6	ดญ.สี่  สกุลสี่สี่

อธิบายตารางแสดงการใช้ &(ampersand) กับ concatenate

1. ใช้ &" "& รวมคำแต่ละคอลัมท์,สังเกตุว่าจะมีเคาะเว้นใน "" ซึ่งจะทำให้มีช่องว่างระหว่าง A3 (นาย) , B3 (หนึ่ง) และ C3 (สกุลหนึ่งหนึ่ง)
2. ถ้าใช้ฟังก์ชัน CONCATENATE ให้เพิ่ม ," ", คั่นระหว่างสตริงก็ได้ผลลัพท์เดียวกับ &" "&
3. กรณีที่ใช้ & ติดต่อกันไป จะไม่มีช่องว่างระหว่างคำที่นำมารวมกัน (นายสามสกุลสามสาม)
4. ถ้าอยากให้มีช่องว่าง 2 ช่องก็ให้เพิ่มเคาะเข้าไประหว่างเครื่องหมาย ""(ดญ.สี่  สกุลสี่สี่)