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

[boonyanood]

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

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

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

[bank9597]

หากใช้สูตรคงต้องเขียนสูตรยาวครับ รบกวนท่านที่เก่งมาช่วยดู

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

[DhitiBank]

ยากจริง รูปแบบตำแหน่งของชื่อไม่แน่นอน
สูตร ผมไม่ไหว VBA ผมนึกไม่ออก ปูเสื่อรอดูคำตอบดีกว่า

[snasui]

ตัวอย่างสูตรที่พอช่วยได้ครับ

สมมุติข้อมูลเริ่มที่ 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 ว่าค่าใดที่ต้องขจัดออกไป เนื่องจากไม่ได้เป็นส่วนหนึ่งของชื่อ เช่นนี้เป็นต้น

[boonyanood]

ใช้ excel 2007 อยู่ค่ะ
ไม่มีคำสั่ง AGGREGATE
พอจะมีคำสั่งอื่นไหมคะ

[snasui]

ปรับสูตรเป็นตามด้านล่างครับ

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. ที่เหลือทำเหมือนเดิม

[jelelite]

ตามมาศึกษาสูตรจากอาจารย์

[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

[snasui]

เมื่อเขียน Code แล้วควรลดการ Run แบบซ้ำ ๆ ลงได้ อย่างน้อยต้องดีกว่าการ Replace ด้วย Manual ไม่เช่นนั้นไม่คุ้มค่ากับเวลาที่ใช้เขียน Code ครับ

[bank9597]

เมื่อเขียน Code แล้วควรลดการ Run แบบซ้ำ ๆ ลงได้ อย่างน้อยต้องดีกว่าการ Replace ด้วย Manual ไม่เช่นนั้นไม่คุ้มค่ากับเวลาที่ใช้เขียน Code ครับ

จริงอย่างนั้นครับอาจารย์

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

[DhitiBank]

ขอบคุณทั้งคุณ bank9597 และอาจารย์มากๆ ครับ เป็นประโยชน์ต่อการศึกษามากครับ

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

[bank9597]

ขอบคุณทั้งคุณ bank9597 และอาจารย์มากๆ ครับ เป็นประโยชน์ต่อการศึกษามากครับ

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

ใส่คำเฉพาะที่ต้องการตัดลงใน C1 เลยครับ หรือจะใส่ใน Array ก็ไม่มีปัญหาครับ

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

[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

[bank9597]

ฟังชันส่งกลับภาษาไทย

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

[DhitiBank]

ฟังชันส่งกลับภาษาไทย

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

ขอบคุณครับ

[DhitiBank]

ปรับสูตรเป็นตามด้านล่างครับ

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 ไม่ได้เหรอครับ

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

[snasui]

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

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

ตอบรวมกันทั้งสองข้อนะครับ

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

[DhitiBank]

ซับซ้อนแท้ ขอบคุณอาจารย์มากครับ

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