Hướng dẫn shortcut to separate text and number in excel - phím tắt tách chữ và số trong excel

Đôi khi bạn có thể có dữ liệu văn bản và số trong cùng một ô và bạn có thể có nhu cầu tách phần văn bản và phần số trong các ô khác nhau.

Mặc dù không có phương pháp sẵn có để thực hiện điều này một cách cụ thể, nhưng có một số tính năng và công thức Excel bạn có thể sử dụng để hoàn thành việc này.

Trong hướng dẫn này, tôi sẽ chỉ cho bạn 4 cách đơn giản và dễ dàng để tách văn bản và số trong Excel.separate text and numbers in Excel.

Chúng ta hãy đi đến đó!

• Văn bản và số riêng biệt bằng cách sử dụng flash fill
• Riêng văn bản và số bằng cách sử dụng công thức
• Văn bản và số riêng biệt bằng VBA (chức năng tùy chỉnh)
• Văn bản và số riêng biệt bằng cách sử dụng truy vấn nguồn

Văn bản và số riêng biệt bằng cách sử dụng flash fill

Riêng văn bản và số bằng cách sử dụng công thức

Văn bản và số riêng biệt bằng VBA (chức năng tùy chỉnh)

Văn bản và số riêng biệt bằng cách sử dụng truy vấn nguồn

Dưới đây tôi có dữ liệu nhân viên trong cột A, trong đó một vài bảng chữ cái đầu tiên cho biết bộ phận của nhân viên và các số sau khi nó cho biết số nhân viên.

Từ dữ liệu này, tôi muốn tách phần văn bản và phần số và đưa chúng thành hai cột riêng biệt (cột B và C).

Phương pháp đầu tiên mà tôi muốn cho bạn thấy bạn tách văn bản và số trong Excel là sử dụng flash điền.

1. Flash Fill (được giới thiệu trong Excel 2013) hoạt động bằng cách xác định các mẫu dựa trên đầu vào của người dùng.
2. Vì vậy, nếu tôi nhập thủ công kết quả dự kiến ​​vào cột B, flash fill sẽ cố gắng và giải mã mẫu và cho tôi kết quả cho tất cả các ô trong cột đó.

1. Dưới đây là các bước để tách phần văn bản khỏi ô và nhận nó trong cột B:

1. Chọn ô B2
2. Nhập thủ công kết quả dự kiến ​​trong ô B2, sẽ là MKT

Với ô B2 được chọn, đặt con trỏ ở phần dưới cùng bên phải của lựa chọn. Bạn sẽ nhận thấy rằng con trỏ thay đổi thành một biểu tượng cộng (cái này được gọi là tay cầm điền)

& nbsp; giữ phím chuột/trackpad trái và kéo tay cầm điền để lấp đầy các ô. Don Tiết lo lắng nếu các tế bào chứa đầy cùng một văn bản

Nhấp vào biểu tượng Tùy chọn điền tự động, sau đó chọn tùy chọn ‘Flash Fill

Các bước trên sẽ trích xuất phần văn bản từ các ô trong cột A và cho bạn kết quả trong cột B.

Riêng văn bản và số bằng cách sử dụng công thức

Văn bản và số riêng biệt bằng VBA (chức năng tùy chỉnh)

Văn bản và số riêng biệt bằng cách sử dụng truy vấn nguồn

Dưới đây tôi có dữ liệu nhân viên trong cột A, trong đó một vài bảng chữ cái đầu tiên cho biết bộ phận của nhân viên và các số sau khi nó cho biết số nhân viên.

=LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))-1)

Từ dữ liệu này, tôi muốn tách phần văn bản và phần số và đưa chúng thành hai cột riêng biệt (cột B và C).

=MID(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),"")),100)

Phương pháp đầu tiên mà tôi muốn cho bạn thấy bạn tách văn bản và số trong Excel là sử dụng flash điền.

Flash Fill (được giới thiệu trong Excel 2013) hoạt động bằng cách xác định các mẫu dựa trên đầu vào của người dùng.

Vì vậy, nếu tôi nhập thủ công kết quả dự kiến ​​vào cột B, flash fill sẽ cố gắng và giải mã mẫu và cho tôi kết quả cho tất cả các ô trong cột đó.

Dưới đây là các bước để tách phần văn bản khỏi ô và nhận nó trong cột B:

Chọn ô B2

{#VALUE!,4,#VALUE!,#VALUE!,#VALUE!,6,#VALUE!,5,#VALUE!,#VALUE!}

• Nhập thủ công kết quả dự kiến ​​trong ô B2, sẽ là MKT
• Với ô B2 được chọn, đặt con trỏ ở phần dưới cùng bên phải của lựa chọn. Bạn sẽ nhận thấy rằng con trỏ thay đổi thành một biểu tượng cộng (cái này được gọi là tay cầm điền)
• & nbsp; giữ phím chuột/trackpad trái và kéo tay cầm điền để lấp đầy các ô. Don Tiết lo lắng nếu các tế bào chứa đầy cùng một văn bản

Nhấp vào biểu tượng Tùy chọn điền tự động, sau đó chọn tùy chọn ‘Flash Fill

Các bước trên sẽ trích xuất phần văn bản từ các ô trong cột A và cho bạn kết quả trong cột B.

Lưu ý rằng trong một số trường hợp, Flash Fill có thể không thể xác định đúng mẫu. Trong những trường hợp như vậy, tốt nhất là nhập kết quả dự kiến ​​vào hai hoặc ba ô, sử dụng tay cầm điền để lấp đầy toàn bộ cột, sau đó sử dụng flash điền vào dữ liệu này.

Bạn có thể theo cùng một quy trình để trích xuất các số trong cột C. Tất cả những gì bạn cần làm là nhập kết quả dự kiến ​​vào ô C2 (Bước 2 trong quy trình được trình bày ở trên)

Lưu ý rằng kết quả bạn nhận được từ Flash Fill là tĩnh và sẽ không cập nhật trong trường hợp bạn thay đổi dữ liệu gốc trong cột A. Nếu bạn muốn kết quả là động, bạn có thể sử dụng phương thức công thức được đề cập tiếp theo.

Dưới đây tôi có dữ liệu nhân viên trong cột A và tôi muốn sử dụng một công thức để chỉ trích xuất phần văn bản và đặt nó vào cột B và trích xuất phần số và đặt nó vào cột C.

Và điều gì sẽ xảy ra nếu tình huống bị đảo ngược - nơi chúng ta có các số trước và văn bản sau và chúng ta muốn tách các số và văn bản?

Bạn vẫn có thể sử dụng cùng một logic với một thay đổi nhỏ - thay vì tìm giá trị tối thiểu cho chúng ta vị trí của chữ số thứ nhất trong ô, bạn cần sử dụng hàm tối đa để tìm vị trí của chữ số cuối cùng trong ô này. Khi bạn có nó, bạn có thể sử dụng lại hàm bên trái hoặc hàm giữa để tách số và văn bản.

Văn bản và số riêng biệt bằng VBA (chức năng tùy chỉnh)

Mặc dù bạn có thể sử dụng các công thức được hiển thị ở trên để phân tách văn bản và số trong một ô và trích xuất chúng vào các ô khác nhau - nếu đây là điều bạn cần làm khá thường xuyên, bạn cũng có tùy chọn để tạo chức năng tùy chỉnh của riêng mình bằng VBA.

Lợi ích của việc tạo chức năng của riêng bạn là sẽ dễ sử dụng hơn rất nhiều (chỉ với một chức năng chỉ mất một đối số).

Bạn cũng có thể lưu mã VBA chức năng tùy chỉnh này vào sổ làm việc macro cá nhân để nó có sẵn trong tất cả các sổ làm việc Excel của bạn.

Bên dưới mã VBA có thể tạo ra một hàm ‘getNumber, sẽ lấy tham chiếu ô làm đối số đầu vào, trích xuất tất cả các số trong ô và đưa ra kết quả.

'Code created by Sumit Bansal from https://trumpexcel.com
'This code will create a function that can separate numbers from a cell
Function GetNumber(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumber = Result
End Function

Và bên dưới mã VBA sẽ tạo ra một hàm khác ‘getText, sẽ lấy tham chiếu ô làm đối số đầu vào và cung cấp cho bạn tất cả văn bản từ ô đó

'Code created by Sumit Bansal from https://trumpexcel.com
''This code will create a function that can separate text from a cell
Function GetText(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetText = Result
End Function

Dưới đây là các bước để thêm mã này vào sổ làm việc Excel của bạn để chức năng này có sẵn để bạn sử dụng trong bảng tính:

1. Nhấp vào tab Nhà phát triển trong Ribbon
2. Nhấp vào biểu tượng Visual Basic

1. Trong Trình chỉnh sửa Visual Basic mở ra, bạn sẽ thấy Project Explorer ở bên trái. Điều này sẽ có sổ làm việc và tên bảng tính của sổ làm việc Excel hiện tại của bạn. Nếu bạn không thấy điều này, nhấp vào tùy chọn Xem trong menu và sau đó nhấp vào Project Explorer

1. Chọn bất kỳ tên trang tính nào (hoặc bất kỳ đối tượng nào) cho sổ làm việc mà bạn muốn thêm chức năng này
2. Nhấp vào tùy chọn Chèn trong thanh công cụ hàng đầu và sau đó nhấp vào mô -đun. Điều này sẽ chèn một mô -đun mới cho sổ làm việc đó

1. Nhấp đúp chuột vào biểu tượng mô -đun trong ‘Project Explorer. Điều này sẽ mở cửa sổ mã mô -đun.

1. Sao chép và dán mã chức năng tùy chỉnh ở trên vào cửa sổ mã mô -đun

1. Đóng trình soạn thảo VB

Với các bước trên, chúng tôi đã thêm mã chức năng tùy chỉnh vào sổ làm việc Excel.

Bây giờ bạn có thể sử dụng các hàm = getNumber hoặc = getText giống như bất kỳ chức năng bảng tính nào khác.

Lưu ý - Khi bạn có mã macro trong cửa sổ mã mô -đun, bạn cần lưu tệp dưới dạng tệp đã bật macro (với tiện ích mở rộng .xlsm thay vì tiện ích mở rộng .xlsx)

Nếu bạn thường có nhu cầu tách số văn bản khỏi các ô trong Excel, sẽ hiệu quả hơn nếu bạn sao chép các mã VBA này để tạo các chức năng tùy chỉnh này và lưu chúng trong sổ làm việc macro cá nhân của bạn.

Bạn có thể tìm hiểu cách tạo và sử dụng sổ làm việc macro cá nhân trong hướng dẫn này mà tôi đã tạo trước đó.

Khi bạn có các chức năng này trong sổ làm việc macro cá nhân, bạn sẽ có thể sử dụng chúng trên bất kỳ sổ làm việc excel nào trên hệ thống của mình.

Một điều quan trọng cần nhớ khi sử dụng các chức năng được lưu trữ trong sổ làm việc macro cá nhân - bạn cần tiền tố tên chức năng với = cá nhân.xlsb !. Ví dụ: nếu tôi muốn sử dụng chức năng getNumber trong sổ làm việc ở Excel và tôi đã lưu mã cho nó trong sổ làm việc vĩ mô bưu chính, tôi sẽ phải sử dụng = cá nhân.xlsb! GetNumber (A2)

Văn bản và số riêng biệt bằng cách sử dụng truy vấn nguồn

Truy vấn sức mạnh đang dần trở thành tính năng yêu thích của tôi trong Excel.

Nếu bạn đã sử dụng Truy vấn Power như một phần của quy trình làm việc của bạn và bạn có một bộ dữ liệu nơi bạn muốn tách văn bản và số thành các cột riêng biệt, Truy vấn Power sẽ thực hiện trong vài lần nhấp.

Khi bạn có dữ liệu của mình trong Excel và bạn muốn sử dụng Truy vấn Power để chuyển đổi dữ liệu đó, một điều kiện tiên quyết là chuyển đổi dữ liệu đó thành bảng Excel (hoặc phạm vi được đặt tên).

Dưới đây tôi có một bảng Excel chứa dữ liệu mà tôi muốn tách các phần văn bản và số thành các cột riêng biệt.

Dưới đây là các bước để làm điều này:

1. Chọn bất kỳ ô nào trong bảng Excel
2. Nhấp vào tab Dữ liệu trong Ribbon

1. Trong nhóm GET and Biến đổi, nhấp vào ‘từ bảng/phạm vi

1. Trong trình chỉnh sửa truy vấn nguồn mở, chọn cột mà bạn muốn tách các số và văn bản
2. Nhấp vào tab Transform trong Ribbon Truy vấn Power

1. Nhấp vào tùy chọn Cột chia

1. Nhấp vào ‘bằng tùy chọn không chữ số để chữ số.

1. Bạn sẽ thấy rằng cột đã được chia thành hai cột trong đó một cột chỉ có văn bản và cái còn lại chỉ có các số

1. [Tùy chọn] Thay đổi tên cột nếu bạn muốn
2. Nhấp vào tab Home và sau đó nhấp vào Đóng và tải. Điều này sẽ chèn một tờ mới và cung cấp cho chúng tôi đầu ra dưới dạng bảng Excel.

Các bước trên sẽ lấy dữ liệu từ bảng excel mà chúng tôi có ban đầu, sử dụng truy vấn nguồn để phân chia cột và tách văn bản và các phần số thành hai cột riêng biệt, sau đó đưa lại cho chúng tôi đầu ra trong một tờ mới trong cùng một sổ làm việc .

Lưu ý rằng chúng tôi đã sử dụng tùy chọn ‘theo tùy chọn không chữ số để chữ số trong bước 7, điều đó có nghĩa là mỗi lần có sự thay đổi từ ký tự không chữ số sang một chữ số, sẽ được chia tách. Nếu bạn có một bộ dữ liệu trong đó các số lần đầu tiên được theo sau bởi văn bản, bạn có thể sử dụng tùy chọn ‘By Digit to Digit

Bây giờ hãy để tôi cho bạn biết phần tốt nhất về phương pháp này. Bảng Excel ban đầu của bạn (là nguồn dữ liệu) được kết nối với bảng Excel đầu ra.

Vì vậy, nếu bạn thực hiện bất kỳ thay đổi nào trong dữ liệu gốc của mình, bạn không cần phải lặp lại toàn bộ quá trình. Bạn chỉ có thể đi đến bất kỳ ô nào trong bảng Excel đầu ra, nhấp chuột phải và sau đó nhấp vào làm mới.

Truy vấn Power sẽ chạy ở phía sau, kiểm tra toàn bộ nguồn dữ liệu gốc, thực hiện tất cả các biến đổi mà chúng tôi đã thực hiện trong các bước trên và cập nhật dữ liệu kết quả đầu ra của bạn.

Đây là nơi truy vấn sức mạnh thực sự tỏa sáng. Nếu có một tập hợp các phép biến đổi mà bạn thường cần thực hiện trên một tập dữ liệu, bạn có thể sử dụng truy vấn nguồn để thực hiện chuyển đổi đó một lần và đặt quy trình. Excel sẽ tạo ra một kết nối giữa nguồn dữ liệu gốc và dữ liệu đầu ra kết quả và ghi nhớ tất cả các bước bạn đã thực hiện để chuyển đổi dữ liệu này.

Bây giờ, nếu bạn nhận được một bộ dữ liệu mới, bạn có thể chỉ cần đặt nó vào vị trí của dữ liệu gốc của mình và làm mới truy vấn, và bạn sẽ nhận được kết quả trong vài giây. Ngoài ra, bạn có thể chỉ cần thay đổi nguồn trong truy vấn nguồn từ bảng Excel hiện có sang một số bảng Excel khác (trong cùng một sổ làm việc hoặc khác nhau).

Vì vậy, đây là bốn cách đơn giản bạn có thể sử dụng để tách số và văn bản trong Excel. Nếu đây là một hoạt động một lần trong khi một lần, bạn nên sử dụng flash điền hoặc phương thức công thức.

Và trong trường hợp đây là điều bạn cần làm khá thường xuyên, sau đó tôi sẽ đề nghị bạn xem xét phương thức VBA nơi chúng tôi tạo ra một chức năng tùy chỉnh hoặc phương thức truy vấn nguồn.

Tôi hy vọng bạn tìm thấy hướng dẫn Excel này hữu ích.

Các hướng dẫn Excel khác mà bạn cũng có thể thích:

• Riêng biệt và họ trong excel (tên phân chia bằng công thức)
• Cách chuyển đổi số thành văn bản trong Excel
• Chuyển đổi văn bản thành các số trong Excel - Hướng dẫn từng bước
• Cách nhanh chóng kết hợp các tế bào trong excel
• Cách kết hợp họ và họ trong Excel
• Cách trích xuất từ ​​đầu tiên từ chuỗi văn bản trong Excel (3 cách dễ dàng)
• Trích xuất số từ một chuỗi trong Excel (sử dụng công thức hoặc VBA)
• Cách trích xuất một chuỗi con trong Excel (sử dụng công thức văn bản)

Làm thế nào để tách văn bản và số từ số trong Excel?

Làm thế nào để tách một chuỗi văn bản thành hai cột trong Excel?

Làm thế nào để nhanh chóng và dễ dàng phân chia các ô theo số trong Excel?

Làm thế nào để bạn tách họ và họ trong Excel?

Phím tắt để tách văn bản trong Excel là gì?

Công thức để tách số từ văn bản trong Excel là gì?

Cách nhanh nhất để tách tên trong Excel là gì?