Excel: วิธีแยกข้อความที่คั่นเป็นแถว

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

ที่นี่แนะนำสองวิธีในการแก้ปัญหานี้

แยกข้อความที่คั่นเป็นแถวโดยใช้ VBA

แยกข้อความที่ใช้ตัวคั่นเป็นแถวโดยใช้ Power Query

นี่คือโค้ด VBA ที่สามารถแบ่งข้อความที่คั่นด้วยตัวคั่นเป็นแถวได้อย่างรวดเร็ว

1 กด อื่น ๆ + F11 คีย์เพื่อเปิดใช้งานไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2 คลิก สิ่งที่ใส่เข้าไป แท็บ> โมดูล เพื่อสร้างโมดูลเปล่าใหม่

3. คัดลอกและวางโค้ดด้านล่างลงในโมดูลเปล่า

VBA: แยกข้อความที่คั่นด้วยแถว

Public Sub SplitTextInCellsToRows()
'UpdatebyExtendoffice20220622
    Dim xSRg, xIptRg, xCrRg, xRg As Range
    Dim xSplitChar As String
    Dim xArr As Variant
    Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
    Dim xWSh As Worksheet
    Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSplitChar = Application.InputBox("Type delimiter:", "Kutools for Excel", , , , , , 2)
    If xSplitChar = "" Then Exit Sub

    Application.ScreenUpdating = False
    xRow = xSRg.Row
    xColumn = xSRg.Column
    Set xWSh = xSRg.Worksheet
    For xFNum = xSRg.Rows.Count To 1 Step -1
        
        Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
        Debug.Print xRg.Address
        xArr = Split(xRg, xSplitChar)
        For xFFNum = LBound(xArr) To UBound(xArr)
            xRg.EntireRow.Copy
            xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
            xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xFFNum)
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

4 กด F5 ที่สำคัญเพื่อคลิกปุ่มเรียกใช้  เพื่อเปิดใช้งานรหัส กล่องโต้ตอบจะปรากฏขึ้นเพื่อให้คุณเลือกคอลัมน์ที่ไม่รวมส่วนหัวที่มีข้อความคั่นซึ่งจำเป็นต้องแยกออกเป็นแถว คลิก OK.

5. จากนั้นกล่องโต้ตอบที่สองจะปรากฏขึ้นเพื่อป้อนตัวคั่นที่คุณใช้ ที่นี่พิมพ์จุลภาค (,) คลิก OK.

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

  VBA จะเปลี่ยนข้อมูลเดิม คุณควรบันทึกสำเนาไว้ก่อนหน้านี้

ถ้าคุณอยู่ใน Excel 2013 หรือเวอร์ชันที่ใหม่กว่า Power Query จะเป็นทางเลือกที่ดีสำหรับการแก้ปัญหานี้

1. เลือกช่วงข้อมูลเพื่อคลิก ข้อมูล > จากตาราง/ช่วง.

2. ใน popping สร้างตาราง โต้ตอบตรวจสอบ ตารางของฉันมีส่วนหัว ตามที่คุณต้องการและคลิก OK.

แล้ว Power Query บรรณาธิการ หน้าต่างจะปรากฏขึ้นและมีการแสดงข้อมูลในนั้น

3. คลิกที่คอลัมน์ที่คุณต้องการแยกข้อความที่คั่นเป็นแถว แล้วคลิก แปลง แท็บ> แยกคอลัมน์ > โดยตัวคั่น.

หรือคุณสามารถเลือกคอลัมน์และคลิกขวาที่ส่วนหัวของคอลัมน์เพื่อเลือกคุณลักษณะนี้ในเมนูบริบท

4 ใน แยกคอลัมน์ตามตัวคั่น หน้าต่าง เลือกตัวคั่นตามที่คุณต้องการจาก เลือกหรือป้อนตัวคั่น จากนั้นเลือก การเกิดขึ้นของตัวคั่นแต่ละครั้ง ตัวเลือกแล้วขยาย ตัวเลือกขั้นสูง ส่วนให้เลือก แถว ตัวเลือก คลิก OK.

ตอนนี้ข้อความที่ใช้ตัวคั่นได้ถูกแบ่งออกเป็นแถว

5. จากนั้นปิดหน้าต่าง กล่องโต้ตอบจะปรากฏขึ้น คลิก เก็บ ปุ่ม

จากนั้นแผ่นงานใหม่จะถูกสร้างขึ้นพร้อมกับตาราง

---

เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด

Kutools for Excel แก้ปัญหาส่วนใหญ่ของคุณ และเพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 80%

• ซุปเปอร์ฟอร์มูล่าบาร์ (แก้ไขข้อความและสูตรหลายบรรทัดได้อย่างง่ายดาย); การอ่านเค้าโครง (อ่านและแก้ไขเซลล์จำนวนมากได้อย่างง่ายดาย); วางลงในช่วงที่กรองแล้ว...
• ผสานเซลล์ / แถว / คอลัมน์ และการเก็บรักษาข้อมูล แยกเนื้อหาของเซลล์ รวมแถวที่ซ้ำกันและผลรวม / ค่าเฉลี่ย... ป้องกันเซลล์ซ้ำ; เปรียบเทียบช่วง...
• เลือกซ้ำหรือไม่ซ้ำ แถว; เลือกแถวว่าง (เซลล์ทั้งหมดว่างเปล่า); Super Find และ Fuzzy Find ในสมุดงานจำนวนมาก สุ่มเลือก ...
• สำเนาถูกต้อง หลายเซลล์โดยไม่เปลี่ยนการอ้างอิงสูตร สร้างการอ้างอิงอัตโนมัติ ถึงหลายแผ่น ใส่สัญลักษณ์แสดงหัวข้อย่อย, กล่องกาเครื่องหมายและอื่น ๆ ...
• แทรกสูตรที่ชื่นชอบและรวดเร็ว, ช่วงแผนภูมิและรูปภาพ; เข้ารหัสเซลล์ ด้วยรหัสผ่าน; สร้างรายชื่อผู้รับจดหมาย และส่งอีเมล ...
• แยกข้อความ, เพิ่มข้อความ, ลบตามตำแหน่ง, ลบ Space; สร้างและพิมพ์ผลรวมย่อยของเพจ แปลงระหว่างเนื้อหาของเซลล์และความคิดเห็น...
• ซุปเปอร์ฟิลเตอร์ (บันทึกและใช้โครงร่างตัวกรองกับแผ่นงานอื่น ๆ ); การเรียงลำดับขั้นสูง ตามเดือน / สัปดาห์ / วันความถี่และอื่น ๆ ตัวกรองพิเศษ โดยตัวหนาตัวเอียง ...
• รวมสมุดงานและแผ่นงาน; ผสานตารางตามคอลัมน์สำคัญ แยกข้อมูลออกเป็นหลายแผ่น; Batch แปลง xls, xlsx และ PDF...
• การจัดกลุ่มตาราง Pivot ตาม จำนวนสัปดาห์วันในสัปดาห์และอื่น ๆ ... แสดงปลดล็อกเซลล์ที่ถูกล็อก ด้วยสีที่ต่างกัน เน้นเซลล์ที่มีสูตร / ชื่อ...

อ่านเพิ่มเติม ... คุณสมบัติเต็มรูปแบบ ทดลองใช้งานฟรี 30 วัน... ซื้อ... 

---

Office Tab - นำอินเทอร์เฟซแบบแท็บมาสู่ Office และทำให้งานของคุณง่ายขึ้นมาก

• เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
• เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
• เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!

อ่านเพิ่มเติม ... คุณสมบัติเต็มรูปแบบ ทดลองใช้งานฟรี 30 วัน... ซื้อ...