Snasui Excel Forum

คลังคำตอบแห่งความรู้จากคนไทย เพื่อโลกที่ต้องการเข้าใจในสิ่งเล็ก ๆ อย่างลึกซึ้ง
http://www.snasui.com/

สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

http://www.snasui.com/viewtopic.php?t=9090

Page 1 of 1

สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Tue Sep 08, 2015 11:06 am
by boonyanood
สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง ไม่เอาช่องว่าง , ตัวเลข , อักขระใดๆ

ตัวอย่างข้อความนะคะ
วงศ์ภัทรM08901909
คุณสุนทรี081446854
ประทีป (ฝ่ายรับประกัน)
ศิระ(ฝ่ายรับประกัน)
&22/05/58 คุณดิษฐวัฒน์
วัชระ/03/07/58
&3/6/58/คุณพุฒิพัฒน์
โทรแจ้ง คุณสมพงษ์ 081668537
นิราวัล&08/06/58-59

รบกวนขอคำแนะนำจากสมาชิกทุกท่าน
ขอบคุณค่ะ

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Tue Sep 08, 2015 1:39 pm
by bank9597
หากใช้สูตรคงต้องเขียนสูตรยาวครับ รบกวนท่านที่เก่งมาช่วยดู

แต่ถ้าใช้ vba ก็จะทำได้ง่ายมาก แต่ต้องเขียนมาเองก่อน ซึ่งเป็นไปตามกฏครับ

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Tue Sep 08, 2015 9:51 pm
by DhitiBank
ยากจริง รูปแบบตำแหน่งของชื่อไม่แน่นอน
สูตร ผมไม่ไหว VBA ผมนึกไม่ออก ปูเสื่อรอดูคำตอบดีกว่า :tt:

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Tue Sep 08, 2015 10:32 pm
by snasui
:D ตัวอย่างสูตรที่พอช่วยได้ครับ

สมมุติข้อมูลเริ่มที่ A2
  1. เซลล์ B2 คีย์สูตรเพื่อหาลำดับที่ของอักขระที่มี Code น้อยกว่า 161 หมายถึงค่าที่น้อยกว่าตัว ก.ไก่
    =AGGREGATE(15,6,1/(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<161)*ROW(INDIRECT("1:"&LEN(A2))),1)-1
    Enter > Copy ลงด้านล่าง
  2. เซลล์ C2 คีย์สูตรเพื่อหาลำดับที่ของอักขระที่มี Code มากกว่าหรือเท่ากับ 161 หมายถึงค่าที่มากกว่าหรือเท่ากับตัว ก.ไก่
    =AGGREGATE(15,6,1/(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=161)*ROW(INDIRECT("1:"&LEN(A2))),1)
  3. เซลล์ D2 คีย์สูตรเพื่อตัดเฉพาะชื่อ
    =IF(B2>0,LEFT(A2,B2),MID(A2,C2,255))
    Enter > Copy ลงด้านล่าง
  4. Manual Replace ค่าในคอลัมน์ A เช่นคำว่า "โทรแจ้ง " ให้เป็นค่าว่าง สังเกตคอลัมน์ D
เนื่องจากมีความหลากหลายในข้อมูล จึงต้องทำข้อ 4 เพิ่มเติม

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

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Wed Sep 09, 2015 9:31 am
by boonyanood
ใช้ excel 2007 อยู่ค่ะ
ไม่มีคำสั่ง AGGREGATE
พอจะมีคำสั่งอื่นไหมคะ

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Wed Sep 09, 2015 10:04 am
by snasui
:D ปรับสูตรเป็นตามด้านล่างครับ
  1. B2
    =MATCH(1,INDEX(1/(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<161),0),0)-1
    Enter > Copy ลงด้านล่าง
  2. C2
    =MATCH(1,INDEX(1/(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=161),0),0)
    Enter > Copy ลงด้านล่าง
  3. ที่เหลือทำเหมือนเดิม

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Wed Sep 09, 2015 12:27 pm
by jelelite
ตามมาศึกษาสูตรจากอาจารย์ :o


:cp: :thup:

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Thu Sep 10, 2015 9:04 pm
by bank9597
ยากจริง รูปแบบตำแหน่งของชื่อไม่แน่นอน
สูตร ผมไม่ไหว VBA ผมนึกไม่ออก ปูเสื่อรอดูคำตอบดีกว่า
หากใช้ vba เราสามารถทำได้ง่ายด้วยการ Loop เพียงแต่ เราต้องระบุอักขระที่ต้องการจะตัดออกไป และสั่ง Run โค๊ดเป็นรอบๆไป

เราคงระบุอักขระที่ต้องการแบบชัดเจนไม่ได้ แต่เราสามารถตัดคำหลายครั้งได้ด้วยตัวอย่างโค๊ดนี้ครับ

ระบุตัวหนังสือที่ต้องการตัดลงในเซลล์ C1
หากไม่ระบุอักษรใดๆ โค๊ดจะตัดตัวเลขออกทั้งหมด
จากนั้นค่อยๆทยอยตัดตัวอักษรที่เราต้องการ จนกว่าจะได้ผลลัพธ์ตามที่ต้องการครับ

Code: Select all

Option Explicit
Sub Rectangle1_Click()
        Dim strVal As Variant
        strVal = Sheets("Sheet1").Range("C1")
        f_CutText (strVal)
End Sub

Code: Select all

Public Function f_CutText(ByVal strValue As Variant)
        Dim ws As Worksheet
        Dim rRange As Range
        Dim tRange As Range
        Dim i As Integer

        Set ws = Worksheets("Sheet1")

        Set rRange = ws.Range("A2:A" & ws.Range("A" & Rows.Count).End(xlUp).Row)

        For Each tRange In rRange
            If strValue = "" Then
                For i = 0 To 9
                    tRange = Trim(WorksheetFunction.Substitute(tRange, i, ""))
                Next i
            Else
                tRange = Trim(WorksheetFunction.Substitute(tRange, strValue, ""))
            End If
        Next tRange

        Set ws = Nothing
        Set rRange = Nothing

End Function

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Thu Sep 10, 2015 9:22 pm
by snasui
:D เมื่อเขียน Code แล้วควรลดการ Run แบบซ้ำ ๆ ลงได้ อย่างน้อยต้องดีกว่าการ Replace ด้วย Manual ไม่เช่นนั้นไม่คุ้มค่ากับเวลาที่ใช้เขียน Code ครับ

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Thu Sep 10, 2015 10:03 pm
by bank9597
snasui wrote::D เมื่อเขียน Code แล้วควรลดการ Run แบบซ้ำ ๆ ลงได้ อย่างน้อยต้องดีกว่าการ Replace ด้วย Manual ไม่เช่นนั้นไม่คุ้มค่ากับเวลาที่ใช้เขียน Code ครับ
:D จริงอย่างนั้นครับอาจารย์

ถ้าจะลดการ Run ซ้ำ ผมก็ยังคิดได้วิธีเดียวเหมือนคนอื่นๆคือ การระบุอักขระที่ต้องการตัดออกไปให้ครบถ้วนที่สุดครับ

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Fri Sep 11, 2015 9:07 am
by DhitiBank
:thup: :cp: ขอบคุณทั้งคุณ bank9597 และอาจารย์มากๆ ครับ เป็นประโยชน์ต่อการศึกษามากครับ

ปล. ผมขอเพิ่มสมาชิกใน Array อีกสองตัวนะครับคือ "โทรแจ้ง","ฝ่ายรับประกัน" :) ไม่รู้จะได้หรือเปล่า ผมยังไม่มีคอมให้ลอง เปิดไฟล์ดูได้เฉยๆ แต่ทำอะไรไม่ได้

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Fri Sep 11, 2015 10:04 am
by bank9597
DhitiBank wrote::thup: :cp: ขอบคุณทั้งคุณ bank9597 และอาจารย์มากๆ ครับ เป็นประโยชน์ต่อการศึกษามากครับ

ปล. ผมขอเพิ่มสมาชิกใน Array อีกสองตัวนะครับคือ "โทรแจ้ง","ฝ่ายรับประกัน" :) ไม่รู้จะได้หรือเปล่า ผมยังไม่มีคอมให้ลอง เปิดไฟล์ดูได้เฉยๆ แต่ทำอะไรไม่ได้
:mrgreen: ใส่คำเฉพาะที่ต้องการตัดลงใน C1 เลยครับ หรือจะใส่ใน Array ก็ไม่มีปัญหาครับ

อันนี้ที่จริง อาจารย์คนควรมีโค๊ดที่สั้นและกระทัดรัดกว่าของผมน่ะครับ ถ้าอาจารย์จะกรุณา เราคงได้ศึกษาโค๊ดนั้นต่อไปครับ :D

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Fri Sep 11, 2015 11:00 am
by ZEROV
ฟังชันส่งกลับภาษาไทย

Code: Select all

Public Function tLang(xStr As Variant) As String
For i = 1 To Len(xStr)
x = Mid(xStr, i, 1)
If Asc(x) > 160 And Asc(x) < 240 Then tLang = tLang & x
Next i
End Function
ปัญหาที่เหลือก็เขียนการตัด Prefix และ Suffix

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Fri Sep 11, 2015 11:23 am
by bank9597
ZEROV wrote:ฟังชันส่งกลับภาษาไทย

Code: Select all

Public Function tLang(xStr As Variant) As String
For i = 1 To Len(xStr)
x = Mid(xStr, i, 1)
If Asc(x) > 160 And Asc(x) < 240 Then tLang = tLang & x
Next i
End Function
ปัญหาที่เหลือก็เขียนการตัด Prefix และ Suffix
:thup:

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Fri Sep 11, 2015 1:09 pm
by DhitiBank
ZEROV wrote:ฟังชันส่งกลับภาษาไทย

Code: Select all

Public Function tLang(xStr As Variant) As String
For i = 1 To Len(xStr)
x = Mid(xStr, i, 1)
If Asc(x) > 160 And Asc(x) < 240 Then tLang = tLang & x
Next i
End Function
ปัญหาที่เหลือก็เขียนการตัด Prefix และ Suffix
:o ขอบคุณครับ

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Fri Sep 11, 2015 1:19 pm
by DhitiBank
snasui wrote::D ปรับสูตรเป็นตามด้านล่างครับ
  1. B2
    =MATCH(1,INDEX(1/(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<161),0),0)-1
    Enter > Copy ลงด้านล่าง
  2. C2
    =MATCH(1,INDEX(1/(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=161),0),0)
    Enter > Copy ลงด้านล่าง
  3. ที่เหลือทำเหมือนเดิม
อาจารย์ครับ ขอสอบถามหน่อยครับ

1. การใช้ MID และ CODE แบบนี้ไม่ต้องกดแบบอาร์เรย์เหรอครับ

2. กรณีนี้ทำไมถึงต้องเอา Index มาช่วยในสูตร Match ด้วยครับ ไม่ต้องมี Index ไม่ได้เหรอครับ

(ตอนนี้ผมไม่มีคอมฯ เลยขออนุญาติถามก่อนครับ)

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Fri Sep 11, 2015 4:02 pm
by snasui
DhitiBank wrote:1. การใช้ MID และ CODE แบบนี้ไม่ต้องกดแบบอาร์เรย์เหรอครับ

2. กรณีนี้ทำไมถึงต้องเอา Index มาช่วยในสูตร Match ด้วยครับ ไม่ต้องมี Index ไม่ได้เหรอครับ
:D ตอบรวมกันทั้งสองข้อนะครับ

Index เป็นตัวช่วยให้ไม่ต้องกดแป้นแบบ Array เนื่องจากมีความเป็น Array แบบธรรมชาติเช่นเดียวกับ Lookup, Sumproduct ฟังก์ชั่นพวกนี้ไม่จำเป็นต้องกดแป้นแบบ Array ยกเว้นซ้อนฟังก์ชั่นเข้าไปหลายชั้นจนฟังก์ชั่นเหล่านี้แข็งแรงไม่พอที่จะคงความเป็น Array จำเป็นต้องกดแป้น Ctrl+Shift+Enter เข้าไปช่วยครับ

Re: สอบถามการตัดข้อความ ต้องการเฉพาะชื่อจริง

Posted: Fri Sep 11, 2015 4:52 pm
by DhitiBank
:o ซับซ้อนแท้ ขอบคุณอาจารย์มากครับ

อ๋อ คราวนี้ก็เข้าใจแล้วว่าเมื่อครู่มีโอกาสใช้ FLOOR กับ MODE แบบอาร์เรย์ซ้อนอยู่ใน SUMPRODUCT ทำไมมันถึงกด Enter แล้วแสดงคำตอบได้ในแอปมือถือ ทั้งๆ ที่มันน่าจะต้องกดแบบอาร์เรย์ ขอบคุณอีกครั้งครับ
All times are UTC+07:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited