Hàm LEFT hoàn hảo để trích xuất các ký tự bắt đầu từ phía bên trái của chuỗi văn bản. Chúng tôi sử dụng TRÁI trong công thức này để trích xuất tất cả các ký tự cho đến số ký tự chúng tôi muốn cắt
Thách thức đối với các giá trị có độ dài thay đổi là chúng ta không biết chính xác có bao nhiêu ký tự cần trích xuất. Đó là nơi hàm LEN được sử dụng
Làm việc từ trong ra ngoài, LEN tính tổng độ dài của từng giá trị. Đối với D6 [736 dặm] tổng chiều dài là 9. Để có số ký tự cần trích xuất, ta trừ đi 6, tức là độ dài của "dặm", bao gồm cả ký tự khoảng trắng. Kết quả là 3, được đưa sang TRÁI dưới dạng số lượng ký tự cần trích xuất. LEFT sau đó trả về văn bản "736" dưới dạng giá trị văn bản
Cuối cùng, vì chúng tôi muốn một giá trị số [chứ không phải văn bản], chúng tôi chạy văn bản thông qua hàm VALUE, hàm này chuyển đổi số ở định dạng văn bản thành số thực
Một vấn đề rất phổ biến là cách xóa số khỏi ô văn bản trong Excel. Vì một số lý do, các hệ thống khác nhau cố tình trộn văn bản và số. g. Dixon [35208] hoặc vô tình e. g. Ad8am Baum. Dù bằng cách nào thì cũng phải dễ dàng sửa lỗi này trong Excel
Mục lục
- YouTube Xóa số khỏi ô văn bản
- Tìm/Thay thế bằng ký tự đại diện
- Sử dụng Flash Fill
- Có liên quan
YouTube Xóa số khỏi ô văn bản
Tìm/Thay thế bằng ký tự đại diện
Một cách là sử dụng công cụ TÌM/THAY THẾ với các ký tự đại diện. Ký tự đại diện là một cách để yêu cầu Excel tìm thứ gì đó. g. '[' và ']' nhưng không nhìn vào những gì ở giữa vì điều này thay đổi mỗi lần
Ký tự đại diện * có nghĩa là số lượng ký tự không xác định và ? . Vì thế
- [*] sẽ tìm thấy [1] và [321554]
- trong khi [?] sẽ chỉ tìm thấy [1]
Dưới đây chúng tôi muốn loại bỏ các số trong ngoặc để chỉ còn lại tên. Chúng ta cần yêu cầu Excel xóa dấu ngoặc và bất kỳ thứ gì nằm giữa dấu ngoặc [không quan trọng có bao nhiêu ký tự]
Để làm được điều này chúng tôi
- đánh dấu cột,
- Bấm CTRL + H [để hiển thị công cụ tìm thay thế]
- Trong loại hộp Find What [*]- điều này cho Excel biết nếu phải tìm kiếm một ký tự '[', sau đó là các ký tự khác [bao nhiêu tùy ý], và sau đó là ']'
- Ô Replace with để trống
- Sau đó nhấp vào Thay thế tất cả
Kết quả cuối cùng sẽ là tất cả các dấu ngoặc có số trong đó sẽ bị xóa như hình bên dưới. Bạn có thể nhận thấy ở trên rằng có vẻ như có khoảng cách giữa tên và dấu ngoặc. Nếu chúng tôi cũng muốn xóa không gian này, bạn sẽ thêm khoảng trắng ở phía trước tùy chọn 'Tìm gì'
Sử dụng Flash Fill
Ở trên là tuyệt vời khi có một số dạng mẫu. Trong trường hợp trên, các số được đóng gói độc đáo ở cuối giữa các dấu ngoặc. Điều gì sẽ xảy ra nếu các con số chỉ được trải ngẫu nhiên trong văn bản?
Dưới đây chúng tôi có các số trộn lẫn với tên e. g. Ad8am Baum thay vì Adam Baum. Tất cả những gì chúng ta cần làm là nhập những gì chúng ta muốn xem trong B2, tô sáng các ô thích hợp và nhấp vào Flash Fill [hoặc CTRL + E]
Trong trường hợp này, bạn sẽ thấy rằng cái đầu tiên đúng và những cái còn lại thì không tốt. Điều này là do chúng tôi chỉ đưa ra một ví dụ
Tất cả những gì chúng ta cần làm là đi đến cái sai đầu tiên và nhập những gì chúng ta muốn xem. Vậy AlBi6no phải đổi thành Al Bino. Khi bạn nhấn enter, Excel sẽ áp dụng 2 ví dụ mà bạn đã đưa ra cho mọi thứ [trên và dưới]
Chúng ta đã học cách tách các giá trị số khỏi một ô trong excel 2016 trở lên. Các công thức chúng tôi đã sử dụng hơi phức tạp nhưng giờ đây Excel 2019 và 365 đã có trong trò chơi.
Excel 2019 và 365 giới thiệu một số hàm mới giúp giảm bớt tác vụ xóa các ký tự không phải là số và chỉ truy xuất các giá trị số trong một ô mới. Chúng tôi sẽ sử dụng các công thức có thể giúp chúng tôi làm như vậy một cách thuận tiện hơn.
Công thức chung
Jumbled_text. Đây là văn bản nguồn mà bạn muốn trích xuất tất cả các giá trị số
NumChars. Đây là tổng số ký tự bạn muốn xử lý. jumbled_text không được có nhiều ký tự hơn số này [kết hợp cả ký tự và số]
Hãy xem một ví dụ để làm cho mọi thứ rõ ràng
Thí dụ. Xóa các ký tự không phải là số và trích xuất tất cả các số
Vì vậy, ở đây chúng tôi có một số văn bản lộn xộn. Văn bản này chứa một số số và một số ký tự không phải là số. Tôi cần loại bỏ các ký tự không phải là số và chỉ nhận các giá trị số trong cột D
Tôi không mong đợi tổng số ký tự trong văn bản lộn xộn nhiều hơn 20. Vì vậy, giá trị của NumChars là 20 ở đây. Bạn có thể tăng con số này nếu bạn cần
Áp dụng công thức chung ở trên để loại bỏ các ký tự không phải là số
Và khi bạn nhấn nút enter. Bạn xóa tất cả các ký tự không phải là số. Kéo công thức này xuống để xóa các ký tự khỏi chuỗi khỏi tất cả các ô trong cột C3
Làm thế nào nó hoạt động?
Trước tiên hãy xem cách giải công thức này từng bước
1->TEXTJOIN["",TRUE,IFERROR[MID[C3,SEQUENCE[20],1]+0,""]]2->TEXTJOIN["",TRUE,IFERROR[MID["12asw12w123",{1 . ;#GIÁ TRỊ. ;#GIÁ TRỊ. ;1;2;#VALUE. ;1;2;3;#VALUE. ;#GIÁ TRỊ. ;. ;#GIÁ TRỊ. }+0,""]]5->TEXTJOIN["",TRUE,{1;2;"";"";"";1;2;"";1;2;3;"";""Như bạn thấy, công thức bắt đầu giải từ bên trong. Lúc đầu, chức năng SEQUENCE được giải quyết. Vì chúng ta đã vượt qua 20. Nó trả về một mảng các số bắt đầu từ 1 đến 20
Mảng này được phục vụ cho Hàm MID làm số bắt đầu. Hàm mid chuyển đến từng chỉ mục trong chuỗi và tách từng ký tự. Bạn có thể thấy điều đó ở bước 3
Tiếp theo, chúng tôi thêm 0 vào mỗi ký tự. Trong excel nếu bạn cố gắng thêm số vào các ký tự không phải là số, kết quả sẽ là #VALUE. Lỗi. Vì vậy, chúng tôi nhận được một mảng số và #VALUE. lỗi. Các ký tự không phải là số đã biến mất
Hàm IFERROR tiếp theo thay thế tất cả các lỗi #VALUE bằng "" [trống]. Bây giờ chúng ta chỉ còn lại các giá trị số và dấu cách
Cuối cùng, mảng này được cung cấp cho Hàm TEXTJOIN. Hàm TEXTJOIN nối chúng ta được chuỗi chỉ chứa số trong đó
Nâng cao công thức
Công thức trên đã sử dụng một số được mã hóa cứng để xử lý số lượng ký tự [chúng tôi lấy hai mươi]. Nhưng bạn có thể muốn nó năng động. Trong trường hợp đó, bạn đoán đúng rồi, chúng ta sẽ sử dụng hàm LEN. Nó sẽ mất một số ký tự chính xác để xử lý. Vậy công thức sẽ là
Tại đây, hàm LEN sẽ tự động dò tìm chính xác ký tự số trong chuỗi ký tự chữ và số. Điều này sẽ giảm bớt gánh nặng xác định số ký tự tối đa
Thay thế hàm SEQUENCE
Nếu không muốn sử dụng hàm SEQUENCE thì bạn có thể sử dụng kết hợp hàm ROW và hàm INDIRECT để tạo số thứ tự
INDIRECT sẽ chuyển đổi văn bản ["1. 20"] vào phạm vi thực tế và sau đó hàm ROW sẽ liệt kê tất cả các số hàng từ 1 đến 20. [20 chỉ là ví dụ. Nó có thể là bất kỳ số nào]
Vì vậy, vâng các bạn, đây là cách bạn có thể tách các ký tự không phải là số từ một chuỗi chữ và số trong excel. Tôi hy vọng tôi đã giải thích đủ và bài viết này đã giúp bạn. Nếu bạn có bất kỳ câu hỏi nào liên quan đến chủ đề này hoặc bất kỳ chủ đề excel/VBA nào khác. Cho đến lúc đó tiếp tục xuất sắc
Những bài viết liên quan
Tách số và văn bản từ chuỗi trong Excel 2016 trở lên. Khi chưa có hàm TEXTJOIN, chúng ta sử dụng hàm LEFT và RIGHT cùng với một số hàm khác để tách các ký tự số và ký tự không phải số trong một chuỗi.
Trích xuất văn bản từ một chuỗi trong Excel bằng hàm LEFT và RIGHT của Excel. Để xóa văn bản trong excel khỏi chuỗi chúng ta có thể sử dụng hàm LEFT và RIGHT của excel. Các chức năng này giúp chúng tôi cắt chuỗi động
Xóa khoảng trắng ở đầu và cuối văn bản trong Excel. Không gian đầu và cuối khó nhận biết bằng mắt thường và có thể làm rối dữ liệu của bạn. Tách các ký tự này khỏi chuỗi là nhiệm vụ cơ bản và quan trọng nhất trong quá trình làm sạch dữ liệu. Đây là cách bạn có thể làm điều đó một cách dễ dàng trong Excel
Xóa ký tự từ bên phải. Để loại bỏ các ký tự bên phải của một chuỗi trong Excel, chúng ta sử dụng hàm LEFT. Có, chức năng LEFT. Hàm LEFT giữ lại số lượng ký tự đã cho từ LEFT và loại bỏ mọi thứ từ bên phải của nó
Loại bỏ các ký tự không mong muốn trong Excel. Để loại bỏ các ký tự không mong muốn khỏi một chuỗi trong Excel, chúng ta sử dụng hàm SUBSTITUTE. Hàm SUBSTITUTE thay thế các ký tự đã cho bằng một ký tự đã cho khác và tạo ra một chuỗi đã thay đổi mới
Cách xóa văn bản trong Excel bắt đầu từ một vị trí trong Excel. Để xóa văn bản khỏi vị trí bắt đầu trong chuỗi, chúng ta sử dụng hàm REPLACE của Excel. Chức năng này giúp chúng ta xác định vị trí bắt đầu và số ký tự cần tách
Các bài viết phổ biến
50 phím tắt Excel để tăng năng suất của bạn. Nhận nhiệm vụ của bạn nhanh hơn. 50 phím tắt này sẽ giúp bạn làm việc nhanh hơn trên Excel
Cách sử dụng Hàm VLOOKUP trong Excel. Đây là một trong những hàm phổ biến và được sử dụng nhiều nhất trong excel được sử dụng để tra cứu giá trị từ các phạm vi và trang tính khác nhau.
Cách sử dụng Hàm COUNTIF trong Excel. Đếm các giá trị có điều kiện bằng chức năng tuyệt vời này. Bạn không cần lọc dữ liệu của mình để đếm các giá trị cụ thể. Chức năng Countif là điều cần thiết để chuẩn bị bảng điều khiển của bạn
Cách sử dụng hàm SUMIF trong Excel. Đây là một chức năng thiết yếu khác của bảng điều khiển. Điều này giúp bạn tổng hợp các giá trị trên các điều kiện cụ thể