Sử dụng các toán tử cho số học đơn giản 4M 44S
Tạo cơ sở dữ liệu mới 3M 2s
Đây là hai giá trị trong hai ô Excel.
Tôi muốn loại bỏ hai từ cuối cùng, nghĩa là 4M 44S khỏi mọi ô để đầu ra phải:
Sử dụng các toán tử cho số học đơn giản
Tạo cơ sở dữ liệu mới
=LEFT[A1,FIND[CHAR[1],SUBSTITUTE[A1," ",CHAR[1],LEN[A1]-LEN[SUBSTITUTE[A1," ",""]]-1]]]
Nhưng chức năng này chỉ loại bỏ từ cuối. Làm cách nào để loại bỏ hai từ cuối cùng khỏi mỗi ô?
Hỏi ngày 28 tháng 8 năm 2012 lúc 4:48Aug 28, 2012 at 4:48
1
Có lẽ bạn có một không gian dấu vết trong văn bản của bạn bởi vì Excellll nói rằng nó sẽ hoạt động khác. Hãy thử thêm vào một số chức năng TRIM để loại bỏ các không gian theo dõi [TRIM cũng sẽ loại bỏ bất kỳ không gian hàng đầu nào và tạo bất kỳ không gian bên trong kép nào vào không gian đơn lẻ ..... nhưng tôi cho rằng điều đó sẽ không phải là vấn đề?]
=LEFT[TRIM[A1],FIND[CHAR[1],SUBSTITUTE[TRIM[A1]," ",CHAR[1],LEN[TRIM[A1]]-LEN[SUBSTITUTE[TRIM[A1]," ",""]]-1]]]
Hoặc đây là một cách khác [ngắn hơn],
=LEFT[A1,FIND["@@REPLACE@@",SUBSTITUTE[TRIM[A1]," ","@@REPLACE@@",LEN[TRIM[A1]]-LEN[SUBSTITUTE[TRIM[A1]," ",""]]-1]]-1]
Đã trả lời ngày 28 tháng 8 năm 2012 lúc 18:01Aug 28, 2012 at 18:01
Barry Houdinibarry Houdinibarry houdini
10,8K1 Huy hiệu vàng19 Huy hiệu bạc25 Huy hiệu đồng1 gold badge19 silver badges25 bronze badges
Đây là một công thức sẽ làm điều đó. Nó hơi dài, nhưng nó sẽ làm được điều đó:
=LEFT[A1,FIND["@@REPLACE@@",SUBSTITUTE[TRIM[A1]," ","@@REPLACE@@",LEN[TRIM[A1]]-LEN[SUBSTITUTE[TRIM[A1]," ",""]]-1]]-1]
Công thức hoạt động bằng cách sử dụng hàm
=LEFT[A1,FIND["@@REPLACE@@",SUBSTITUTE[TRIM[A1]," ","@@REPLACE@@",LEN[TRIM[A1]]-LEN[SUBSTITUTE[TRIM[A1]," ",""]]-1]]-1]
=LEFT[A1,FIND["@@REPLACE@@",SUBSTITUTE[TRIM[A1]," ","@@REPLACE@@",LEN[TRIM[A1]]-LEN[SUBSTITUTE[TRIM[A1]," ",""]]-1]]-1]
=LEFT[A1,FIND["@@REPLACE@@",SUBSTITUTE[TRIM[A1]," ","@@REPLACE@@",LEN[TRIM[A1]]-LEN[SUBSTITUTE[TRIM[A1]," ",""]]-1]]-1]
=LEFT[A1,FIND["@@REPLACE@@",SUBSTITUTE[TRIM[A1]," ","@@REPLACE@@",LEN[TRIM[A1]]-LEN[SUBSTITUTE[TRIM[A1]," ",""]]-1]]-1]
Mong đợi một lỗi sẽ được trả về cho văn bản với hai hoặc ít từ hơn.
Đã trả lời ngày 28 tháng 8 năm 2012 lúc 13:28Aug 28, 2012 at 13:28
ExcellLlexCellllExcellll
12.5K11 Huy hiệu vàng50 Huy hiệu bạc78 Huy hiệu đồng11 gold badges50 silver badges78 bronze badges
Công thức Excel để xóa từ đầu tiên hoặc cuối cùng khỏi một ô.
Bạn có thể sao chép và dán các công thức bên dưới để sửa nhanh và cũng tìm hiểu cách chúng hoạt động, nếu bạn quan tâm.
Sections:
Loại bỏ từ đầu tiên khỏi một ô
Loại bỏ từ cuối cùng khỏi một ô
Công thức đa năng hơn - Ngăn chặn lỗi
Ghi chú
Loại bỏ từ đầu tiên khỏi một ô
=RIGHT[A1,LEN[A1]-FIND[" ",A1]]
Result:
Loại bỏ từ cuối cùng khỏi một ô
Công thức đa năng hơn - Ngăn chặn lỗi
Ghi chú" " with whatever separator is used. If a dash is used, change that to "-" in your formula.
Điều này giả định văn bản nằm trong ô A1, thay đổi khi cần thiết cho bảng tính của bạn.
Ký tự không gian
Nếu từ của bạn sử dụng dấu gạch ngang hoặc một số dấu phân cách khác, chỉ cần thay thế "" bằng bất kỳ dấu phân nào được sử dụng. Nếu một dấu gạch ngang được sử dụng, hãy thay đổi nó thành "-" trong công thức của bạn.
Giải trình
Có ba chức năng hoạt động ở đây: các hàm Quyền [], Len [] và Find []." " to get the number of characters from the left of the cell until the first space. In the example above this returns 5. That is the number of characters that we want to remove from the cell.
Chức năng phù hợp là những gì có được văn bản từ bên phải của ô. Điều này là cần thiết bởi vì chúng tôi đang trích xuất tất cả các văn bản ngoại trừ từ đầu tiên; Điều đó có nghĩa là chúng ta cần có được mọi thứ ở bên phải của từ đầu tiên trong ô.
Các chức năng LEN và tìm được sử dụng để cho biết đúng chức năng có bao nhiêu ký tự ở bên phải của ô chúng ta muốn có được. Hàm đúng chỉ trích xuất rất nhiều ký tự từ bên phải của ô.
Để có được tất cả mọi thứ ngoại trừ từ đầu tiên, chúng ta cần tìm không gian tách biệt từ đầu tiên và phần còn lại của văn bản. Sử dụng chức năng Tìm, tìm kiếm một không gian "" để lấy số lượng ký tự từ bên trái của ô cho đến không gian đầu tiên. Trong ví dụ trên này, RENTREYS 5. Đó là số lượng ký tự mà chúng tôi muốn xóa khỏi ô.
Loại bỏ từ cuối cùng khỏi một ô
=LEFT[A1,FIND["~",SUBSTITUTE[A1," ","~",LEN[A1]-LEN[SUBSTITUTE[A1," ",""]]]]-1]
Result:
Loại bỏ từ cuối cùng khỏi một ô
Công thức đa năng hơn - Ngăn chặn lỗi if your cells might have the ~ character in them, change that part of the formula to a character that won't be present in the cells.
Công thức đa năng hơn - Ngăn chặn lỗi
Ghi chú" " with whatever separator is used. If a dash is used, change that to "-" in your formula.
Điều này giả định văn bản nằm trong ô A1, thay đổi khi cần thiết cho bảng tính của bạn.
Ký tự không gian
Nếu từ của bạn sử dụng dấu gạch ngang hoặc một số dấu phân cách khác, chỉ cần thay thế "" bằng bất kỳ dấu phân nào được sử dụng. Nếu một dấu gạch ngang được sử dụng, hãy thay đổi nó thành "-" trong công thức của bạn.
Giải trình
Có ba chức năng hoạt động ở đây: các hàm Quyền [], Len [] và Find [].
FIND["~",SUBSTITUTE[A1," ","~",LEN[A1]-LEN[SUBSTITUTE[A1," ",""]]]]
Chức năng phù hợp là những gì có được văn bản từ bên phải của ô. Điều này là cần thiết bởi vì chúng tôi đang trích xuất tất cả các văn bản ngoại trừ từ đầu tiên; Điều đó có nghĩa là chúng ta cần có được mọi thứ ở bên phải của từ đầu tiên trong ô.SUBSTITUTE[A1," ",""] removes all spaces in the cell and then counts how many characters are left using the LEN function LEN[SUBSTITUTE[A1," ",""]]. From the example above, the result of this is 10.
Các chức năng LEN và tìm được sử dụng để cho biết đúng chức năng có bao nhiêu ký tự ở bên phải của ô chúng ta muốn có được. Hàm đúng chỉ trích xuất rất nhiều ký tự từ bên phải của ô.LEN[A1]. The result is 12.
Để có được tất cả mọi thứ ngoại trừ từ đầu tiên, chúng ta cần tìm không gian tách biệt từ đầu tiên và phần còn lại của văn bản. Sử dụng chức năng Tìm, tìm kiếm một không gian "" để lấy số lượng ký tự từ bên trái của ô cho đến không gian đầu tiên. Trong ví dụ trên này, RENTREYS 5. Đó là số lượng ký tự mà chúng tôi muốn xóa khỏi ô.2 in this example. This number is the total number of spaces that are in the cell and this tells the other SUBSTITUTE function which space to replace; we want to replace the last space so we can find it more easily.
Để có được số mà chúng tôi muốn giữ, chúng tôi cần sử dụng hàm LEN để đếm tất cả các ký tự trong ô. Kết quả của hàm LEN là 12 vì có 12 ký tự trong ô.~ and then the FIND function is used to find that ~ and then the formula knows how many characters from the left of the cell the last space is.
Trong công thức trên, chúng tôi trừ kết quả tìm thấy từ Len, là 12-5 hoặc 7.
Bây giờ, chúng ta có tổng số ký tự từ bên trái của ô cho đến không gian xuất hiện trước từ cuối cùng trong ô và chúng ta có thể sử dụng hàm bên trái để trả về mọi thứ ngoại trừ từ cuối cùng.
Nếu bạn vẫn còn một chút bối rối, hãy xem hướng dẫn của chúng tôi về chức năng thay thế trong Excel
Công thức đa năng hơn - Ngăn chặn lỗi
Nếu bạn sử dụng một trong các công thức trên trong danh sách và bất kỳ ô nào trong danh sách đó chứa một từ duy nhất, một lỗi sẽ xuất hiện.
Để tránh bất kỳ lỗi nào, chúng ta có thể sử dụng hàm iferror [] đơn giản.
Công thưc 1:
=IFERROR[RIGHT[A1,LEN[A1]-FIND[" ",A1]],A1]
Công thức 2:
=IFERROR[LEFT[A1,FIND["~",SUBSTITUTE[A1," ","~",LEN[A1]-LEN[SUBSTITUTE[A1," ",""]]]]-1],A1]
Điều này nói rằng nếu có lỗi, hãy xuất nội dung của ô ban đầu. Các lỗi như thế này thường xảy ra nếu chỉ có một từ trong ô, vì vậy điều này sẽ xuất ra từ đó.
Đọc hướng dẫn của chúng tôi để tìm hiểu thêm về chức năng iferror trong Excel và ngăn chặn các lỗi nói chung.
Ghi chú
Những công thức này có thể gây nhầm lẫn. Tôi khuyên bạn nên chỉ cần sao chép/dán chúng vào bảng tính của bạn và thay đổi các tham chiếu ô khi cần thiết.
Để tạo phạm vi mới của văn bản giá trị thay vì các công thức, hãy làm theo hướng dẫn này để chuyển đổi công thức thành các giá trị.
Đảm bảo tải xuống tệp mẫu được đính kèm vào hướng dẫn này để có được các ví dụ này trong Excel.