ไปยังเนื้อหาหลัก

วิธีแยกข้อความและตัวเลขจากเซลล์หนึ่งออกเป็นสองคอลัมน์

หากคุณมีคอลัมน์ของสตริงข้อความซึ่งประกอบด้วยข้อความและตัวเลขตอนนี้คุณต้องการแยกข้อความและตัวเลขจากเซลล์หนึ่งออกเป็นสองเซลล์ตามภาพหน้าจอต่อไปนี้ ใน Excel คุณสามารถทำงานนี้ให้เสร็จสิ้นได้ด้วยวิธีการเหล่านี้


วิธีที่ 1: แยกข้อความและตัวเลขด้วยสูตรใน Excel

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

1. ป้อนสูตรนี้ลงในเซลล์ว่าง - C3 ที่คุณต้องการวางผลลัพธ์: =LEFT(A3,MIN(หา)0,1,2,3,4,5,6,7,8,9 {},A3&"0123456789"))-1), (A3 คือเซลล์ที่มีสตริงข้อความที่คุณต้องการแยก) จากนั้นกด เข้าสู่ คีย์เพื่อรับเฉพาะข้อความจากเซลล์ A2 ดูภาพหน้าจอ:

doc แยกข้อความหมายเลข 2

2. จากนั้นคุณสามารถแยกตัวเลขออกจากเซลล์โดยใช้สูตรนี้: =ขวา(A3,LEN(A3)-MIN(FIND(0,1,2,3,4,5,6,7,8,9 {},A3&"0123456789"))+1),( A3 คือเซลล์ที่มีสตริงข้อความที่คุณต้องการแยก) ป้อนสูตรนี้ลงในเซลล์ D3 ที่คุณต้องการวางตัวเลขแล้วกด เข้าสู่ จากนั้นคุณจะได้ตัวเลขดังนี้:

doc แยกข้อความหมายเลข 3

3. จากนั้นเลือกเซลล์ C3: D3 แล้วลากจุดจับเติมไปยังเซลล์ที่คุณต้องการให้มีสูตรเหล่านี้และคุณจะเห็นข้อความและตัวเลขถูกแยกออกเป็นเซลล์ต่างๆ:

doc แยกข้อความหมายเลข 4


แยกหรือแยกสตริงข้อความเป็นแต่ละข้อความและคอลัมน์ตัวเลข:

Kutools สำหรับ Excel's แยกเซลล์ คุณลักษณะเป็นเครื่องมือที่มีประสิทธิภาพสามารถช่วยให้คุณแบ่งค่าของเซลล์ออกเป็นหลายคอลัมน์หรือหลายแถวและยังช่วยให้คุณสามารถแบ่งสตริงตัวอักษรและตัวเลขออกเป็นคอลัมน์ข้อความและตัวเลขที่แยกจากกัน ฯลฯ ... คลิกเพื่อดาวน์โหลด Kutools for Excel!

doc แยกข้อความหมายเลข 16

วิธีที่ 2: แยกข้อความและตัวเลขด้วย Flash Fill ใน Excel 2013 และเวอร์ชันที่ใหม่กว่า

ด้วยสูตรข้างต้นคุณสามารถแยกข้อความและตัวเลขได้หากข้อความอยู่ก่อนตัวเลข หากต้องการแยกสตริงข้อความว่าหมายเลขใดอยู่หน้าข้อความคุณสามารถใช้ไฟล์ เติม Flash คุณลักษณะของ Excel 2013 และเวอร์ชันที่ใหม่กว่า

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

1. พิมพ์ตัวเลขของสตริงข้อความแรกของคุณลงในเซลล์ว่างที่อยู่ติดกัน - B3 ดูภาพหน้าจอ:

doc แยกข้อความหมายเลข 5

2. จากนั้นเลือกช่วง B3: B7 ที่คุณต้องการเติมตัวเลขแล้วคลิก ข้อมูล > เติม Flashและมีการเติมตัวเลขลงในเซลล์พร้อมกันเท่านั้นดูภาพหน้าจอ:

doc แยกข้อความหมายเลข 6

3. จากนั้นป้อนสตริงข้อความลงในเซลล์ C3 อย่างสมบูรณ์ดูภาพหน้าจอ:

doc แยกข้อความหมายเลข 7

4. และเลือกช่วงเซลล์ C3: C7 ที่คุณต้องการเติมข้อความเท่านั้นคลิก ข้อมูล > เติม Flash เช่นเดียวกับขั้นตอนที่ 2 คุณจะเห็นข้อความถูกแยกออกดังนี้:

doc แยกข้อความหมายเลข 8

ปลาย: คุณยังสามารถลากจุดจับที่เติมไปยังช่วงที่คุณต้องการใช้จากนั้นคลิก ตัวเลือกการเติมอัตโนมัติ และตรวจสอบ เติม Flash.

doc แยกข้อความหมายเลข 9

วิธีที่ 3: แยกข้อความและตัวเลขที่ผสมผิดปกติกับ User Defined Function

หากคุณมีสตริงข้อความที่ผสมข้อความและตัวเลขไม่สม่ำเสมอเหมือนกับข้อมูลที่แสดงต่อไปนี้ Excel ไม่สนับสนุนคุณลักษณะทั่วไปในการแก้ปัญหานี้ แต่คุณสามารถสร้างฟังก์ชันที่กำหนดโดยผู้ใช้เพื่อทำสิ่งนี้ให้เสร็จสิ้น

doc แยกข้อความหมายเลข 17

1. กด ALT + F11 คีย์เพื่อเปิด หน้าต่าง Microsoft Visual Basic for Applications.

2. คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในไฟล์ หน้าต่างโมดูล.

รหัส VBA: แยกข้อความและตัวเลขลงในเซลล์ต่างๆจากเซลล์เดียว

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. จากนั้นบันทึกและปิดรหัสนี้กลับไปที่แผ่นงานป้อนสูตรนี้ = SplitText (A3, FALSE) ลงในเซลล์ว่างเพื่อรับสตริงข้อความเท่านั้นจากนั้นลากที่จับเติมลงไปที่เซลล์ที่คุณต้องการเติมสูตรนี้ดูภาพหน้าจอ:

doc แยกข้อความหมายเลข 10

4. และจากนั้นพิมพ์สูตร = SplitText (A3, TRUE) ลงในเซลล์อื่นแล้วลากที่จับเติมลงไปที่เซลล์ที่คุณต้องการเติมสูตรนี้เพื่อรับตัวเลขดูภาพหน้าจอ:

doc แยกข้อความหมายเลข 11

หมายเหตุ: ผลลัพธ์จะไม่ถูกต้องหากมีตัวเลขทศนิยมในสตริงข้อความ


วิธีที่ 4: แยกข้อความและตัวเลขออกเป็นสองคอลัมน์ด้วย Kutools for Excel

ถ้าคุณมี Kutools สำหรับ Excelด้วยเครื่องมืออันทรงพลัง - แยกเซลล์ ยูทิลิตี้คุณสามารถแบ่งสตริงข้อความออกเป็นสองคอลัมน์ได้อย่างรวดเร็ว: หนึ่งคือตัวเลขและอีกคอลัมน์คือข้อความ

หลังจากการติดตั้ง Kutools สำหรับ Excelโปรดดำเนินการดังนี้:

1. เลือกช่วงข้อมูลที่คุณต้องการแยกข้อความและตัวเลข

2. จากนั้นคลิก Kutools > ผสานและแยก > แยกเซลล์ดูภาพหน้าจอ:

3. ใน แยกเซลล์ ใหเลือก แยกเป็นคอลัมน์ ภายใต้ ชนิดภาพเขียน จากนั้นตรวจสอบ ข้อความและตัวเลข จาก แยกตาม ส่วนดูภาพหน้าจอ:

doc แยกข้อความหมายเลข 13

4. จากนั้นคลิก Ok และกล่องพร้อมต์จะปรากฏขึ้นเพื่อเตือนให้คุณเลือกเซลล์ที่จะแสดงผลลัพธ์ดูภาพหน้าจอ:

doc แยกข้อความหมายเลข 14

5. คลิก OK ปุ่มและสตริงข้อความในส่วนที่เลือกได้ถูกแยกออกเป็นสองคอลัมน์ตามภาพหน้าจอต่อไปนี้:

doc แยกข้อความหมายเลข 15

คลิกดาวน์โหลด Kutools สำหรับ Excel และทดลองใช้ฟรีทันที!


แยกข้อความและตัวเลขด้วย Kutools for Excel

Kutools สำหรับ Excel: ด้วย Add-in ของ Excel ที่มีประโยชน์มากกว่า 300 รายการให้ทดลองใช้ฟรีโดยไม่มีข้อ จำกัด ใน 30 วัน ดาวน์โหลดและทดลองใช้ฟรีทันที!

บทความที่เกี่ยวข้อง:

วิธีแบ่งคำเป็นตัวอักษรแยกกันใน Excel

วิธีการแบ่งเซลล์ออกเป็นหลายคอลัมน์หรือหลายแถวโดยการกลับรถ?


 

  • Kutools for Excel: 300+ คุณสมบัติที่มีประโยชน์สำหรับ Excel ทดลองใช้ฟรี

    การรวบรวมเครื่องมือที่ชาญฉลาดและเป็นมืออาชีพมากกว่า 300 รายการสำหรับ Excel ช่วยลดความซับซ้อนของงานที่ซับซ้อนประเภทต่างๆลงในการคลิกเพียงไม่กี่ครั้งในงานประจำวันของคุณซึ่งสามารถประหยัดเวลาและเพิ่มผลผลิตได้มาก

    • รวมแผ่นงานหรือสมุดงานหลายแผ่นเป็นสมุดงานหรือแผ่นงานเดียว
    • นับและรวมเซลล์ตามพื้นหลังแบบอักษรหรือสีการจัดรูปแบบตามเงื่อนไข
    • ส่งอีเมลฉบับเดียวกันไปยังรายชื่อผู้รับทีละรายการพร้อมไฟล์แนบที่แตกต่างกัน
    • ทางเลือกของผู้ใช้ Excel มากกว่า 110,000 ราย ทดลองใช้ฟรี 30 วันเต็มไม่ต้องใช้บัตรเครดิต!
    • รับประกันคืนเงินภายใน 60 วัน!
    คูทูล 1 kutools บวก
  • รวมแผ่นงานหรือสมุดงานหลายแผ่นเป็นแผ่นงานหรือสมุดงานเดียว

    ในการทำงานประจำวันของคุณการรวมแผ่นงานหรือสมุดงานหลายแผ่นไว้ในแผ่นงานหรือสมุดงานเดียวอาจเป็นงานใหญ่สำหรับคุณที่จะรวมเข้าด้วยกันทีละแผ่น แต่ด้วย Kutools สำหรับ Excel's รวมกัน คุณสามารถแก้ปัญหานี้ได้ด้วยการคลิกเพียงไม่กี่ครั้ง

    รวมแผ่น
  • นับและรวมเซลล์ตามพื้นหลังแบบอักษรหรือสีการจัดรูปแบบตามเงื่อนไข

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

    นับตามสี
  • ส่งอีเมลฉบับเดียวกันไปยังรายชื่อผู้รับทีละรายการพร้อมไฟล์แนบที่แตกต่างกัน

    คุณสามารถใช้แอปพลิเคชัน Word เพื่อส่งอีเมลส่วนตัวไปยังบุคคลหลาย ๆ คนโดยไม่ต้องมีไฟล์แนบ แต่ด้วย Kutools สำหรับ Excel's ส่งอีเมลล์ คุณลักษณะข้อ จำกัด นี้สามารถแก้ไขได้ ในขณะเดียวกันคุณสามารถ CC หรือ Bcc ข้อความถึงบุคคลใดบุคคลหนึ่งได้เช่นกัน

    ส่งอีเมลล์

 

Comments (34)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Tenho uma coluna com vários endereços e os números de endereços. Conforme tabela abaixo. Como separo em uma coluna apenas os nomes e outra coluna apenas os números?

endereço
Avenida Angélica 1235
Rua José Maria Lisboa 456
Rua Celso de Azevedo Marques 307
Rua Rio Duas Barras 953
This comment was minimized by the moderator on the site
Hello, Mateus,
To extract the address number only from the address, the folloiwng formula may help you:
=SUMPRODUCT(MID(0&B3, LARGE(INDEX(ISNUMBER(--MID(B3, ROW(INDIRECT("1:"&LEN(B3))), 1)) * ROW(INDIRECT("1:"&LEN(B3))), 0), ROW(INDIRECT("1:"&LEN(B3))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B3)))/10)

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Bom dia.
Consegui extrair o número, muito obrigado!
Mas como eu consigo extrair agora apenas o endereço?
This comment was minimized by the moderator on the site
Hello, Mateus,
If you want to extract both address and address number, you can apply the Method 3 in this article:

https://www.extendoffice.com/documents/excel/2701-excel-separate-text-and-numbers.html#a3

After copying and pasting the code, please apply the below formulas:
Extract address: =SplitText(B2,FALSE)

Extract address number: =SplitText(B2,TRUE)
Please try, hope it can help you! If you have any other problem, please comment here.
This comment was minimized by the moderator on the site
Agile (11/20/2017 12:00:00 AM)How can I separate this cell into just "Agile" and "(11/20/2017 12:00:00 AM)"
This comment was minimized by the moderator on the site
Hello, Milner,To get the name text, please use this formula: =TRIM(LEFT(A1, FIND("(", A1)-1))To get the date time, please apply this formula: =MID(A1,SEARCH("(",A1),SEARCH(")",A1)-SEARCH("(",A1)+1)Please try the formulas, hope them can help you!
This comment was minimized by the moderator on the site
0002786961 TRAK CDFA #: 0008787942 2722 2723 4536841 N/A 4345784 001018809~00077480

Above is an example of data line I need to split these into 3 types: First: starts with 2 and is of 4 digit (2722 in above example) Second: starts with 2 and is of 7 digit third; starts with 4 is of 7 digit.
I tried separating everything into different columns and then putting if and conditions that I mentioned above but the problem is not everything is getting separated and splitting everything is not efficient enough. I am not able to figure out a vba code for something that satisfies all the conditions and works too.
Can anyone help me out?
This comment was minimized by the moderator on the site
Hello, menze
Do you need to split this three parts 2722 2723 4536841 from the long data? If so, you just can use the Text to Column feature in Excel to split the content into multiple cells by space, then delete the extra data and only keep the data you need.
If not, please upload your Excel file or screenshot here for a reference.
Thank you!
This comment was minimized by the moderator on the site
I have a requirement code that I need to separate out from the text, looks like this 3.1.1.2. Line-of-sight Range Requirements (T=O) The vehicle and units shall be capable of line-of-sight connectivity to all nodes or within a 10-mile radius, whichever is greater. In one column I need the code and the other I need the text. Can you help?
This comment was minimized by the moderator on the site
I have address like 12,anna street,98413256789 i want to seperate address and contact number(mobile or landline) two columns. Is it possible.Pls help to solve
This comment was minimized by the moderator on the site
Hi, Mohan,To seperate address and contact number, please apply the below formulas:Address: =LEFT(A1,LEN(A1)-12)Mobile number: =RIGHT(A1,11)
Please try, hope it can help you!
This comment was minimized by the moderator on the site
Please, help me with the VBA code required to take out "INC000010542805" out of "User KSmith Audit ID INC000010542805 Comment None Control Data".
I have 1,000 rows of this type of data with different number of characters but always with that "INC" string.
This comment was minimized by the moderator on the site
Hello, Kenny,
May be the below formula can solve your problem, please try:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("INC",A1),LEN(A1))," ",REPT(" ",100)),100))
This comment was minimized by the moderator on the site
skyyang thanks very much, it works. I really appreciate your help
This comment was minimized by the moderator on the site
how to separate number and letters (1122AB). I tried the upper formula but its not working with me. anybody help me in this regard. Thanks in advance
This comment was minimized by the moderator on the site
Hello, Naeem,
The above formula only works if the text is before the numbers, your numbers before the text, so i recommend the second and third method for you!
Please try, hope it can help you!
This comment was minimized by the moderator on the site
kereeeennn... berhasil....
This comment was minimized by the moderator on the site
Thanks worked for Alpha-numeric cell data [ =RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)]
This comment was minimized by the moderator on the site
Hello Can anyone help me? I want to remove this zero and braceket by an excel formula.
1 BR PE-0.50CT(H SI2)- -0.00( )-2 SP PE-0.50CT(H SI1)-1 RU PE-0.40CT(H-SI)-750GF-RG-RING-25-40-2.50GM


Gowtam
This comment was minimized by the moderator on the site
VERY USEFUL FORMULA I LIKE IT.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations