Hàm tách chữ trong excel

Trong bài viết này, trinhde.vn đã phân tích và lý giải giải pháp bóc tách những ô vào Excel bằng cách làm. Bạn vẫn học biện pháp tách bóc văn phiên bản dựa vào vết phẩy, khoảng tầm Trắng hoặc bất kỳ vết chia cách làm sao khác, cùng có tác dụng nỗ lực như thế nào nhằm phân loại chuỗi thành vnạp năng lượng phiên bản với số.

You watching: Hàm tách chữ trong excel


Làm núm làm sao để phân chia văn uống phiên bản trong Excel bằng cách áp dụng công thức:

Để tách bóc chuỗi vào Excel hay tách chữ và số vào excel, chúng ta thường xuyên áp dụng hàm LEFT, RIGHT hoặc MID kết phù hợp với FIND hoặc SEARCH. lúc đầu, một vài công thức có thể phức hợp, tuy vậy thực tế xúc tích là khá dễ dàng và đơn giản, và các ví dụ tiếp sau đây vẫn cung cấp cho chính mình một trong những đầu mối.

Tách chuỗi bằng lốt phẩy, vết hai chnóng, vết gạch men chéo, lốt gạch men ngang hoặc che dấu chia cách khác

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

Để làm rõ hơn, hãy xem xét ví dụ sau đây:

Giả sử bạn tất cả một list những SKU của chủng loại Loại-Màu-Kích thước, và bạn có nhu cầu phân chia bóc tách cột thành 3 cột riêng biệt biệt:

*


*

*

Để trích xuất tên mục (tất cả những ký tự trước lốt nối đầu tiên), ckém bí quyết sau trong B2, cùng kế tiếp sao chép nó xuống cột:

= LEFT (A2, tìm kiếm (“-“, A2,1) -1)

Trong cách làm này, hàm Search xác định vị trí của vết nối thứ nhất (“-“) vào chuỗi và tác dụng LEFT sẽ chiết tất cả các ký kết trường đoản cú sót lại (bạn trừ 1 từ bỏ địa điểm của vết nối cũng chính vì chúng ta không muốn gồm vết nối).

*

Để trích xuất màu sắc (toàn bộ các ký kết trường đoản cú giữa những vệt gạch nối thứ 2 với lắp thêm 3), hãy nhập công thức sau vào C2, và tiếp nối sao chép nó xuống các ô khác:

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

*

Nlỗi chúng ta có thể biết, hàm MID tất cả cú pháp sau:

MID (vnạp năng lượng bản, start_num, num_chars)

Nơi:

Vnạp năng lượng phiên bản – chỗ nhằm trích xuất văn uống phiên bản từ.Start_num – địa điểm của kí từ thứ nhất để trích xuất.Num_chars – số cam kết từ nhằm trích xuất.

Trong cách làm trên, văn bản được trích ra từ ô A2, với 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ố ban đầu (start_num) là địa chỉ của vệt nối thứ nhất +1:

SEARCH (“-“, A2) + 1

Số ký kết từ để trích xuất (num_chars): sự biệt lập thân địa điểm của vết nối lắp thêm hai và vết nối đầu tiên, trừ đi 1:

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

Để trích xuất kích cỡ (toàn bộ các ký từ sau vết nối đồ vật 3), hãy nhập công thức sau vào D2:

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

Trong công thức này, hàm LEN trả về tổng chiều lâu năm của chuỗi, từ bỏ đó chúng ta trừ đi địa chỉ của vệt nối sản phẩm hai. Sự khác biệt là số cam kết từ bỏ sau vệt nối sản phẩm nhị với hàm RIGHT triết xuất chúng.

*
Trong một giải pháp tương tự, bạn có thể phân chia cột do bất kỳ kí tự như thế nào không giống. Tất cả các bạn buộc phải làm là thay thế “-” bằng cam kết từ chia cách yêu cầu, ví dụ như vệt cách (“”), vết gạch chéo cánh (“/”), vết nhị chnóng (“;”), lốt chnóng phẩy (“;”) và vân vân.

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

Làm cầm làm sao để phân chia chuỗi bằng cách ngắt dòng vào Excel:

Để phân chia văn bản bằng khoảng chừng trắng, hãy áp dụng các bí quyết giống như như bí quyết được minc họa trong ví dụ trước. Sự biệt lập tuyệt nhất là bạn cần tác dụng CHAR nhằm cung ứng đến ký từ ngắt cái bởi vì các bạn không thể gõ thẳng vào phương pháp. Giả sử, các ô nhưng bạn muốn chia nhỏ dại trông tương tự nlỗi sau:

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

Để trích xuất tên phương diện hàng:

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

Để trích xuất màu 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 cụ làm sao nhằm phân chia vnạp năng lượng phiên bản và số trong Excel:

Để bắt đầu, không có giải pháp tổng thể mang đến toàn bộ những chuỗi chữ số. Công thức làm sao nhằm sử dụng nhờ vào vào chủng loại chuỗi cụ thể. Dưới đây các bạn sẽ tra cứu thấy phương pháp đến 3 kịch bạn dạng thường gặp mặt nhất.

See more: Simcity Deluxe Edition - Free Download Game Simcity 5 Full Version + Crack

lấy ví dụ như 1. Chia chuỗi của các loại ‘văn bạn dạng + số’

Giả sử các bạn có một cột những chuỗi với văn bản với số phối hợp, trong những số đó một số trong những luôn luôn luôn theo sau văn bản. Quý khách hàng muốn phá tan vỡ những chuỗi ban đầu để văn phiên bản cùng số mở ra trong những ô đơn nhất, nlỗi sau:

*

Để trích xuất những số, áp dụng công thức mảng sau đây, được xong xuôi 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 phiên bản, sử dụng:

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

Trường thích hợp A2 là chuỗi ban đầu, cùng C2 là số trích xuất, nlỗi diễn tả vào hình bên dưới đây:

*
Công thức hoạt động như thế nào:

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

Và đây là công thức chi tiết phân rã:

Đầu tiên, bạn sử dụng các hàm LEN cùng SUBSTITUTE để tìm ra tần số lộ diện một vài nào kia vào chuỗi cội – sửa chữa thay thế số bởi một chuỗi rỗng (“”), và tiếp nối trừ đi chiều lâu năm của chuỗi nhưng không tồn tại số đó từ bỏ tổng số Chiều dài của chuỗi ban đầu. Bởi bởi kia là 1 cách làm mảng, thao tác làm việc này được triển khai 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ộ những lần xuất hiện thêm của tất cả các 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 mặt bắt buộc của chuỗi.

Công thức nhằm trích xuất văn uống bạn dạng (hàm LEFT). Quý khách hàng tính toán thù từng nào cam kết từ bỏ vnạp năng lượng bạn dạng chuỗi cất bằng cách trừ số chữ số chiết xuất (C2) từ chiều lâu năm của chuỗi gốc (A2). Sau đó, chúng ta áp dụng hàm LEFT nhằm trả về các ký kết tự từ trên đầu chuỗi.

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

Giải pháp sửa chữa thay thế sẽ áp dụng công thức sau nhằm xác xác định trí của số thứ nhất trong chuỗi: = MIN (SEARCH (0,1,2,3,4,5,6,7,8,9, A2 & “0123456789”))

Mặc cho dù công thức cũng cất một hằng số mảng, kia là 1 phương pháp bình thường được ngừng theo cách thường thì bằng cách nhấn phím Enter.

lúc địa chỉ của số trước tiên được kiếm tìm thấy, chúng ta có thể bóc tách văn uống bạn dạng và số bằng cách sử dụng các cách làm LEFT với RIGHT hết sức đơn giản dễ dàng (hãy nhớ là một số luôn luôn xuất hiện sau vnạp năng lượng bản):

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

= LEFT (A2, B2-1)

Để trích xuất số:

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

Trường thích hợp A2 là chuỗi ban sơ, cùng B2 là vị trí của số thứ nhất, nlỗi diễn đạt trong hình bên dưới đây:

*
Để loại bỏ cột helper duy trì địa điểm số ban đầu, chúng ta cũng có thể nhúng hàm MIN vào các hàm LEFT và RIGHT:

Công thức trích xuất vnạp năng lượng bản:

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

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

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

Công thức tính tân oán địa điểm của số sản phẩm 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 kiếm từng số trong hằng số mảng bên trong phiên bản cội, cùng trả lại địa chỉ của chúng. Bởi vì hằng số mảng chứa 10 chữ số, mảng tác dụng cũng đựng 10 mục.

Hàm MIN đem mảng hiệu quả cùng trả về cực hiếm nhỏ độc nhất vô nhị, tương xứng cùng với địa điểm của số trước tiên vào chuỗi thuở đầu.

Dường như, Shop chúng tôi sử dụng một cấu tạo quan trọng (A2 và “0123456789”) nhằm ghxay từng số có thể cùng với chuỗi lúc đầu. Cách này tiến hành vai trò của IFERROR với cho phép công ty chúng tôi tránh lỗi Khi một số trong những nhất quyết vào hằng số mảng không được kiếm tìm thấy trong chuỗi nguồn. Trong ngôi trường đúng theo này, công thức trả về địa điểm “đưa mạo” bằng chuỗi chiều lâu năm từ là 1 ký từ trsống lên. Như vậy chất nhận được hàm LEFT trích xuất vnạp năng lượng bản với hàm RIGHT trả về một chuỗi rỗng giả dụ chuỗi cội không cất ngẫu nhiên số làm sao, nhỏng trong cái 7 hình ở trên.

Ví dụ: so với chuỗi “Dress 05” vào A2, mảng hiệu quả là 7,10,11,12,13,8,15,16,17,18. Và đấy là cách Cửa Hàng chúng tôi có:

5 là ký kết trường đoản cú sản phẩm công nghệ 8 trong chuỗi nơi bắt đầu, với 0 là ký kết trường đoản cú thiết bị 7, chính là nguyên nhân vì sao mục thứ nhất của mảng công dụng là “7”, cùng sản phẩm sáu là “8”.Không có mục như thế nào không giống của hằng số mảng được kiếm tìm thấy vào A2, với do đó 8 phần không giống của mảng kết quả thay mặt đại diện cho những địa điểm của các chữ số tương ứng vào chuỗi nối (A2 & “0123456789”).

*
Và bởi vì 7 là quý giá nhỏ tuổi nhất trong mảng kết quả, bởi hàm MIN trả về, và chúng ta nhận được địa chỉ của số trước tiên (0) vào chuỗi văn uống bạn dạng lúc đầu.

See more: Con Gái Thích Quà Gì Nhất Trong Ngày Sinh Nhật? 53 Món Quà Sinh Nhật Cho Bạn Gái Nữ Ý Nghĩa Nhất

ví dụ như 2. Chia chuỗi của các loại ‘số + văn bản’

Nếu ai đang bóc những ô khu vực văn phiên bản lộ diện sau một vài, bạn có thể trích xuất các số cùng với phương pháp mảng này (xong xuôi bằng phương pháp 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ương tự như nlỗi công thức mảng từ bỏ ví dụ trước, ngoài các bạn áp dụng hàm LEFT cầm vị RIGHT, bởi vì vào ngôi trường đúng theo này số luôn mở ra ngơi nghỉ phía phía trái của chuỗi. Một lúc bạn đã sở hữu các con số, trích xuất văn uống phiên bản bằng cách trừ số chữ số trường đoản cú tổng chiều dài 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ư mô tả vào hình dưới đây:

*

Ví dụ 3. Trích xuất chỉ số từ bỏ chuỗi số ‘số văn uống bản’

Nếu các bước của chúng ta đòi hỏi cần trích xuất toàn bộ các số xuất phát từ một chuỗi vào format ‘number-text-number’, bạn có thể áp dụng công thức tiếp sau đây được lưu ý vày một trong những Chuyên Viên của MrExcel:

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

Trường đúng theo A2 là chuỗi văn uống bạn dạng ban đầu.

*