Page 1 of 1
การแยกตัวเลขออกจากตัวอักษร
Posted: Thu Oct 06, 2016 8:23 am
by pongpang
เรียน อาจารย์ทุกท่านครับ
ต้องการแยกตัวเลขใน คอลัมน์ A ออกจากตัวอักษร ไว้ในคอลัมน์ B โดยใช้สูตร ครับ
แยกตัวเลขออกจากตัวอักษร.xlsx
Re: การแยกตัวเลขออกจากตัวอักษร
Posted: Thu Oct 06, 2016 1:36 pm
by puriwutpokin
แก้ไขตัวสีแดงใหม่อีกทีครับ ให้เป็น" =LOOKUP(9.99999999999999E+307,--ID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&”0123456789″)),ROW(INDIRECT("1:"&LEN(A1)))))
เป็น=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))
Re: การแยกตัวเลขออกจากตัวอักษร
Posted: Thu Oct 06, 2016 2:30 pm
by pongpang
ขอบคุณครับที่ให้คำแนะนำ แต่ยังไม่ได้ตามต้องการ ปัญหา คือ เมื่อตัวเลขกับตัวพยัญชนะอยู่ระหว่างตัวเลข เช่น 1หนึ่ง2สอง3สาม จะแสดงตัวเลข เฉพาะเลข1 เท่านั้น หรือ ห้าหกเจ็ด567แปดเก้าสิบ8910 จะแสดงเฉพาะ 567 คือกลุ่มแรกเท่านั้นครับ
สรุปว่า แสดงได้เฉพาะตัวเลขตัวแรกหรือกลุ่มแรกเท่านั้น ถ้ามีตัวพยัญชนะคั่นอยู่เลขตัวหลังหรือกลุ่มหลังไม่แสดงครับ
ความต้องการ ต้องการให้แสดงตัวเลขทุกตัวครับ
แยกตัวเลขออกจากตัวอักษร.xlsx
Re: การแยกตัวเลขออกจากตัวอักษร
Posted: Thu Oct 06, 2016 2:55 pm
by puriwutpokin
ถ้าเอาทุกตัวที่เป็นตัวเลข คงต้องใช้ VB ครับ เขียนหรือค้นหามาก่อนครับ ตามกฏข้อ5 ครับ แล้วเพื่อนๆจะได้ช่วยดูได้ครับ
Re: การแยกตัวเลขออกจากตัวอักษร
Posted: Thu Oct 06, 2016 5:19 pm
by vichaim
แบบนี้หรือเปล่าครับที่ต้องการ
Re: การแยกตัวเลขออกจากตัวอักษร
Posted: Thu Oct 06, 2016 7:35 pm
by snasui
pongpang wrote:เรียน อาจารย์ทุกท่านครับ
ต้องการแยกตัวเลขใน คอลัมน์ A ออกจากตัวอักษร ไว้ในคอลัมน์ B โดยใช้สูตร ครับ
แยกตัวเลขออกจากตัวอักษร.xlsx
ตัวอย่างสูตรครับ
ที่เซลล์ B1 คีย์
=SUM(MID(A1,SMALL(IF(ISNUMBER(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))),ROW(INDIRECT("1:"&COUNT(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1))))),1)*10^(COUNT(ROW(INDIRECT("1:"&COUNT(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))+1-ROW(INDIRECT("1:"&COUNT(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))/10)
Ctrl+Shift+Enter > Copy ลงด้านล่าง สูตรนี้มีข้อจำกัดคือหากมีเลข 0 อยู่ด้านหน้าจะไม่สามารถนำมาแสดงได้ครับ
Re: การแยกตัวเลขออกจากตัวอักษร
Posted: Sat Oct 08, 2016 5:12 pm
by pongpang
เรียน อาจารย์ทุกท่านครับ
ขอบคุณมากครับ ใช้ได้แล้วครับ
Re: การแยกตัวเลขออกจากตัวอักษร
Posted: Sat Oct 08, 2016 11:02 pm
by DhitiBank
snasui wrote:
ที่เซลล์ B1 คีย์
=SUM(MID(A1,SMALL(IF(ISNUMBER(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))),ROW(INDIRECT("1:"&COUNT(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1))))),1)*10^(COUNT(ROW(INDIRECT("1:"&COUNT(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))+1-ROW(INDIRECT("1:"&COUNT(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))/10)
Ctrl+Shift+Enter > Copy ลงด้านล่าง สูตรนี้มีข้อจำกัดคือหากมีเลข 0 อยู่ด้านหน้าจะไม่สามารถนำมาแสดงได้ครับ
โอ้ว... มีให้นั่งแกะอีกแล้ว ขอบคุณอาจารย์มากๆ ครับ ปกติใช้วิธีเขียนสูตรแยกเลขออกมาทีละตัวแล้วค่อยเชื่อมกลับเข้าไปใหม่ ตอนนี้ก็ไม่ต้องแล้ว
ขออนุญาตนำมาคิดต่อครับ หากมี 0 นำหน้า ผมเอา "1" มาเชื่อม แล้วใช้ Mid ตัดผลลัพธ์สุดท้ายตั้งแต่ตำแหน่ง 2 เป็นต้นไปครับ
=MID(SUM(MID("1"&A1,SMALL(IF(ISNUMBER(-MID("1"&A1,ROW(INDIRECT("1:"&LEN("1"&A1))),1)),ROW(INDIRECT("1:"&LEN("1"&A1)))),ROW(INDIRECT("1:"&COUNT(-MID("1"&A1,ROW(INDIRECT("1:"&LEN("1"&A1))),1))))),1)*10^(COUNT(-MID("1"&A1,ROW(INDIRECT("1:"&LEN("1"&A1))),1))-ROW(INDIRECT("1:"&COUNT(-MID("1"&A1,ROW(INDIRECT("1:"&LEN("1"&A1))),1)))))),2,255)
Re: การแยกตัวเลขออกจากตัวอักษร
Posted: Sat Oct 08, 2016 11:17 pm
by snasui
ผมเขียนห้วนไปหน่อยครับ
ผมหมายถึงว่าเมื่อแปลงให้เป็น Number มันจะตัดศูนย์ด้านหน้าทิ้งไป ซึ่งเป็นข้อจำกัดพื้นฐานของตัวเลขแบบ Text ที่ถูกแปลงไปเป็น Number ครับ
กรณีต้องการตัวเลขแบบ Text สามารถเชื่อม 0 เข้าไปก่อนจำนวนหนึ่งมาช่วยก็เป็นอีกทางเลือกครับ สูตรก็จะอยู่ใน Form ตามด้านล่างครับ
=Right(Rept(0,15)&OldExtractNumberFromText_formula,COUNT(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1))
Re: การแยกตัวเลขออกจากตัวอักษร
Posted: Sun Oct 09, 2016 10:40 pm
by DhitiBank
snasui wrote:กรณีต้องการตัวเลขแบบ Text สามารถเชื่อม 0 เข้าไปก่อนจำนวนหนึ่งมาช่วยก็เป็นอีกทางเลือกครับ สูตรก็จะอยู่ใน Form ตามด้านล่างครับ
=Right(Rept(0,15)&OldExtractNumberFromText_formula,COUNT(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1))
ขอบคุณมากๆ เลยครับ
หาก Excel มีฟังก์ชันสำเร็จที่เชื่อมอักขระที่ได้จากการตัดแบบอาร์เรย์ด้วยคงสะดวกขึ้นมากๆ เลยนะครับ