Hàm tách chữ và số trong excel

     

Trong nội dung bài viết này, myga.vn vẫn lý giải giải pháp tách bóc các ô vào Excel bởi phương pháp. Quý khách hàng sẽ học tập cách tách vnạp năng lượng bản dựa vào vết phẩy, khoảng chừng trắng hoặc bất kỳ vết phân cách như thế nào khác, cùng có tác dụng ráng như thế nào nhằm phân chia chuỗi thành văn bản với số.

Bạn đang xem: Hàm tách chữ và số trong excel


Làm cầm như thế nào nhằm chia văn bản trong Excel bằng cách thực hiện công thức:

Để bóc tách chuỗi vào Excel tuyệt tách chữ và số trong excel, bạn hay sử dụng hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. Lúc trước, một vài phương pháp hoàn toàn có thể phức hợp, dẫu vậy thực tế xúc tích và ngắn gọn là hơi dễ dàng, cùng những ví dụ dưới đây vẫn hỗ trợ cho bạn một vài đầu mối.

Tách chuỗi bởi vệt phẩy, vệt hai chnóng, lốt gạch ốp chéo cánh, vết gạch ngang hoặc có thể dấu phân làn khác

khi phân chia những ô vào Excel, câu hỏi đó là xác xác định trí của lốt phân cách vào chuỗi văn bản. Tùy thuộc vào công việc của bạn, vấn đề đó hoàn toàn có thể được thực hiện bằng phương pháp áp dụng hàm Search không tách biệt chữ hoa chữ thường hoặc hàm Find tất cả phân minh chữ hoa chữ thường xuyên. Một khi chúng ta tất cả vị trí của dấu chia cách, sử dụng hàm RIGHT, LEFT hoặc MID để trích xuất phần tương xứng của chuỗi văn uống bản.

Để làm rõ hơn, hãy chú ý ví dụ sau đây:

Giả sử chúng ta gồm một list các SKU của mẫu Loại-Màu-Kích thước, với bạn có nhu cầu chia bóc tách cột thành 3 cột riêng rẽ biệt:

*


*

*

Để trích xuất thương hiệu mục (toàn bộ những ký tự trước vệt nối đầu tiên), cnhát phương pháp sau trong B2, cùng kế tiếp xào nấu nó xuống cột:

= LEFT (A2, search (“-“, A2,1) -1)

Trong bí quyết này, hàm Search xác xác định trí của vệt nối đầu tiên (“-“) trong chuỗi cùng công dụng LEFT sẽ chiết toàn bộ các ký từ sót lại (các bạn trừ 1 từ địa chỉ của vết nối bởi vì chúng ta không muốn bao gồm vệt nối).

*

Để trích xuất màu sắc (tất cả các cam kết từ bỏ giữa các vết gạch men nối thứ 2 và máy 3), hãy nhập bí quyết sau trong C2, cùng kế tiếp coppy nó xuống các ô khác:

=MID(A2, SEARCH(“-“, A2)+1, SEARCH(“-“, A2, SEARCH(“-“,A2)+1)-SEARCH(“-“,A2)-1

*

Nhỏng bạn có thể biết, hàm MID có cú pháp sau:

MID (văn uống bản, start_num, num_chars)

Nơi:

Văn uống bạn dạng – nơi nhằm trích xuất vnạp năng lượng bạn dạng tự.Start_num – vị trí của kí từ trước tiên nhằm trích xuất.Num_chars – số ký trường đoản cú để trích xuất.

Trong bí quyết trên, văn bản được trích ra trường đoản cú ô A2, và 2 đối số không giống được tính bằng phương pháp sử dụng 4 hàm SEARCH khác:

Số bắt đầu (start_num) là vị trí của vết nối đầu tiên +1:

SEARCH (“-“, A2) + 1

Số cam kết tự để trích xuất (num_chars): sự khác biệt giữa địa chỉ của vệt nối vật dụng hai và lốt nối thứ nhất, trừ đi 1:

SEARCH (“-“, A2, SEARCH (“-“, A2) +1) – SEARCH (“-“, A2) -1

Để trích xuất kích cỡ (toàn bộ những ký từ bỏ sau vết nối sản phẩm 3), hãy nhập cách làm sau vào D2:

= RIGHT (A2, LEN (A2) – SEARCH (“-“, A2, SEARCH (“-“, A2) + 1))

Trong bí quyết này, hàm LEN trả về tổng chiều dài của chuỗi, tự kia bạn trừ đi địa chỉ của vệt nối sản phẩm nhì. Sự khác biệt là số ký kết từ bỏ sau vệt nối thiết bị nhị với hàm RIGHT chiết xuất bọn chúng.

*
Trong một bí quyết tương tự như, chúng ta cũng có thể phân chia cột vì chưng ngẫu nhiên kí từ bỏ làm sao khác. Tất cả chúng ta đề nghị có tác dụng là thay thế sửa chữa “-” bởi ký trường đoản cú phân cách đề nghị, ví dụ như vệt giải pháp (“”), vết gạch men chéo cánh (“/”), vệt nhị chnóng (“;”), lốt chnóng phẩy (“;”) với vân vân.

Mẹo. Trong những phương pháp trên, +1 và -1 tương xứng với số ký kết tự vào lốt chia cách. Trong ví dụ này, nó là 1 trong những lốt nối (1 ký kết tự). Nếu lốt chia cách của khách hàng bao gồm 2 cam kết tự, ví dụ: Dấu phẩy cùng khoảng trắng, tiếp nối chỉ cung ứng vệt phẩy (“,”) đến hàm SEARCH, cùng thực hiện +2 với -2 nuốm bởi vì +1 với -1.

Làm cầm làm sao nhằm phân loại chuỗi bằng phương pháp ngắt mẫu trong Excel:

Để phân chia văn uống bản bằng khoảng White, hãy sử dụng những bí quyết giống như như công thức được minc họa vào ví dụ trước. Sự biệt lập nhất là bạn phải chức năng CHAR để cung ứng đến cam kết từ ngắt chiếc vày chúng ta quan yếu gõ thẳng vào cách làm. Giả sử, các ô nhưng mà bạn có nhu cầu chia nhỏ trông tựa như nhỏng sau:

*
Lấy bí quyết trường đoản cú ví dụ trước với nỗ lực vết gạch ốp nối (“-“) bởi CHAR (10) trong các số ấy 10 là mã ASCII mang đến loại cung cấp dữ liệu.

Để trích xuất thương hiệu khía cạnh hàng:

=LEFT(A2, SEARCH(CHAR(10),A2,1)-1)

Để trích xuất màu sắc sắc:

=MID(A2, SEARCH(CHAR(10),A2) + 1, SEARCH(CHAR(10),A2,SEARCH(CHAR(10),A2)+1) – SEARCH(CHAR(10),A2) – 1)

Để trích xuất kích thước:

=RIGHT(A2,LEN(A2) – SEARCH(CHAR(10), A2, SEARCH(CHAR(10), A2) + 1))

Và đấy là kết quả:

*

Làm ráng nào để phân chia vnạp năng lượng phiên bản cùng số vào Excel:

Để ban đầu, không có chiến thuật tổng thể mang lại tất cả các chuỗi chữ số. Công thức như thế nào nhằm sử dụng phụ thuộc vào vào mẫu chuỗi rõ ràng. Dưới trên đây bạn sẽ tìm thấy cách làm mang lại 3 kịch phiên bản thường xuyên gặp tốt nhất.

Xem thêm: Top 6 Bài Cảm Nhận Về Huấn Cao Trong Chữ Người Tử Tù Của Nguyễn Tuân

Ví dụ 1. Chia chuỗi của nhiều loại ‘văn bản + số’

Giả sử các bạn gồm một cột những chuỗi với văn phiên bản với số phối kết hợp, trong các số ấy một vài luôn luôn theo sau vnạp năng lượng bản. quý khách hy vọng phá vỡ vạc những chuỗi ban sơ nhằm văn bản với số mở ra trong số ô hiếm hoi, nhỏng sau:

*

Để trích xuất các số, thực hiện công thức mảng tiếp sau đây, được dứt bằng phương pháp nhận Ctrl + Shift + Enter:

= RIGHT (A2, SUM (LEN (A2) – LEN (SUBSTITUTE (A2, “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7” , “8”, “9”}, “”))))

Để trích xuất văn bạn dạng, sử dụng:

= LEFT (A2, LEN (A2) -LEN (C2))

Trường hợp A2 là chuỗi ban đầu, và C2 là số trích xuất, nhỏng biểu hiện vào hình dưới đây:

*
Công thức chuyển động như thế nào:

Công thức nhằm trích xuất số (hàm RIGHT). Về cơ bạn dạng, phương pháp tìm kiếm tìm phần lớn số hoàn toàn có thể trường đoản cú 0 mang đến 9 vào chuỗi mối cung cấp, tính con số và trả về các ký kết từ bỏ trường đoản cú cam kết từ cuối chuỗi ban đầu.

Và đấy là bí quyết chi tiết phân rã:

Trước hết, bạn sử dụng các hàm LEN và SUBSTITUTE để đưa ra chu kỳ mở ra một vài làm sao đó trong chuỗi nơi bắt đầu – thay thế số bằng một chuỗi trống rỗng (“”), với kế tiếp trừ đi chiều lâu năm của chuỗi mà không tồn tại số kia trường đoản cú tổng cộng Chiều dài của chuỗi ban sơ. Bởi vày kia là 1 công thức mảng, làm việc này được tiến hành bên trên từng số trong hằng mảng:

LEN (A2) -LEN (SUBSTITUTE (A2, “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9 “},” “)

Tiếp theo, hàm SUM thêm toàn bộ các lần lộ diện của toàn bộ những chữ số vào chuỗi mối cung cấp.Cuối thuộc, hàm RIGHT trả về những ký kết từ trường đoản cú phía bên đề xuất của chuỗi.

Công thức để trích xuất văn uống bản (hàm LEFT). quý khách hàng tính toán thù bao nhiêu cam kết từ văn uống bản chuỗi chứa bằng cách trừ số chữ số chiết xuất (C2) trường đoản cú chiều nhiều năm của chuỗi nơi bắt đầu (A2). Sau đó, bạn thực hiện hàm LEFT để trả về các ký từ bỏ từ đầu chuỗi.

Một phương án khác (phương pháp không có mảng)

Giải pháp sửa chữa sẽ áp dụng phương pháp sau để xác định vị trí của số đầu tiên vào chuỗi: = MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 & “0123456789”))

Mặc mặc dù công thức cũng đựng một hằng số mảng, đó là 1 trong công thức bình thường được dứt Theo phong cách thường thì bằng cách nhận phím Enter.

khi vị trí của số trước tiên được tìm kiếm thấy, bạn có thể bóc tách văn bạn dạng cùng số bằng cách thực hiện các phương pháp LEFT với RIGHT hết sức dễ dàng và đơn giản (đừng quên một số luôn xuất hiện thêm sau văn uống bản):

Để trích xuất văn uống bản:

= LEFT (A2, B2-1)

Để trích xuất số:

=RIGHT(B2, LEN(A1)-B2+1)

Trường hòa hợp A2 là chuỗi lúc đầu, và B2 là địa chỉ của số trước tiên, như diễn đạt trong hình bên dưới đây:

*
Để vứt bỏ cột helper giữ địa chỉ số bước đầu, chúng ta cũng có thể nhúng hàm MIN vào những hàm LEFT và RIGHT:

Công thức trích xuất văn uống bản:

= LEFT (A2, MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 và “0123456789”)) – 1)

Công thức trích xuất những số:

= RIGHT (A2, LEN (A2) -MIN (SEARCH(0,1,2,3,4,5,6,7,8,9, A2 và “0123456789”)) + 1)

Công thức tính toán địa điểm của số đồ vật nhất

Quý Khách cung cấp hằng số mảng 0,1,2,3,4,5,6,7,8,9 trong đối số find_text của hàm SEARCH, tạo nên nó tìm từng số trong hằng số mảng bên phía trong bạn dạng gốc, và trả lại địa chỉ của chúng. Bởi vị hằng số mảng cất 10 chữ số, mảng tác dụng cũng đựng 10 mục.

Hàm MIN mang mảng công dụng với trả về cực hiếm nhỏ tuổi duy nhất, tương xứng cùng với địa chỉ của số đầu tiên vào chuỗi thuở đầu.

Ngoài ra, Cửa Hàng chúng tôi thực hiện một kết cấu đặc biệt (A2 và “0123456789”) nhằm ghxay từng số rất có thể cùng với chuỗi lúc đầu. Cách này tiến hành sứ mệnh của IFERROR cùng chất nhận được Cửa Hàng chúng tôi tách lỗi lúc một vài cố định trong hằng số mảng không được tìm kiếm thấy vào chuỗi nguồn. Trong ngôi trường hợp này, công thức trả về địa điểm “đưa mạo” bằng chuỗi chiều dài từ 1 ký từ trngơi nghỉ lên. Như vậy chất nhận được hàm LEFT trích xuất văn phiên bản và hàm RIGHT trả về một chuỗi trống rỗng nếu chuỗi nơi bắt đầu ko chứa ngẫu nhiên số làm sao, nlỗi vào mẫu 7 hình sinh sống trên.

Ví dụ: đối với chuỗi “Dress 05” vào A2, mảng tác dụng là 7,10,11,12,13,8,15,16,17,18. Và đây là bí quyết Shop chúng tôi có:

5 là ký từ bỏ vật dụng 8 trong chuỗi gốc, cùng 0 là ký từ bỏ lắp thêm 7, chính là lý do vì sao mục thứ nhất của mảng kết quả là “7”, và lắp thêm sáu là “8”.Không tất cả mục nào không giống của hằng số mảng được tra cứu thấy trong A2, cùng vì vậy 8 phần không giống của mảng kết quả đại diện cho những vị trí của các chữ số tương xứng trong chuỗi nối (A2 & “0123456789”).

*
Và cũng chính vì 7 là quý giá nhỏ tuổi tuyệt nhất trong mảng công dụng, vày hàm MIN trả về, cùng họ nhận được địa điểm của số trước tiên (0) vào chuỗi văn bản thuở đầu.

lấy ví dụ 2. Chia chuỗi của một số loại ‘số + văn uống bản’

Nếu nhiều người đang tách các ô nơi văn uống bản xuất hiện thêm sau một số trong những, bạn cũng có thể trích xuất các số với công thức mảng này (ngừng bằng phương pháp thừa nhận Ctrl + Shift + Enter):

= LEFT (A2, SUM (LEN (A2) -LEN (SUBSTITUTE (A2, “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7” , “8”, “9”, “”))))

Công thức tựa như nlỗi bí quyết mảng tự ví dụ trước, quanh đó chúng ta sử dụng hàm LEFT gắng vày RIGHT, bởi vì trong trường thích hợp này số luôn luôn xuất hiện sinh hoạt phía phía bên trái của chuỗi. Một Khi bạn đã có những số lượng, trích xuất văn uống bạn dạng bằng cách trừ số chữ số trường đoản cú tổng chiều lâu năm của chuỗi gốc:

= RIGHT(A2, LEN (A2) -LEN (B2))

Trong các bí quyết bên trên, A2 là chuỗi ban đầu và B2 là số trích xuất, nhỏng biểu hiện trong hình bên dưới đây:

*

lấy ví dụ 3. Trích xuất chỉ số trường đoản cú chuỗi số ‘số văn bản’

Nếu quá trình của người tiêu dùng yên cầu nên trích xuất tất cả các số từ một chuỗi trong format ‘number-text-number’, chúng ta có thể áp dụng cách làm tiếp sau đây được gợi ý bởi vì một trong số những chuyên gia của MrExcel:

= SUMPRODUCT (MID (0 & A2, LARGE (INDEX (ISNUMBER (- MID (A2, ROW (INDIRECT (“1:” và LEN (A2))), 1)) * ROW (TRỰC TIẾPhường (“1:” & LEN (A2) (1: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “& LEN (A2))) / 10)

Trường hòa hợp A2 là chuỗi văn uống bản ban đầu.

*