Excel Forum

ขอรบกวนอาจารย์อีกครั้งครับ ผมต้องการแยกตัวเลขลำดับที่ปนอยู่ในข้อความให้แสดงผลออกเป็นสองคอลัมน์ต้องทำอย่างไร จึงจะสะดวกที่สุดครับ

ลองตามด้านล่างครับ

1. ที่ B3 คีย์สูตรเพื่อตัดตัวเลขออกมา

=IF(ISNUMBER(LEFT(A3,1)+0),LEFT(SUBSTITUTE(A3,".",REPT(" ",10)),10)+0,"")

Enter > Copy ลงด้านล่าง

2. ที่ C3 คีย์สูตรเพื่อตัดค่าอักษรออกมา

=SUBSTITUTE(A3,B3&".","")

Enter > Copy ลงด้านล่าง

เยี่ยมเลยครับ ขอบคุณอาจารย์มากๆ ครับ

อาจารย์ครับ มีปัญหาขอเรียนถามเพิ่มเติมเล็กน้อยครับ

บางแถวมีรูปแบบตามภาพประกอบด้านล่าง จะปรับสูตรอย่างไรที่ทำให้ตัวเลขลำดับที่ทั้งหมดมาอยู่ในคอลัมน์ B ครับ

ลองตามนี้ครับ

ปรับสูตรที่ B3 ใหม่เป็น

=IF(ISNUMBER(LEFT(A3,1)+0),LEFT(A3,FIND("@",SUBSTITUTE(A3,".","@",LEN(LEFT(A3,10))-LEN(SUBSTITUTE(LEFT(A3,10),".",""))))),"")

Enter > Copy ลงด้านล่าง ส่วนที่ C3 ใช้สูตรเดิมครับ

อาจารย์ครับ ผลลัพธ์ของคอลัมน์ C เปลี่ยนไปครับ
ขอรบกวนอาจารย์อีกครั้งครับ

snasui wrote: ส่วนที่ C3 ใช้สูตรเดิมครับ

ผมบอกผิดไปครับ ลืมสังเกตว่าสูตรเดิมมีส่วนที่เกินความต้องการเข้ามาด้วย

เปลี่ยน C3 จาก

=SUBSTITUTE(A3,B3&".","")

เป็น

=SUBSTITUTE(A3,B3,"")

Enter > Copy ลงด้านล่าง

ทำได้แล้วครับ ขอบคุณอาจารย์มากๆ ครับ

เรียนถามอาจารย์ครับ ถ้าผมอยากลบตัวเลขออกจาก ชื่อตามตัวอย่าง นี้ แต่มันมีหลาย เซลนะครับเช่น
12365436313654163131313 อโนทัย จำมัย
45131346131331313131 สงกรานต์ จ่าจับ
161613131313113131 อาธร อาจิณ
ต้องการเอาตัวเลขออกให้เหลือเป็น
อโนทัย จำมัย
สงกรานต์ จ่าจับ
อาธร อาจิณ

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

ตัวอย่างไฟล์ ทดลองทำดูแต่ถ้าเจอชื่อสั้น ต้องทำอย่างไรครับ ถึงจะไม่ผิดพลาด

ลองตามนี้ครับ

ที่เซลล์ B1 คีย์สูตร

=TRIM(MID(A1,FIND(" ",A1),255))

Enter > Copy ลงด้านล่าง

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

snasui wrote: ลองตามนี้ครับ

ที่เซลล์ B1 คีย์สูตร

=TRIM(MID(A1,FIND(" ",A1),255))

Enter > Copy ลงด้านล่าง

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

ขอบคุณมากครับอาจารย์ ที่อธิบายด้วย ผมจะไปศึกษาเพิ่มว่าสูตรทำงานอย่างไรครับ ดีกว่าที่คิดเองมากเลย มันทำงานได้จริงๆด้วยครับ มหัศจรรย์มากเลยครับ คิดได้ไงทึ่งครับ

http://www.snasui.com/viewtopic.php?f=10&t=3688
เรียน อาจารย์ ช่วยอธิบายหน่อยครับ
เกี่ยวกับกระทู้นี้ ผมทำไม่ได้ครับ
ตัวอย่างแนบมาด้วยครับ
สงสัย ว่า เกียวกับ Ctrl+Shift+Enter ตรงเซลล์ C3 หรือเปล่า ผมใช้ กอร์ปแล้ววางธรรมดาครับ

ตอบตามที่คิดได้ก่อนน่ะครับ

1. เรียงลำดับพยางค์ของชื่อนำหน้า จากมากไปน้อย โดยที่ G2 คีย์ =LEN(F2) คัดลอกลงมา
2. copy ค่าที่ได้มา แล้ววางที่เดิมโดยการวางค่า (Paste Value)
3. ทำการ Sort ข้อมูลจากมากไปน้อย

4 ที่ C3 คีย์ =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,$F$2,""),$F$3,""),$F$4,""),$F$5,""),$F$6,""),$F$7,""),$F$8,""),$F$9,""),$F$10,""),$F$11,""),$F$12,"") คัดลอกลงมา

5 ที่ D3 คีย์ =SUBSTITUTE(C3,LEFT(C3,FIND(" ",C3)),"") คัดลอกลงมา
6 ที่ B3 คีย์ =SUBSTITUTE(A3,C3,"") คัดลอกลงมา

bank9597 wrote: ตอบตามที่คิดได้ก่อนน่ะครับ

1. เรียงลำดับพยางค์ของชื่อนำหน้า จากมากไปน้อย โดยที่ G2 คีย์ =LEN(F2) คัดลอกลงมา
2. copy ค่าที่ได้มา แล้ววางที่เดิมโดยการวางค่า (Paste Value)
3. ทำการ Sort ข้อมูลจากมากไปน้อย

4 ที่ C3 คีย์ =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,$F$2,""),$F$3,""),$F$4,""),$F$5,""),$F$6,""),$F$7,""),$F$8,""),$F$9,""),$F$10,""),$F$11,""),$F$12,"") คัดลอกลงมา

5 ที่ D3 คีย์ =SUBSTITUTE(C3,LEFT(C3,FIND(" ",C3)),"") คัดลอกลงมา
6 ที่ B3 คีย์ =SUBSTITUTE(A3,C3,"") คัดลอกลงมา

ขอบคุณครับ
ลองโหลดไปดูแล้ว แต่ยังไม่เข้าใจการใช้งานครับ ช่อง f กับ g มีไว้ทำไมครับ
หากเราจะนำไปใช้งาน จะกอร์ปสูตรไปใช้ ที่ตารางอื่นต้องทำไงบ้างครับ
สมมติ เรามี ตาราง A อย่างเดียว
เราต้องคีย์ ตารางไหนก่อนหลังครับ งงมาก

หากไม่ใช้ตาราง เราก็เข้าไปปรับในสูตร ตารางมีไว้ list ชื่อนำหน้าว่ามีกี่คำเท่านั้นเองครับ

หากไม่ต้องการใช้ตาราง เราสามารถปรับสูตรดังนี้

ที่ B2 คีย์ =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"เด็กหญิง",""),"เด็กชาย",""),"นางสาว",""),"ร.ต.อ.",""),"พล.อ.",""),"น.ส.",""),"ด.ช.",""),"ด.ญ.",""),"นาง",""),"นาย",""),"ดร.","")
คัดลอกลงมา

มีเทคนิคอยู่ว่า ชื่อนำหน้าที่มีพยางค์มากที่สุดต้องอยู่หน้าเสมอ แล้วค่อยๆเรียงจนถึงพยางค์น้อยที่สุด ลองดูในสูตรครับ

ส่วนสูตรอื่นๆก็ใช้เหมือนเดิมครับ

ซำเหมา wrote:http://www.snasui.com/viewtopic.php?f=10&t=3688
เรียน อาจารย์ ช่วยอธิบายหน่อยครับ
เกี่ยวกับกระทู้นี้ ผมทำไม่ได้ครับ
ตัวอย่างแนบมาด้วยครับ
สงสัย ว่า เกียวกับ Ctrl+Shift+Enter ตรงเซลล์ C3 หรือเปล่า ผมใช้ กอร์ปแล้ววางธรรมดาครับ

Copy มาธรรมดาไม่ได้ครับ จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ตามที่สงสัยนั้นถูกต้องแล้วครับ

ตามไฟล์ที่แนบมา

1. คลิกที่เซลล์ C3
2. กดแป้น F2 เพื่อทำการ Edit เซลล์
3. กดแป้น Ctrl+Shift ค้างไว้แล้วตามด้วย Enter
4. Copy C3 ลงด้านล่าง

snasui wrote:

ซำเหมา wrote:http://www.snasui.com/viewtopic.php?f=10&t=3688
เรียน อาจารย์ ช่วยอธิบายหน่อยครับ
เกี่ยวกับกระทู้นี้ ผมทำไม่ได้ครับ
ตัวอย่างแนบมาด้วยครับ
สงสัย ว่า เกียวกับ Ctrl+Shift+Enter ตรงเซลล์ C3 หรือเปล่า ผมใช้ กอร์ปแล้ววางธรรมดาครับ

Copy มาธรรมดาไม่ได้ครับ จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ตามที่สงสัยนั้นถูกต้องแล้วครับ

ตามไฟล์ที่แนบมา

1. คลิกที่เซลล์ C3
2. กดแป้น F2 เพื่อทำการ Edit เซลล์
3. กดแป้น Ctrl+Shift ค้างไว้แล้วตามด้วย Enter
4. Copy C3 ลงด้านล่าง

ขอบคุณมากครับทำได้แล้วครับ
จริงๆอาจารย์เขียนขั้นตอนไว้ดีมากแล้วครับ
แต่ผมเอง งงตรงที่ว่า ในเซลล์ C3 ทำไมเราใช้ COPY แล้ว วางไม่ได้
ต้อง วางแล้ว กด Ctrl+Shift ค้างไว้แล้วตามด้วย Enter
จึงจะใช้งานได้ ผมเองไม่สามารถเข้าใจคำสั่งทั้งหมดได้ครับ แต่ก็ลอกไปใช้งานได้ มีประโยชน์มากครับ ขอบคุณจริงๆ ช่วยเหลือได้ทุกครั้งเลยครับ