Tách số ra khỏi chuỗi trong Excel

Trong quá trình làm việc với Excel mình có gặp một số trường hợp yêu cầu tách chỉ lấy phần số trong 1 chuỗi cho trước, nay đăng bài này chia sẻ với các bạn cách làm này. Bài viết dưới đây hướng dẫn chi tiết cách tách số từ chuỗi cho trước trong Excel.

Có 2 cách tách số:

- Trường hợp 1: Tách số từ chuỗi và lấy giá trị dương.

- Trường hợp 2: Tách số từ chuỗi và lấy cả giá trị âm và dương.

1. Tách số từ chuỗi và lấy giá trị dương

Bước 1: Mở File Excel cần tách số -> Nhấn tổ hợp phím Alt + F11-> cửa sổ VBA xuất hiện -> Vào tab Insert -> Module.

Module

Bước 2: Tại cửa sổ lệnh nhập đoạn Code sau:

Nhập code

Function ExtractNumber(rCell As Range)
Dim lCount As Long
Dim sText As String
Dim lNum As String
sText = rCell
For lCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, lCount, 1)) Then
lNum = Mid(sText, lCount, 1) & lNum
End If
Next lCount
ExtractNumber = CLng(lNum)
End Function

Chú ý: Bạn ghi nhớ tên hàm này nhé vì khi trở về File Excel phải sử dụng tới nó.

Bước 3: Sau khi nhập xong kích chọn Save (do chứa macro nên khi lưu hiển thị hộp thoại thông báo bạn cứ chọn OK nhé) -> Quay trở lại File Excel tìm đến hàm như hình vẽ:

ExtractNumber

Bước 4: Copy công thức cho các ô còn lại có kết quả sau:

Kết quả

2. Tách số từ chuỗi và lấy cả giá trị âm và dương

Bước 1: Mở File Excel cần tách số -> Nhấn tổ hợp phím Alt + F11 -> cửa sổ VBA xuất hiện -> Vào tab Insert -> Module.

Module 2

Bước 2: Tại cửa sổ lệnh nhập đoạn Code sau:

Nhập Code 2

Private Function SuperTrim(TheStr As String)
Dim Temp As String, DoubleSpase As String
DoubleSpase = Chr(32) & Chr(32)
Temp = Trim(TheStr)
Temp = Replace(Temp, DoubleSpase, Chr(32))
Do Until InStr(Temp, DoubleSpase) = 0
Temp = Replace(Temp, DoubleSpase, Chr(32))
Loop
SuperTrim = Temp
End Function

Public Function Tach_So(strText As String)
Dim strText_1 As String
Dim subText() As String, so() As Double
Dim i As Integer, j As Integer, k As Integer, m As Integer
strText = SuperTrim(strText)
subText = Split(strText, " ")
For i = 0 To UBound(subText)
For j = 1 To Len(subText(i))
k = 0
If IsNumeric(Mid(subText(i), j, 1)) _
Or (Mid(subText(i), j, 1) = "-" And IsNumeric(Mid(subText(i), j + 1, 1))) Then
k = j
Exit For
End If
Next j
If k <> 0 Then
m = m + 1
strText_1 = Val(Mid(subText(i), k))
ReDim Preserve so(1 To m) As Double
so(m) = strText_1
End If
Next i
If index > 0 And index <= m Then
Tach_So = so(m)
Else
Tach_So = ""
End If
End Function

Chú ý: Ghi nhớ tên hàm ""Tach_so()".

Bước 3: Kích chọn Save bạn cứ chọn OK nhé. Quay trở lại file Excel chọn hàm Tach_so() như hình vẽ:

Chọn hàm

Bước 4: Hàm Tach_so() chỉ có 1 tham số là vị tí ô cần tách.

Vị trí ô cần tách

Bước 5: Copy công thức cho các ô còn lại ta có kết quả:

Kết quả 2

Như vậy tách được cả giá trị âm và dương. Và dấu âm ở vị trí đầu tiên của ô đều lấy được.

Chú ý: Các hàm trên chỉ áp dụng với dãy chứa 1 chuỗi kí tự số liền nhau. Trường hợp dữ liệu là nhiều chuỗi kí tự số khác nhau cách nhau bởi những kí tự chữ cái thì giá trị trả về là chuỗi kí tự số đầu tiên.

Chúc các bạn thành công!

Bình luận (10)

  1. avatar
    Duyon March 03, 2016

    bạn có thể viết hàm "tổng số được tách khỏi chuỗi" giùm mình không? Ví dụ: 01fd12aadg14 cho ra kết quả là 27 (bằng 1+12+14) hay: toán 7, lý 6, văn 5 cho kết quả là 18 (bằng 7+6+5)

  2. avatar
    Quang Tuanon April 07, 2016

    Bạn có thể giúp mình lập hàm tính giá trị của chuỗi text tại A2 3*5*12 . (kết quả tại ô B2 = 180)

  3. avatar
    phạm thanh phươngon June 04, 2016

    Bạn có thể giúp mình khi tách số vẫn giữ được số 0 ở đầu không. Ví dụ: áo trẻ em 00543 thì kết quả là 00543 ạ.

  4. avatar
    nguyễn mạnh hưngon April 14, 2017

    giúp mình với ví dụ mình có chuỗi sô 123456. giờ minh muốn tách ra làm 3 ô mỗi ô chứa các chữ số như ô1: 12. ô2: 34. ô3: 56. tại mình làm trên rất nhiều tên gần 1000 chuỗi. có hàm nào tách được không. vì mình định dạng toàn bộ la text để khi nhập máy số 0 ở đầu không bị mất. giờ phải làm sao?

  5. avatar
    Nguyễn Thị Thu Trangon June 24, 2017

    Mình có dãy 304OG102-2.0-A1 mình muốn tách thành 2 cột. 1 cột là 102, 1 cột 2.0 . giúp mình với. thanks

  6. avatar
    Nguyễn Đức Va Xion September 24, 2017

    Bây giờ em có 2 chuỗi số nằm ở 2 ô tham chiếu là A1 và A2 VD: Chuỗi 1: 01,10,29,38,47,56,65,74,83,92,04,13,22,31,40,59,68,77,86,95,05,14,23,32,41,50,69,78,87,96,07,16,25,34,43,52,61,70,89,98, Chuỗi 2: 01,10,11,21,12,31,13,41,14,51,15,61,16,71,17,81,18,91,19,04,40,14,41,24,42,34,43,44,54,45,64,46,74,47,84,48,94,49,05,50,15,51,25,52,35,53,45,54,55,65,56,75,57,85,58,95,59,07,70,17,71,27,72,37,73,47,74,57,75,67,76,77,87,78,97,79, Mục đích: em muốn viết 1 công thức để lấy ra các ký tự trùng nhau trong 2 chuỗi số này Yêu cầu trong đó.; trả về kết quả 1 chuỗi có dạng như trên nhưng chỉ bao gồm các số trùng nhau trong 2 chỗi số trên. Vì nó vượt quá khả năng của bản thân nên em đăng lên đây mong các bác, anh chị nào có khả năng xin giúp em với ạ. Em xin trân thành cảm ơn.

  7. avatar
    Nguyễn văn phongon October 29, 2017

    Mình có dãy sô dt 0123456789 mình muốn tách số 9 ra thì làm tn

    • avatar
      thảoon January 05, 2018

      =right(ô có chứa dãy số,1)

  8. avatar
    THYon November 13, 2017

    Mình có dữ liệu như thế này: 1/ WHCM . Ma KH:03032471304 LUU THI YEN OANH GD TIEN MAT . Ky TT:11/2017:491510. 2/ RE: KH se thanh toan tong so 1 hoa don 3/ LE KHAC NGOC BOI.03032470666.11/2017:63250 4/ NGUYEN TAN BUU.01042525080.11/2017:36570 5/ PHAN THANH HUYEN.01072810582.11/2017:97520 6/ THAI MINH SON. 01011020293.11/2017:357880 Mình muốn tách 11 số liền nhau thì làm sao, công thức trên mình làm không được

    • avatar
      thảoon January 05, 2018

      Dùng hàm right hoặc left

Viết bình luận