Lấy số trong chuỗi ký tự excel

Trong nội dung bài viết này, goldmarkcity-hanoi.com vẫn giải thích giải pháp tách các ô vào Excel bởi bí quyết. Quý khách hàng đang học giải pháp tách bóc văn bản dựa trên vệt phẩy, khoảng trắng hoặc ngẫu nhiên vết chia cách làm sao khác, với làm cầm cố như thế nào để phân chia chuỗi thành vnạp năng lượng phiên bản với số.

Bạn đang xem: Lấy số trong chuỗi ký tự excel

Làm gắng nào để chia vnạp năng lượng bạn dạng trong Excel bằng cách sử dụng công thức:

Để bóc chuỗi trong Excel xuất xắc tách chữ với số trong excel, các bạn hay sử dụng hàm LEFT, RIGHT hoặc MID kết hợp với FIND hoặc SEARCH. Lúc trước, một vài bí quyết hoàn toàn có thể tinh vi, nhưng mà thực tiễn xúc tích và ngắn gọn là tương đối dễ dàng, với những ví dụ tiếp sau đây đang cung ứng cho chính mình một số mai dong.

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


*

*

lúc phân loại những ô trong Excel, Việc chính là xác định vị trí của dấu phân cách trong chuỗi vnạp năng lượng bản. Tùy thuộc vào công việc của chúng ta, vấn đề đó rất có thể được thực hiện bằng phương pháp thực hiện hàm Search ko minh bạch chữ hoa chữ hay hoặc hàm Find gồm phân minh chữ hoa chữ hay. Một khi chúng ta bao gồm vị trí của lốt ngăn cách, thực hiện hàm RIGHT, LEFT hoặc MID để trích xuất phần tương ứng của chuỗi vnạp năng lượng phiên bản.

Để làm rõ hơn, hãy chu đáo ví dụ sau đây:

Giả sử các bạn gồm một danh sách các SKU của mẫu mã Loại-Màu-Kích thước, cùng bạn muốn phân chia tách bóc cột thành 3 cột riêng rẽ biệt:

*

Để trích xuất tên mục (tất cả những ký kết từ trước lốt nối đầu tiên), cnhát công thức sau vào B2, cùng tiếp đến xào luộc nó xuống cột:

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

Trong phương pháp này, hàm Search xác xác định trí của dấu nối thứ nhất (“-“) trong chuỗi cùng tác dụng LEFT đang tách tất cả những ký kết từ bỏ còn lại (chúng ta trừ 1 tự địa chỉ của vệt nối cũng chính vì bạn không thích bao gồm dấu nối).

*

Để trích xuất Màu sắc (toàn bộ các ký từ giữa những dấu gạch nối thứ hai cùng thiết bị 3), hãy nhập công thức sau vào C2, và sau đó coppy nó xuống các ô khác:

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

*

Nlỗi bạn cũng có thể biết, hàm MID bao gồm cú pháp sau:

MID (văn phiên bản, start_num, num_chars)

Nơi:

Văn uống phiên bản – vị trí nhằm trích xuất văn phiên bản từ bỏ.Start_num – địa điểm của kí trường đoản cú đầu tiên nhằm trích xuất.Num_chars – số ký kết trường đoản cú nhằm trích xuất.

Trong phương pháp trên, văn uống bạn dạng được trích ra từ ô A2, cùng 2 đối số khác được tính bằng cách sử dụng 4 hàm SEARCH khác:

Số bước đầu (start_num) là địa điểm của vệt nối đầu tiên +1:

SEARCH (“-“, A2) + 1

Số ký từ bỏ nhằm trích xuất (num_chars): sự khác hoàn toàn giữa vị trí của dấu nối thiết bị hai cùng lốt nối thứ nhất, trừ đi 1:

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

Để trích xuất kích thước (tất cả các ký kết từ sau vệt nối vật dụng 3), hãy nhập cách làm sau trong D2:

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

Trong phương pháp này, hàm LEN trả về tổng chiều nhiều năm của chuỗi, tự đó các bạn trừ đi vị trí của vệt nối sản phẩm nhì. Sự biệt lập là số ký kết từ bỏ sau vệt nối lắp thêm nhị cùng hàm RIGHT chiết xuất bọn chúng.

*
Trong một phương pháp giống như, chúng ta cũng có thể phân loại cột bởi vì ngẫu nhiên kí từ bỏ nào không giống. Tất cả bạn buộc phải làm là sửa chữa “-” bởi cam kết từ bỏ ngăn cách buộc phải, ví như dấu giải pháp (“”), vệt gạch chéo cánh (“/”), vệt hai chnóng (“;”), dấu chnóng phẩy (“;”) với vân vân.

Mẹo. Trong các công thức trên, +1 cùng -1 tương xứng cùng với số ký từ bỏ vào lốt phân làn. Trong ví dụ này, nó là một trong vết nối (1 cam kết tự). Nếu vệt phân cách của chúng ta bao hàm 2 ký từ bỏ, ví dụ: Dấu phẩy cùng khoảng tầm trắng, kế tiếp chỉ cung cấp vết phẩy (“,”) cho hàm SEARCH, cùng thực hiện +2 với -2 cầm cố bởi +1 với -1.

Làm cố nào để phân chia chuỗi bằng cách ngắt chiếc vào Excel:

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

*
Lấy cách làm trường đoản cú ví dụ trước với cố gắng lốt gạch men nối (“-“) bằng CHAR (10) trong đó 10 là mã ASCII mang đến loại cấp cho 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 color 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 nắm nào để phân loại vnạp năng lượng bạn dạng với số vào Excel:

Để bước đầu, không có giải pháp tổng quát mang đến toàn bộ những chuỗi chữ số. Công thức làm sao để thực hiện phụ thuộc vào vào mẫu chuỗi cụ thể. Dưới trên đây các bạn sẽ tìm thấy công thức mang đến 3 kịch bản hay chạm mặt duy nhất.

Xem thêm: Xuất Khẩu Dữ Liệu Từ Misa Ra Excel Ko Nhỉ?? Cách Xuất Dữ Liệu Từ Misa Ra Excel

lấy ví dụ 1. Chia chuỗi của nhiều loại ‘vnạp năng lượng bản + số’

Giả sử các bạn tất cả một cột những chuỗi cùng với vnạp năng lượng bản cùng số kết hợp, trong các số đó một trong những luôn luôn luôn luôn theo sau vnạp năng lượng bản. quý khách mong muốn phá đổ vỡ những chuỗi thuở đầu để văn bản cùng số mở ra trong những ô lẻ tẻ, nhỏng sau:

*

Để trích xuất những số, thực hiện cách làm mảng tiếp sau đây, được dứt bằng cách thừa 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 thuở đầu, cùng C2 là số trích xuất, nhỏng bộc lộ vào hình bên dưới đây:

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

Công thức nhằm trích xuất số (hàm RIGHT). Về cơ phiên bản, bí quyết tra cứu tìm những số hoàn toàn có thể từ 0 cho 9 trong chuỗi nguồn, tính số lượng và trả về nhiều cam kết tự trường đoản cú ký kết trường đoản cú cuối chuỗi thuở đầu.

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

trước hết, bạn áp dụng những hàm LEN và SUBSTITUTE nhằm đưa ra mốc giới hạn xuất hiện một trong những nào đó trong chuỗi nơi bắt đầu – sửa chữa số bởi một chuỗi trống rỗng (“”), và tiếp đến trừ đi chiều dài của chuỗi mà lại không tồn tại số đó tự tổng thể Chiều lâu năm của chuỗi thuở đầu. Bởi vị đó là một cách làm mảng, làm việc này được tiến hành trên từng số vào 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 nguồn.Cuối cùng, hàm RIGHT trả về những ký kết từ từ phía mặt buộc phải của chuỗi.

Công thức nhằm trích xuất văn uống bản (hàm LEFT). Bạn tính toán bao nhiêu ký từ vnạp năng lượng bản chuỗi cất bằng phương pháp trừ số chữ số triết xuất (C2) từ chiều dài của chuỗi nơi bắt đầu (A2). Sau đó, các bạn áp dụng hàm LEFT để trả về các cam kết trường đoản cú từ đầu chuỗi.

Một phương án không giống (cách làm không có mảng)

Giải pháp thay thế đang áp dụng bí quyết sau để xác định vị 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 dù bí quyết cũng chứa một hằng số mảng, đó là 1 trong bí quyết bình thường được chấm dứt Theo phong cách thông thường bằng phương pháp nhận phím Enter.

Lúc địa điểm của số thứ nhất được tìm kiếm thấy, bạn có thể bóc tách văn uống bản với số bằng phương pháp sử dụng những công thức LEFT với RIGHT rất đơn giản dễ dàng (đừng quên một trong những luôn luôn xuất hiện thêm sau văn uố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 vừa lòng A2 là chuỗi ban sơ, cùng B2 là địa chỉ của số thứ nhất, nhỏng biểu lộ vào hình dưới đây:

*
Để loại trừ cột helper giữ lại địa điểm số ban đầu, bạn 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 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 những 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 toán vị trí của số thiết bị nhất

quý khách hàng hỗ trợ hằng số mảng 0,1,2,3,4,5,6,7,8,9 vào đối số find_text của hàm SEARCH, tạo cho nó tìm từng số vào hằng số mảng bên phía trong bản nơi bắt đầu, cùng trả lại địa chỉ của bọn chúng. Bởi bởi hằng số mảng cất 10 chữ số, mảng tác dụng cũng cất 10 mục.

Hàm MIN mang mảng công dụng với trả về giá trị bé dại duy nhất, tương ứng cùng với địa chỉ của số thứ nhất vào chuỗi ban đầu.

Dường như, Cửa Hàng chúng tôi thực hiện một cấu tạo đặc biệt quan trọng (A2 & “0123456789”) để ghxay mỗi số hoàn toàn có thể với chuỗi thuở đầu. Cách này triển khai phương châm của IFERROR và chất nhận được công ty chúng tôi tránh lỗi lúc một số nhất mực trong hằng số mảng ko được tìm kiếm thấy trong chuỗi mối cung cấp. Trong trường hợp này, phương pháp trả về địa chỉ “mang mạo” bằng chuỗi chiều nhiều năm từ 1 cam kết từ trsinh hoạt lên. Như vậy cho phép hàm LEFT trích xuất văn uống bản cùng hàm RIGHT trả về một chuỗi rỗng nếu chuỗi nơi bắt đầu không cất bất kỳ số làm sao, nhỏng vào cái 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à giải pháp công ty chúng tôi có:

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

*
Và bởi vì 7 là quý giá nhỏ dại duy nhất trong mảng công dụng, vị hàm MIN trả về, với họ nhận ra địa điểm của số thứ nhất (0) vào chuỗi văn phiên bản ban đầu.

lấy ví dụ 2. Chia chuỗi của nhiều loại ‘số + vnạp năng lượng bản’

Nếu nhiều người đang bóc các ô vị trí vnạp năng lượng bạn dạng lộ diện sau một trong những, 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 cách dấ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, ngoài các bạn thực hiện hàm LEFT cố gắng vì chưng RIGHT, bởi vì trong ngôi trường đúng theo này số luôn luôn xuất hiện sống phía bên trái của chuỗi. Một Khi bạn đã sở hữu các số lượng, trích xuất văn phiên bản bằng cách trừ số chữ số từ tổng chiều nhiều năm của chuỗi gốc:

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

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

*

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

Nếu quá trình của chúng ta yên cầu nên trích xuất tất cả các số xuất phát điểm từ một chuỗi vào định dạng ‘number-text-number’, bạn có thể sử dụng công thức dưới đây được gợi ý do một giữa 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ẾP (“1:” và LEN (A2) (1: “& LEN (A2)))) + 1, 1) * 10 ^ ROW (INDIRECT (” 1: “và LEN (A2))) / 10)

Trường đúng theo A2 là chuỗi vnạp năng lượng bản ban sơ.

*