Home-theater-designers
Microsoft Excel rất tốt khi làm việc với cả số và văn bản --- nhưng nếu bạn đang sử dụng cả hai trong cùng một ô, bạn có thể gặp một số khó khăn. May mắn thay, bạn có thể trích xuất số hoặc văn bản từ các ô để làm việc với dữ liệu của mình hiệu quả hơn. Chúng tôi trình bày một số tùy chọn, tùy thuộc vào định dạng dữ liệu của bạn hiện đang ở.
Số Excel được định dạng dưới dạng văn bản
Đây là một tình huống phổ biến, và --- may mắn thay --- rất dễ giải quyết. Đôi khi, các ô chỉ chứa số được gắn nhãn hoặc định dạng văn bản không chính xác, ngăn Microsoft Excel sử dụng chúng trong các hoạt động.
Bạn có thể thấy trong hình ảnh bên dưới rằng các ô trong cột A được định dạng dưới dạng văn bản, như được chỉ ra bởi hộp định dạng số. Bạn cũng có thể thấy một lá cờ màu xanh lá cây ở góc trên cùng bên trái của mỗi ô.
Chuyển văn bản thành số trong Excel
Nếu bạn thấy cờ màu xanh lá cây ở góc trên cùng bên trái, hãy chọn một hoặc nhiều ô, nhấp vào dấu hiệu cảnh báo và chọn Chuyển đổi thành số .
Nếu không, hãy chọn các ô và trong menu Định dạng Số trong Ruy-băng, hãy chọn giá trị mặc định Con số Lựa chọn.
Nếu bạn cần các tùy chọn chi tiết hơn, hãy nhấp chuột phải vào / các ô được đánh dấu và chọn Định dạng ô , sẽ mở menu tương ứng. Tại đây, bạn có thể tùy chỉnh định dạng số và thêm hoặc xóa số thập phân, thêm dấu phân tách 1.000 hoặc quản lý số âm.
Rõ ràng, bạn cũng có thể sử dụng các tùy chọn Ruy-băng hoặc Định dạng Ô được nêu ở trên để chuyển đổi một số thành văn bản hoặc văn bản thành đơn vị tiền tệ, thời gian hoặc bất kỳ định dạng nào khác mà bạn muốn.
Áp dụng định dạng số với Excel's Paste Special
Để phương pháp này hoạt động, bạn sẽ cần nhập một số (bất kỳ số nào) vào một ô; điều quan trọng là ô này cũng được định dạng dưới dạng số. Sao chép ô đó. Bây giờ, hãy chọn tất cả các ô mà bạn muốn chuyển đổi sang định dạng số, hãy chuyển đến Trang chủ> Dán> Dán đặc biệt , lựa chọn Định dạng để chỉ dán định dạng của ô bạn đã sao chép ban đầu, sau đó bấm VÂNG .
Thao tác này áp dụng định dạng của ô bạn đã sao chép cho tất cả các ô đã chọn, ngay cả các ô văn bản.
Trích xuất số hoặc văn bản từ các ô định dạng hỗn hợp
Bây giờ chúng ta đến phần khó: lấy số ra khỏi các ô chứa nhiều định dạng đầu vào. Nếu bạn có một số và một đơn vị (như '7 xẻng', như chúng tôi có bên dưới), bạn sẽ gặp phải vấn đề này. Để giải quyết vấn đề này, chúng ta sẽ xem xét một số cách khác nhau để chia các ô thành số và văn bản, cho phép bạn làm việc với từng ô riêng lẻ.
Tách số khỏi văn bản
Nếu bạn có nhiều ô chứa hỗn hợp số và văn bản hoặc bội số của cả hai, việc tách chúng theo cách thủ công có thể mất rất nhiều thời gian. Để vượt qua quá trình nhanh hơn, bạn có thể sử dụng Microsoft Excel's Văn bản thành cột hàm số.
Chọn các ô mà bạn muốn chuyển đổi, đi tới Dữ liệu> Văn bản thành cột và sử dụng trình hướng dẫn để đảm bảo các ô xuất hiện một cách chính xác. Đối với hầu hết các phần, bạn chỉ cần nhấp vào Kế tiếp và Kết thúc , nhưng hãy đảm bảo rằng bạn chọn một dấu phân cách phù hợp; trong ví dụ này, một dấu phẩy.
Nếu bạn chỉ có một và hai chữ số, Chiều rộng cố định tùy chọn cũng có thể hữu ích, vì nó sẽ chỉ tách ra hai hoặc ba ký tự đầu tiên của ô. Bạn thậm chí có thể tạo một số phần chia theo cách đó.
Ghi chú: Các ô được định dạng dưới dạng văn bản sẽ không phải tự động xuất hiện với định dạng số (hoặc ngược lại), có nghĩa là bạn vẫn có thể phải chuyển đổi các ô này như được mô tả ở trên.
Trích xuất một số hoặc văn bản từ một chuỗi phân cách
Phương pháp này hơi rườm rà, nhưng hoạt động rất tốt trên các tập dữ liệu nhỏ. Điều chúng tôi giả định ở đây là một khoảng trắng phân tách số và văn bản, mặc dù phương pháp này cũng hoạt động với bất kỳ dấu phân cách nào khác.
Hàm chính mà chúng ta sẽ sử dụng ở đây là LEFT, hàm này trả về các ký tự ngoài cùng bên trái từ một ô. Như bạn có thể thấy trong tập dữ liệu của chúng tôi ở trên, chúng tôi có các ô có số một, hai và ba ký tự, vì vậy chúng tôi sẽ cần trả về một, hai hoặc ba ký tự ngoài cùng bên trái từ các ô. Bằng cách kết hợp LEFT với Chức năng tìm kiếm , chúng ta có thể trả lại mọi thứ ở bên trái của khoảng trắng. Đây là chức năng:
= LEFT (A1, TÌM KIẾM ('', A1, 1))
Thao tác này sẽ trả lại mọi thứ về bên trái của khoảng trắng. Sử dụng điều khiển điền để áp dụng công thức cho các ô còn lại, đây là những gì chúng tôi nhận được (bạn có thể xem công thức trong thanh chức năng ở đầu hình ảnh):
Như bạn có thể thấy, bây giờ chúng tôi có tất cả các số bị cô lập, vì vậy chúng tôi có thể thao túng chúng. Bạn cũng muốn cách ly văn bản? Chúng ta có thể sử dụng hàm RIGHT theo cách tương tự:
= RIGHT (A1, LEN (A1) -SEARCH ('', A1, 1))
Điều này trả về X ký tự từ phía bên phải của ô, trong đó x là tổng chiều dài của ô trừ đi số ký tự ở bên trái của khoảng trắng.
Bây giờ bạn cũng có thể thao tác với văn bản. Bạn muốn kết hợp chúng lại? Chỉ cần sử dụng hàm CONCATENATE với tất cả các ô làm đầu vào:
= CONCATENATE (E1, F1)
Rõ ràng, phương pháp này hoạt động tốt nhất nếu bạn chỉ có số và đơn vị, và không có gì khác. Nếu bạn có các định dạng ô khác, bạn có thể phải sáng tạo với các công thức để mọi thứ hoạt động đúng. Nếu bạn có một tập dữ liệu khổng lồ, sẽ rất đáng để dành thời gian để tìm ra công thức!
Trích xuất một số từ một đầu của chuỗi liên tục
Bây giờ điều gì sẽ xảy ra nếu không có dấu phân tách tách số và văn bản của bạn?
Nếu bạn trích xuất số từ bên trái hoặc bên phải của chuỗi , bạn có thể sử dụng một biến thể của công thức LEFT hoặc RIGHT được thảo luận ở trên:
= LEFT (A1, SUM (LEN (A1) -LEN (SUBSTITUTE (A1, {'0', '1', '2', '3', '4', '5', '6', '7' ' , '8', '9'}, ''))))
= RIGHT (A1, SUM (LEN (A1) -LEN (SUBSTITUTE (A1, {'0', '1', '2', '3', '4', '5', '6', '7' ' , '8', '9'}, ''))))
Điều này sẽ trả về tất cả các số từ bên trái hoặc bên phải của chuỗi.
Nếu bạn trích xuất số từ bên phải của chuỗi , bạn cũng có thể sử dụng quy trình hai bước. Đầu tiên, xác định vị trí của chữ số đầu tiên của bạn trong chuỗi bằng cách sử dụng hàm MIN. Sau đó, bạn có thể cung cấp thông tin đó vào một biến thể của công thức RIGHT, để tách các số của bạn khỏi văn bản của bạn.
= MIN (TÌM KIẾM ({0,1,2,3,4,5,6,7,8,9}, A1 & '0123456789'))
nút chuột trái không hoạt động windows 10
= RIGHT (A1, LEN (A1) -B1 + 1)
Ghi chú: Khi bạn sử dụng các công thức này, hãy nhớ rằng bạn có thể phải điều chỉnh các ký tự cột và số ô.
Trích xuất các số từ cả hai đầu của một chuỗi liên tục
Với các chiến lược ở trên, bạn sẽ có thể trích xuất số hoặc văn bản từ hầu hết các ô có định dạng hỗn hợp đang gây rắc rối cho bạn. Ngay cả khi không, bạn có thể kết hợp chúng với một số hàm văn bản mạnh mẽ có trong Microsoft Excel để có được các ký tự bạn đang tìm kiếm. Tuy nhiên, có một số tình huống phức tạp hơn nhiều đòi hỏi các giải pháp phức tạp hơn.
Ví dụ, tôi đã tìm thấy một bài đăng trên diễn đàn nơi ai đó muốn trích xuất các số từ một chuỗi như '45t * & 65 /', để anh ta kết thúc bằng '4565.' Một người đăng khác đã đưa ra công thức sau đây như một cách để làm điều đó:
= SUMPRODUCT (MID (0 & A1, LARGE (INDEX (ISNUMBER (- MID (A1, ROW ($ 1: $ 25), 1)) *
ROW ($ 1: $ 25), 0), ROW ($ 1: $ 25)) + 1,1) * 10 ^ ROW ($ 1: $ 25) / 10)
Thành thật mà nói, tôi không biết nó hoạt động như thế nào. Nhưng theo bài đăng trên diễn đàn, nó sẽ lấy các con số ra khỏi một chuỗi số phức tạp và các ký tự khác. Vấn đề là, với đủ thời gian, sự kiên nhẫn và nỗ lực, bạn có thể trích xuất các con số và văn bản từ bất kỳ thứ gì! Bạn chỉ cần tìm các nguồn tài nguyên phù hợp.
Sau một số thủ thuật Excel khác? Đây là cách sao chép công thức trong Excel .
Đăng lại Đăng lại tiếng riu ríu E-mail Cách làm sạch PC Windows của bạn bằng Command PromptNếu PC Windows của bạn sắp hết dung lượng lưu trữ, hãy dọn sạch rác bằng các tiện ích Command Prompt nhanh chóng này.
Đọc tiếp Chủ đề liên quan- Năng suất
- Bảng tính
- Microsoft Excel
Trong khi hoàn thành chương trình Tiến sĩ, Tina bắt đầu viết về công nghệ tiêu dùng vào năm 2006 và không bao giờ dừng lại. Bây giờ cũng là một biên tập viên và SEO, bạn có thể tìm thấy cô ấy trên Twitter hoặc đi bộ đường dài trên đường mòn gần đó.
Xem thêm từ Tina SieberTheo dõi bản tin của chúng tôi
Tham gia bản tin của chúng tôi để biết các mẹo công nghệ, đánh giá, sách điện tử miễn phí và các ưu đãi độc quyền!
Bấm vào đây để đăng ký