Hướng dẫn extract first letter in excel - trích xuất chữ cái đầu tiên trong excel

Khi sử dụng dữ liệu trong Excel đã được nhập từ một nguồn khác, văn bản thường không phải là cách bạn muốn xem nó.

Bạn có thể thấy rằng các tài liệu tham khảo sản phẩm bao gồm mã sản phẩm, tham chiếu mã và kích thước sản phẩm tất cả được nối thành một đoạn văn bản xuất hiện trong một ô trong bảng tính.

Yêu cầu của bạn là bạn muốn phân chia các phần có liên quan của chuỗi văn bản để tất cả chúng xuất hiện trong các ô riêng lẻ trong bảng tính của bạn.

Điều này có thể là do bạn muốn sắp xếp hoặc nhóm theo một phần văn bản cụ thể. Bạn cũng có thể muốn sử dụng các công thức khác trên phần chia cụ thể đó. Ví dụ: bạn có thể muốn tìm kiếm trên một bảng dữ liệu khác, sử dụng phần đó của văn bản làm giá trị tra cứu. Điều này là không thể với dữ liệu ở dạng ban đầu.

Nếu bạn đang sử dụng dữ liệu trong bảng trục, bạn có thể yêu cầu một trường riêng biệt có dữ liệu phân chia để cho phép lọc hoặc hợp nhất.

Bài viết này mô tả bảy cách trong đó bạn có thể trích xuất N ký tự đầu tiên hoặc cuối cùng từ một chuỗi dữ liệu văn bản trong Microsoft Excel.

Dữ liệu mẫu được sử dụng trong bài đăng này

Các ví dụ trong bài đăng này sẽ trích xuất 2 ký tự đầu tiên và cuối cùng từ Productsku trong bộ dữ liệu sản phẩm nhỏ trên. 2 ký tự đầu tiên trong SKU chứa mã danh mục sản phẩm và 2 ký tự cuối cùng chứa kích thước sản phẩm.ProductSKU in the above set of small product data. The first 2 characters in the SKU contains the product category code and the last 2 characters contains the product size.

Trích xuất các ký tự có chức năng trái và phải

Excel có một số chức năng hữu ích để chia các chuỗi văn bản để có được văn bản thực tế mà bạn cần.

Hàm trái

Syntax::

LEFT [ Text, [Number] ]

• Text & nbsp; - Đây là chuỗi văn bản mà bạn muốn trích xuất. Nó cũng có thể là một tham chiếu ô hợp lệ trong một sổ làm việc. – This is the text string that you wish to extract from. It can also be a valid cell reference within a workbook.
• Số [Tùy chọn] - Đây là số lượng ký tự mà bạn muốn trích xuất từ ​​chuỗi văn bản. Giá trị phải lớn hơn hoặc bằng không. Nếu giá trị lớn hơn độ dài của chuỗi văn bản, thì tất cả các ký tự sẽ được trả về. Nếu giá trị bị bỏ qua, thì giá trị được coi là một. [Optional] – This is the number of characters that you wish to extract from the text string. The value must be greater than or equal to zero. If the value is greater than the length of the text string, then all characters will be returned. If the value is omitted, then the value is assumed to be one.

Điều này sẽ trả về số lượng ký tự được chỉ định từ chuỗi văn bản, bắt đầu ở phía bên trái của văn bản. Nó sẽ trích xuất số lượng ký tự được chỉ định đầu tiên từ văn bản.

Chức năng đúng

Syntax::

RIGHT [ Text, [Number] ]

Các tham số hoạt động theo cùng một cách như đối với hàm bên trái được mô tả ở trên.

Điều này sẽ trả về số lượng ký tự được chỉ định từ chuỗi văn bản, bắt đầu ở phía bên phải của văn bản. Nó sẽ trích xuất số lượng ký tự được chỉ định cuối cùng từ văn bản.

Trích xuất các ký tự có văn bản vào cột

Văn bản đến các cột cho phép bạn chia một cột thành các phần bằng cách xác định các giới hạn cố định cho phần văn bản của bạn. & NBSP; Để sử dụng phương pháp này: allows you to split a column into sections by defining fixed limits to your text section. To use this methodology:

1. Chọn tất cả các dữ liệu trong cột Sản phẩm trong dữ liệu mẫu.ProductSKU column in the sample data.
2. Nhấp vào tab Dữ liệu trong Ribbon Excel.Data tab in the Excel ribbon.
3. Nhấp vào biểu tượng văn bản đến các cột trong nhóm Công cụ dữ liệu của Ribbon Excel và một trình hướng dẫn sẽ xuất hiện để giúp bạn thiết lập cách tách văn bản.Text to Columns icon in the Data Tools group of the Excel ribbon and a wizard will appear to help you set up how the text will be split.
4. Nhấp vào nút Tùy chọn Chiều rộng Đã sửa.Fixed Width option button.
5. Nhấp vào tiếp theo cho bước tiếp theo của trình hướng dẫn.Next for the next step of the wizard.

Trong bước thứ hai, trong cửa sổ Xem trước, nhấp vào thanh đo trong bản xem trước dữ liệu nơi bạn muốn chia văn bản. Điều này sẽ hiển thị một đường thẳng đứng hiển thị sự phân tách. Làm điều này một lần nữa cho các ký tự bên phải.Preview Window, click on the measuring bar in Data Preview where you want to divide the text. This will display a vertical line showing the separation. Do this again for the right-hand characters.

Nhấp vào tiếp theo để đi đến bước cuối cùng của trình hướng dẫn.Next to go to the final step of the wizard.

Đặt ô đích thành E3, để dữ liệu hiện tại không bị ghi đè.E3, so that existing data does not get overwritten.

Nhấp vào Kết thúc và dữ liệu của bạn sẽ được nhập vào ba cột bắt đầu tại Cell E3.Finish and your data will be imported into three columns commencing at cell E3.

Bảng tính của bạn bây giờ sẽ trông giống như ở trên với kết quả được chia thành các ô riêng biệt.

Trích xuất các ký tự với flash điền

Phương pháp điền flash này cho phép bạn nhập một vài ví dụ vào các ô nơi bạn muốn dữ liệu phân chia của mình xuất hiện. Excel sau đó tìm ra dữ liệu cần thiết trong cột dựa trên các ví dụ của bạn.

Nhược điểm của phương pháp này là các ví dụ chỉ ra có bao nhiêu ký tự sẽ được trích xuất. Điều này hoạt động tốt nếu nó luôn là một số đồng đều, nhưng trong một số trường hợp, nó có thể không, trong trường hợp đó sẽ không hoạt động.

Để sử dụng flash điền, bạn cần nhập ít nhất ba ví dụ về văn bản mà bạn muốn xem.Flash Fill, you need to enter at least three examples of the text that you wish to see.

Bạn cũng có thể kích hoạt flash fill & nbsp; trong nhóm công cụ dữ liệu của tab dữ liệu trên ruy băng Excel hoặc bằng cách sử dụng phím tắt bàn phím Ctrl + E.Flash Fill in the Data Tools group of the Data tab on the Excel ribbon or by using the Ctrl + E keyboard shortcut.

Excel sau đó sẽ tự động điền vào dữ liệu còn lại dựa trên các ví dụ của bạn.

Trích xuất các ký tự với VBA

VBA là ngôn ngữ lập trình nằm phía sau Excel và cho phép bạn viết mã của riêng mình để thao tác dữ liệu hoặc thậm chí tạo các chức năng của riêng bạn.

Để truy cập Trình soạn thảo Visual Basic [VBE], bạn sử dụng Alt + F11.ALT + F11.

Sub SplitText[]
Dim N As Integer
For N = 3 To 12
    ActiveSheet.Cells[N, 5].Value = Left[Cells[N, 2], 2]
Next N
End Sub

Bên trong trình soạn thảo Visual Basic.

1. Nhấp vào chèn vào thanh menu.Insert in the menu bar.
2. Nhấp vào Mô -đun & NBSP; và một khung mới sẽ xuất hiện cho mô -đun.Module and a new pane will appear for the module.
3. Dán vào mã trên. in the above code.

Cửa sổ của bạn bây giờ sẽ trông giống như ở trên.

Đặt con trỏ bất cứ nơi nào trong quy trình mã và sau đó nhấn F5 để chạy mã.F5 to run the code.

Mã vòng lặp qua các giá trị từ 3 đến 12, là số hàng cho dữ liệu mẫu. Nó nhập hai ký tự đầu tiên của văn bản được tìm thấy ở số hàng n và cột 2 vào cột 5 [cột E].

Đây là mã VBA rất đơn giản và nó có nhược điểm là chỉ cần hai ký tự đầu tiên của mã sản phẩm. Nếu mã sản phẩm dài hơn thì kết quả sai sẽ được tạo ra. Tuy nhiên, mã có thể được mở rộng để tìm kiếm ký tự Delimiter [-] và sau đó đưa số lượng ký tự lên đến điểm đó.
However, the code could be extended to search for the delimiter character [-] and then take the number of characters up to that point.

Trích xuất các ký tự với truy vấn sức mạnh

Truy vấn sức mạnh là một công cụ hữu ích trong Excel để thao tác dữ liệu và nó có thể dễ dàng được sử dụng để chia một cột dữ liệu thành các phần.

Đầu tiên, bạn cần chuyển đổi dữ liệu của mình thành bảng Excel.

Tạo một truy vấn từ bảng/phạm vi.From Table/Range query.

1. Chọn một ô bên trong bảng. a cell inside the table.
2. Chuyển đến tab Dữ liệuData tab
3. Nhấp vào từ bảng/phạm vi & nbsp; trong nhóm dữ liệu GET & Biến đổi.From Table/Range in the Get & Transform Data group.

Điều này sẽ mở ra Trình chỉnh sửa truy vấn Power cho phép bạn chuyển đổi dữ liệu.

1. Nhấp vào cột Sản phẩm.ProductSKU column.
2. Nhấp vào tab Thêm cột của Trình chỉnh sửa truy vấn Power.Add Column tab of the power query editor.
3. Nhấp vào trích xuất trong nhóm văn bản.Extract in the From Text group.
4. Chọn & nbsp; ký tự đầu tiên trong thả xuống.First Characters in the drop-down.

Một cửa sổ bật lên sẽ được hiển thị. Nhập 2 vào hộp đếm. Nhấp vào OK và một cột mới có tên các ký tự đầu tiên sẽ được thêm. & NBSP; nhấp đúp vào tiêu đề cột mới và đổi tên thành danh mục.OK and a new column called First Characters will be added. Double-click on the new column header and rename it to Category.

= Table.AddColumn[#"Changed Type", "First Characters", each Text.Start[[ProductSKU], 2], type text]

Điều này sẽ dẫn đến công thức mã M ở trên.

Nếu bạn cần 2 ký tự cuối cùng, thì hãy nhấp vào các ký tự cuối cùng trong trình bày trích xuất.Last Characters in the Extract drop-down.

= Table.AddColumn[#"Inserted First Characters", "Last Characters", each Text.End[[ProductSKU], 2], type text]

Nó sẽ dẫn đến công thức mã M ở trên.

Nhấp vào Đóng và tải trong nhóm Đóng trên tab Trang chủ của Ribbon và một bảng tính mới sẽ được thêm vào sổ làm việc của bạn với một bảng dữ liệu ở định dạng mới.Close and Load in the Close group on the Home tab of the ribbon, and a new worksheet will be added to your workbook with a table of the data in the new format.

Trích xuất các ký tự với cột được tính toán trục nguồn

Bạn cũng có thể sử dụng bảng trục nguồn để hiển thị hai ký tự đầu tiên của mã sản phẩm.

Để làm điều này, bạn cần chuyển đổi dữ liệu của mình thành bảng Excel.

Để nhập dữ liệu của bạn vào Power Pivot:

1. Chọn một ô bên trong bảng. a cell inside the table.
2. Chuyển đến tab Dữ liệuPower Pivot tab. If you don’t see the Power Pivot tab, you can install it by going to the Data tab and clicking on Manage Data Model.
3. Nhấp vào từ bảng/phạm vi & nbsp; trong nhóm dữ liệu GET & Biến đổi.Add to Data Model.

Điều này sẽ mở ra Trình chỉnh sửa truy vấn Power cho phép bạn chuyển đổi dữ liệu.Power Pivot tab by selecting Manage in the Data Model group in the Excel ribbon.

Nhấp vào cột Sản phẩm.Add Column and click on Rename Column in the pop-up menu. You can also double click on the column to rename it. Then you can rename this to something like Category.

= LEFT[Products[ProductSKU], 2]

Nhấp vào tab Thêm cột của Trình chỉnh sửa truy vấn Power.Category column and insert the above formula into the formula bar. Regardless of which cell you enter this formula in, it will propagate throughout the entire column.

Nhấp vào trích xuất trong nhóm văn bản.

= RIGHT[Products[ProductSKU], 2]

Chọn & nbsp; ký tự đầu tiên trong thả xuống.

Một cửa sổ bật lên sẽ được hiển thị. Nhập 2 vào hộp đếm. Nhấp vào OK và một cột mới có tên các ký tự đầu tiên sẽ được thêm. & NBSP; nhấp đúp vào tiêu đề cột mới và đổi tên thành danh mục.Pivot Table icon in the ribbon and click on Pivot Table in the pop-up menu in order to turn your source data into a spreadsheet pivot table.

Điều này sẽ dẫn đến công thức mã M ở trên.OK. If the Pivot Table Fields pane does not automatically display, right click on the pivot table skeleton and select Show Field List.

Nếu bạn cần 2 ký tự cuối cùng, thì hãy nhấp vào các ký tự cuối cùng trong trình bày trích xuất.Category or Size.

Nó sẽ dẫn đến công thức mã M ở trên.Pivot Table Fields Pane, double click on your data source name e.g. Products and this will show the available fields. Check all the boxes to use all fields in your pivot table.

Nhấp vào Đóng và tải trong nhóm Đóng trên tab Trang chủ của Ribbon và một bảng tính mới sẽ được thêm vào sổ làm việc của bạn với một bảng dữ liệu ở định dạng mới.Rows box, drag the Category field to the top of the list so that the pivot table will summarise by different categories.

Trích xuất các ký tự với cột được tính toán trục nguồn

Bạn cũng có thể sử dụng bảng trục nguồn để hiển thị hai ký tự đầu tiên của mã sản phẩm.

Để làm điều này, bạn cần chuyển đổi dữ liệu của mình thành bảng Excel.ProductSKU, you use the same data source instructions as for the Calculated Column methodology shown above.

Để nhập dữ liệu của bạn vào Power Pivot:Power Pivot tab and click on the Manage icon, you can enter a Measure into the Power Pivot Manager window.

Chuyển đến tab Power Pivot. & NBSP; Nếu bạn không thấy tab Power Pivot, bạn có thể cài đặt nó bằng cách truy cập tab Dữ liệu và nhấp vào quản lý mô hình dữ liệu.

Category List :=
CONCATENATEX [
    VALUES [ Products[Category] ],
    Products[Category],
    ", "
]

Chọn ô đầu tiên trong phần đo và nhập công thức trên trong thanh công thức.

Một biện pháp luôn luôn cần trả lại giá trị vô hướng. Đây là lý do tại sao hàm Concatenatex được sử dụng trước tiên để tổng hợp kết quả của các cột được tính toán được tạo từ các hàm bên trái hoặc bên phải. Nếu hàm ConcatenateX không có, thì điều này sẽ trả về giá trị lỗi và không thể được sử dụng trong bảng Pivot.

Nhấp vào biểu tượng bảng Pivot trong Ribbon và làm theo các hướng dẫn để tạo bảng Pivot như được hiển thị ở trên trong ví dụ cột được tính toán.Pivot Table icon in the ribbon, and follow the instructions to create the pivot table as shown above in the calculated column example.

Trong danh sách các trường bảng Pivot, hiện có một trường được tính toán được gọi là danh mục, được hiển thị với FX phía trước nó. Điều này trả về hai ký tự đầu tiên của các sản phẩm hiện trườngku.Category, shown with fx in front of it. This returns the first two characters of the field ProductSKU.

Bởi vì một biện pháp luôn luôn là một trường được tính toán, nó chỉ có thể được đưa vào như một giá trị, mặc dù nó thực sự trả về một giá trị văn bản trong trường hợp này. Nó không thể được sử dụng như một bộ lọc hoặc một hàng hoặc tên cột.

Đây là một bất lợi rất lớn khi sử dụng một cột được tính toán trong trục nguồn.

Sự kết luận

Có nhiều cách để có được một vài ký tự đầu tiên hoặc cuối cùng từ văn bản của bạn trong Excel.

Bạn có thể sử dụng công thức, văn bản đến cột, flash điền, VBA, truy vấn nguồn hoặc trục nguồn.

Bạn thích cái nào hơn?

Thông tin về các Tác giả

John là một MVP của Microsoft và chuyên gia tính toán đủ điều kiện với hơn 15 năm kinh nghiệm. Ông đã làm việc trong một loạt các ngành công nghiệp, bao gồm bảo hiểm, công nghệ quảng cáo và gần đây nhất là tư vấn nền tảng quyền lực. Anh ấy là một người giải quyết vấn đề sắc sảo và có niềm đam mê sử dụng công nghệ để làm cho các doanh nghiệp hiệu quả hơn.

Làm thế nào để bạn trích xuất tên đầu tiên từ một ô trong Excel?