Trong bài viết này, mình sẽ hướng dẫn cho bạn 4 Mẹo Tách Chữ Và Số Ra Từ Chuỗi Trong Excel Có Thể Bạn Chưa Biết thường xuyên gặp hằng ngày trong đời sống. Hãy cùng mình tìm hiểu ngay bên dưới nhé!


Ví dụ: Mình có bảng dữ liệu như bên dưới:

Yêu cầu: Dựa vào số liệu trong bảng số liệu trên, bạn sẽ tách những chuỗi trong cột Emloyee Id ra thành 2 phần: tách ra mã bộ phận của nhân viên vào cột Emp Department và tách ra số nhân viên ra vào trong cột Emloyee No.

Bạn đang xem: Hàm chỉ lấy số trong excel

Để làm được việc này, mình sẽ qua 4 mẹo tách dữ liệu như sau:

Mẹo 1: Tách chữ và số ra bằng cách sử dụng Flash Fill

Kể từ phiên bản 2013 trở đi, Excel có thêm một công cụ mới hết sức tiện ích, đó là Flash Fill. Đây là một tính năng giúp người dùng điền các giá trị một cách tự động, giúp tiết kiệm thời gian và hạn chế phải sử dụng các hàm không cần thiết.

Để sử dụng công cụ này, bạn sẽ thực hiện các bước sau:

Bước 1: Trong cột Emp Department, tại ô đầu tiên, bạn gõ mã bộ phận của nhân viên đầu tiên vào.

Tương tự, ở cột Emloyee No, bạn cũng gõ số lượng nhân viên dựa vào ô đầu tiên trong cột Emloyee ID.

Bước 2: Lần lượt tại ô hiển thị kết quả đầu tiên ở cột Emp Department, bạn nhấn phím Ctrl + E để hiển thị kết quả phía sau.

Tương tự, tại ô đầu tiên trong cột Emloyee No, bạn nhấn phím Ctrl + E để hiển thị kết quả phía sau.

Mẹo 2: Tách chữ và số ra bằng cách sử dụng công thức

Để sử dụng cách này, tại ô đầu tiên trong cột Emp Department, bạn gõ công thức sau: =LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))-1)

Giải thích: Find: Dùng hàm này để tìm ra vị trí của các con số lần lượt từ 0 -> 9. A2: Ô chứa ID đầu tiên muốn tách mã bộ phận nhân viên. IFERROR: Sử dụng hàm này để phòng trong trường hợp nếu tìm không thấy những con số hoặc bị lỗi thì sẽ hiển thị ra dấu khoảng trắng "". Min: Vì trong trường hợp dùng hàm FIND thì những vị trí nào nào chứa những con số thì đều hiển thị ra. Tuy nhiên, mình chỉ muốn lấy vị trí đầu tiên xuất hiện những con số nên dùng hàm MIN để lấy ra số vị trí nhỏ nhất. LEFT: Dùng hàm này để tách ra mã bộ phận nhân viên từ ô ID. Cuối cùng trừ 1 để chỉ tính số lượng mã bộ phân nhân viên từ ô ID. Số lượng đó phải trước những con số như trong dữ liệu.

Tương tự, mình cũng tách ra số lượng nhân viên vào cột Emloyee No, bạn cũng gõ: =MID(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),"")),100)

Giải thích: Find: Dùng hàm này để tìm ra vị trí của các con số lần lượt từ 0 -> 9. A2: Ô chứa ID đầu tiên muốn tách số lượng nhân viên. IFERROR: Sử dụng hàm này để phòng trong trường hợp nếu tìm không thấy những con số hoặc bị lỗi thì sẽ hiển thị ra dấu khoảng trắng "". Min: Vì trong trường hợp dùng hàm FIND thì những vị trí nào nào chứa những con số thì đều hiển thị ra. Tuy nhiên, mình chỉ muốn lấy vị trí đầu tiên xuất hiện những con số nên dùng hàm MIN để lấy ra số vị trí nhỏ nhất. MID: Dùng hàm này để tách ra số lượng nhân viên từ ô ID. 100: Dùng giá trị 100 để ước lượng tương đối số kí tự sau đó.

Mẹo 3: Tách chữ và số ra bằng cách sử dụng VBA

Để sử dụng phương pháp này, đầu tiên bạn phải truy cập vào giao diệnVBA bằng cách:

Tại trang tính chứa dữ liệu cần tách, bạn nhấn tổ hợp phím ALT + F11.

Hiện ra giao diện VBA. Tiếp theo, bạn sẽ tạo module để chứa code tách dữ liệu bằng cách vào thẻ Insert => chọn vào Module.

Hiển thị giao diện module, tại đây bạn gõ đoạn code sau:

Function Get
Number(Cell
Ref As String)Dim String
Length As Integer
String
Length = Len(Cell
Ref)For i = 1 To String
Length
If Is
Numeric(Mid(Cell
Ref, i, 1)) Then Result = Result & Mid(Cell
Ref, i, 1)Next i
Get
Number = Result
End Function

Function Get
Text(Cell
Ref As String)Dim String
Length As Integer
String
Length = Len(Cell
Ref)For i = 1 To String
Length
If Not (Is
Numeric(Mid(Cell
Ref, i, 1))) Then Result = Result & Mid(Cell
Ref, i, 1)Next i
Get
Text = Result
End Function

Tiếp theo, bạn nhấn phím Run trên thanh công cụ hoặc nhấn phím F5 để chạy đoạn code trên.

Sau khi chạy xong thì thoát giao diện VBA ra.

Ở cột Emp Department, ô đầu tiên bạn gõ: =Get
Text(A2)

Giải thích: Hàm Get
Text
có được do mình tạo hàm từ Code VBA.

Tương tự, để hiển thị cột Emloyee No, tại ô đầu tiên bạn gõ: =Get
Number(A2)

Giải thích: Hàm Get
Number
có được do mình tạo hàm từ Code VBA.

Mẹo 4: Tách chữ và số ra bằng cách sử dụng Power Query

Trước khi sử dụng mẹo này, thì bạn sẽ phải chuyển dữ liệu của bạn sang thành bảng Table bằng cách bôi đen vùng dữ liệu muốn tách, sau đó chọn vào thẻ Insert => Table và nhấn Ok để hoàn thành việc tạo bảng.

Sau khi một bảng được tạo, bây giờ tôi có thể sử dụng truy vấn nguồn để đưa tất cả dữ liệu này vào trình chỉnh sửa truy vấn nguồn. Sau đó mình sẽ tách văn bản và số trong trình chỉnh sửa truy vấn nguồn. Vì vậy, để làm điều đó, mình sẽ đi đến thẻ Data, tại đây bạn chọn vào From Table/Range.

Hiển thị ra giao diện Power Query, tại đây bạn sẽ bôi đen vùng dữ liệu cần tách bằng cách chọn vào tiêu đề.

Tiếp theo, vào thẻ Home => Split Column => By non Digit to Digit

Sau khi nhấn chọn xong, dữ liệu của bạn đã được tách

Hoàn thành xong, thì bạn sẽ tải dữ liệu về File Excel bằng cách vào thẻ Home => Close & Load to...

Trong bảng Import Data, bạn chọn vào Existing worksheet và chọn vào vị trí mà bạn muốn hiển thị kết quả.

Nhấn OK để hoàn thành.

Lưu ý: Ở mẹo này thú vị ở chỗ khi bạn sửa thông tin trong cột dữ liệu gốc thì bảng dữ liệu mới tách sẽ tự cập nhật bằng cách chọn ô bất kì trong vùng dữ liệu mới tắt, nhấn chuột phải và chọn vào Refresh.

Vậy là mình đã hướng dẫn xong cho bạn4 mẹo tách chữ và số ra từ chuỗi trong excel có thể bạn chưa biết. Hy vọng bài viết này sẽ giúp ích cho bạn. Nếu thấy hay đừng quên đánh giá bài viết bên dưới nhé! Trân trọng.

Excel là một trong những phần mềm của Microsoft, chúng giúp người dùng tính toán, thống kê,...Tuy nhiên, bạn đã biết cách sử dụng hàm VALUE chuỗi ký tự trong Excel vào đời sống, công việc chưa? Hãy cùng Sforum tìm hiểu về hàm VALUE qua bài viết hôm nay nhé!

*

Hàm VALUE là gì?

Hàm VALUE là hàm lấy số trong chuỗi ký tự của bảng tính Excel. Hiểu đơn giản, VALUE chính là hàm để đổi ký tự thành số và thường kết hợp cùng các hàm như MID, RIGHT, LEFT.

*
VALUE là hàm để chuyển đổi chuỗi văn bản thành số.

Công thức hàm VALUE trong Excel

Cú pháp: =VALUE(text)

Trong đó: Text (Văn bản) được đặt trong một tham chiếu hoặc ngoặc kép đến một ô chứa văn bản mà người dùng muốn chuyển đổi.

Lưu ý:

Text (văn bản) có thể là thời gian, ngày hoặc số hàng định nào, miễn sao Microsoft Excel công nhận là được. Trong trường hợp văn bản không nằm trong các định dạng, kết quả sẽ trả về giá trị lỗi #VALUE.

Thông thường, người dùng không phải dùng hàm VALUE vì Excel sẽ chuyển đổi tự động văn bản sang số. Hàm VALUE trong Excel được cung cấp nhằm tương thích với chương trình bản tính khác.

*
Cú pháp VALUE trong Excel

Hướng dẫn cách dùng hàm VALUE trong Excel?

Trong công việc, cuộc sống, chúng ta sẽ gặp nhiều trường hợp phải chuyển đổi văn bản thành số để phục vụ cho tính toán, truy xuất dữ liệu. Chúng ta có thể sử dụng hàm VALUE trong Excel để chuyển đổi sang dữ liệu số một cách dễ dàng, nhanh chóng sau đây:

=VALUE(“15:00”) kết quả thu về là 0,625: số thập phân sẽ tương đương với 15 giờ bởi chúng được lưu trữ trong Excel.

=VALUE(“12,000”) kết quả thu về là 12000: một chuỗi tương ứng với chuỗi văn bản.

=VALUE(“4:30”)+VALUE(“0:30”) kết quả thu về là 0.208333333: tương đương với 5 giờ sáng. Nghĩa là 4:30 + 00:30 = 5:00.

*
Ví dụ cách áp dụng công thức VALUE trong Excel.

Hướng dẫn cách dùng hàm VALUE với hàm MID, RIGHT

Sau đây, Sforum sẽ hướng dẫn cho các bạn cách dùng hàm VALUE với hàm MID, hàm RIGHT trong Excel.

*
Cách kết hợp VALUE (MID) và VALUE(RIGHT) trong Excel.

Hàm VALUE (MID) trong Excel

Sử dụng hàm VALUE và hàm MID trong Excel với mục đích trích xuất các ký tự từ giữa chuỗi bắt đầu bằng ký tự. Công thức cụ thể sau:

=VALUE(MID(A,t,n))

Trong đó:

A: ô có chứa đoạn văn bản mà người dùng muốn chuyển đổi.

t: là số ký tự bắt đầu mà bạn muốn trích xuất.

n: số ký tự muốn trích xuất.

Với công thức này, các bạn có thể tách chữ số, đồng thời, chuyển đổi văn bản thành số.

*
Cách kết hợp VALUE và MID trong Excel

Hàm VALUE (RIGHT) trong Excel

Nếu bạn muốn trích xuất ký tự đầu hoặc cuối trong một chuỗi và trả về kết quả bằng số thì bạn có thể sử dụng hàm VALUE với hàm RIGHT.

Công thức cụ thể sau đây:

=VALUE(RIGHT(A,n))

Trong đó:

A: ô chứa đoạn văn bản muốn chuyển đổi.

n: số ký tự muốn trích xuất.

Xem thêm: Những Bài Hát Viêt Hay Nhất, Những Bài Nhạc Trẻ Hot Nhất Một Thời

*
Cách kết hợp VALUE và RIGHT trong Excel.

Vậy là chúng ta đã cùng nhau tìm hiểu về cách sử dụng hàm VALUE trong Excel. Hy vọng những thông tin trên sẽ giúp ích cho các bạn trong công việc, học tập, cuộc sống. Đừng quên theo dõi Sforum để biết thêm nhiều thông tin thú vị về game, công nghệ nhé!