Excel lấy ký tự thứ 3
|
Thông thường, chúng tôi xử lý dữ liệu trong đó các số nằm trong văn bản và chúng tôi cần trích xuất chúng. Sáng nay tôi có nhiệm vụ như vậy. Như bạn đã biết, gần đây chúng tôi đã thực hiện một cuộc khảo sát hỏi mức lương bạn kiếm được. Chúng tôi đã có 1800 phản hồi cho đến nay. Mình lấy số liệu ra excel để phân tích. Và bất ngờ. những con số là một mớ hỗn độn. Đây là một mẫu dữ liệu Show
Bây giờ, làm cách nào để trích xuất số tiền lương từ đây mà không cần nhập các giá trị? Suy nghĩ đầu tiên của tôi là viết một hàm do người dùng xác định để trích xuất số từ văn bản. Nhưng tôi thường né tránh VBA. Vì vậy, tôi muốn xem liệu có cách tiếp cận dựa trên công thức nào để trích xuất số từ văn bản không Sử dụng công thức để trích xuất số từ văn bảnĐể trích xuất số từ một văn bản, chúng ta cần biết 2 điều
Ví dụ: trong văn bản US $ 31330. 00 số bắt đầu từ chữ cái thứ 6 và có độ dài là 8. Vì vậy, nếu chúng ta có thể viết công thức để lấy 1 & 2, thì chúng ta có thể kết hợp chúng trong công thức MID để trích xuất số từ văn bản Tìm vị trí bắt đầu của số trong văn bảnĐể tìm vị trí bắt đầu, chúng ta cần tìm ký tự đầu tiên là một số (0 đến 9). Nói cách khác, nếu chúng ta có thể tìm thấy các vị trí từ 0 đến 9 bên trong văn bản đã cho, thì vị trí tối thiểu của tất cả các vị trí đó sẽ là vị trí bắt đầu Có vẻ phức tạp?. ? Giả sử văn bản ở dạng A1 và phạm vi lstNumbers chứa từ 0 đến 9, công thức bên dưới tìm vị trí bắt đầu {=MIN(IFERROR(FIND(lstNumbers,A1),””))} Bạn cần nhập mảng đó (CTRL+SHIFT+Enter) Làm thế nào công thức này hoạt động? Phần TÌM (lstNumbers, A1). Phần này tìm vị trí của mỗi số từ 0 đến 9 trong văn bản ở A1. Nếu tìm thấy một trận đấu, vị trí được trả lại. Khác chúng tôi nhận được một lỗi. Cho HOA KỲ $31330. 00 các giá trị sẽ là, {10;7;#VALUE. ;6;#VALUE. ;#GIÁ TRỊ. ;#GIÁ TRỊ. ;#GIÁ TRỊ. ;#GIÁ TRỊ. ;#GIÁ TRỊ. } Có nghĩa là, 0 xảy ra ở vị trí thứ 10, 1 xảy ra ở vị trí thứ 7, 3 xảy ra ở vị trí thứ 6 và mọi thứ khác (2,4,5,6,7,8,9) không xảy ra trong số IFERROR(…,””) phần. Sau đó, chúng tôi thay thế các lỗi bằng khoảng trống để MIN có thể phát huy tác dụng kỳ diệu của nó Ở giai đoạn này, kết quả sẽ là, {10;7;””;6;””;””;””;””;””;””} Có liên quan. Công thức IFERROR – cú pháp & ví dụ {=MIN(…)} phần. Điều này sẽ tìm thấy giá trị nhỏ nhất của {10;7;””;6;””;””;””;””;””;””} là 6. Vị trí bắt đầu của số trong văn bản Vì tìm nhiều mục nên cần nhập công thức mảng để có kết quả chính xác Tìm độ dài của sốKhi chúng tôi tìm thấy điểm bắt đầu, tiếp theo chúng tôi cần biết độ dài của số. Có rất nhiều cách để làm điều này. Tùy thuộc vào sự đa dạng trong dữ liệu đầu vào của bạn, bạn có thể chọn một kỹ thuật hoạt động tốt nhất Cách tiếp cận 1 – đếm số chữ số trong văn bảnCách tiếp cận đầu tiên của tôi là đếm số chữ số trong văn bản và sử dụng nó làm độ dài. Đối với điều này, chúng ta có thể chia văn bản thành các ký tự riêng lẻ và sau đó xem liệu mỗi ký tự đó có phải là một số hay không Giả sử văn bản ở trong A1, số chữ số trong đó là, =SUMPRODUCT(- -ISNUMBER(MID(A1,ROW($A$1. $A$200),1)+0)) MID(A1,ROW($A$1. $A$200),1) + 0 phần. Thao tác này chia văn bản trong A1 thành các ký tự riêng lẻ (giả sử độ dài tối đa là 200) rồi thêm 0 vào chúng Ở giai đoạn này, bạn có 200 giá trị, một số là số, một số khác là lỗi ISNUMBER(…) phần. Điều này kiểm tra tất cả 200 giá trị cho các số. Sau này, chúng ta sẽ có 200 giá trị true hoặc false — ISNUMBER (…) phần. Điều này chuyển đổi các giá trị đúng, sai thành 0 và 1. (bằng cách phủ định hai lần, Excel sẽ chuyển đổi giá trị boolean thành số tương đương) phần TÓM TẮT(…). Điều này cuối cùng tổng hợp tất cả các số 1, do đó cho chúng ta số chữ số trong văn bản Nó có hoạt động không?Mặc dù phương pháp này hoạt động tốt đối với một số con số, nhưng nó không thành công trong các trường hợp khác. Ví dụ: một văn bản như US $ 31330. 00 có phần số gồm 8 ký tự (31330. 00) trong đó công thức của chúng ta sẽ cho biết độ dài là 7 (vì dấu thập phân. không phải là một số và do đó ISNUMBER() sẽ trả về giá trị sai cho điều đó) Vì vậy, tôi đã phải chuyển sang cách tiếp cận tiếp theo Cách tiếp cận 2 – đếm số chữ số, dấu phẩy và dấu thập phân trong văn bảnCách tiếp theo là đếm không chỉ các số mà còn cả dấu phẩy & dấu thập phân trong văn bản. Đối với điều này, đầu tiên tôi đặt tất cả các chữ số (0 đến 9) và dấu phẩy & dấu thập phân trong một phạm vi được gọi là lstDigits Công thức dưới đây đếm có bao nhiêu lstDigits trong văn bản trong A1 =SUMPRODUCT(COUNTIF(lstDigits,MID(A1,ROW($A$1. $A$200),1))) Phần COUNTIF(lstDigits, MID(…)). Điều này kiểm tra xem mỗi ký tự trong số 200 ký tự xuất hiện bao nhiêu lần trong lstDigits Đây sẽ là một mảng đếm. Ví dụ: {0;0;0;0;0;1;1;1;1;1;1;1;1;…} với giá 31330 đô la Mỹ. 00, chỉ ra rằng 5 số đầu tiên không có trong lstDigits và sau đó chúng tôi có 8 trong lstDigits phần TÓM TẮT(…). chỉ tính tổng tất cả các số, do đó chúng ta có độ dài là 8 Có liên quan. Công thức TÓM TẮT – ví dụ & giải thích Trích xuất số từ văn bảnKhi chúng tôi có vị trí bắt đầu của số & độ dài của nó, chúng tôi có thể kết hợp chúng trong công thức MID để trích xuất số. Đây là kết quả cho tập dữ liệu mẫu của chúng tôi Như bạn có thể thấy, phương pháp này hoạt động tốt, nhưng không thành công trong một số trường hợp như,
May mắn thay, trong bộ dữ liệu của tôi, chúng tôi chỉ gặp một vài sự cố như thế này. Vì vậy, tôi đã quyết định điều chỉnh chúng theo cách thủ công hơn là tìm ra công thức thậm chí còn phức tạp hơn Sử dụng Macro để trích xuất số từ văn bảnNhư bạn có thể đoán, chúng ta có thể sử dụng một macro đơn giản (hoặc UDF) để trích xuất các số từ một văn bản nhất định. Chúng ta sẽ học cách làm điều này vào tuần tới Tải xuống Sổ làm việc Ví dụNhấp vào đây để tải xuống bảng tính ví dụ với tất cả các công thức này. Kiểm tra các công thức để hiểu cách bạn có thể trích xuất các số từ văn bản trong Excel How do you Extract numbers from Text?Often I deal with data like this. Tôi sử dụng kết hợp các kỹ thuật. Ngoài cái được đề cập ở trên, tôi cũng sử dụng,
What about you? How do you extract numbers from text? What are your favorite techniques? Please share using comments Tips on cleaning data using ExcelIf you use Excel to clean data, go thru these articles to learn some powerful techniques
Share this tip with your colleagues Get FREE Excel + Power BI TipsSimple, fun and useful emails, once per week
PrevPreviousUse MAX to find latest date in a list [Quick tip] NextJoin our Financial Modeling Class before fee hike [Quick update]Next Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME Want an AWESOME Excel School made me great at work 5/5 – Brenda FREE Goodies for you Danh sách 100 công thức Excel Từ đơn giản đến phức tạp, có một công thức cho mọi dịp. Kiểm tra danh sách ngay bây giờ 20 mẫu Excel Lịch, hóa đơn, trình theo dõi và nhiều hơn nữa. Tất cả miễn phí, vui vẻ và tuyệt vời  13 kỹ năng Pivot Table nâng cao Power Query, Mô hình dữ liệu, DAX, Bộ lọc, Bộ cắt, Định dạng có điều kiện và biểu đồ đẹp. Tất cả đều ở đây Bắt đầu với Power BI Vẫn còn băn khoăn về Power BI? Các bài viết gần đây về Chandoo. tổ chức Bài đăng này kiểm tra việc triển khai Hàm Mandelbrot trong Excel mà không cần sử dụng mã VBA. Nó khám phá cách điều này được thực hiện bằng cách sử dụng Số thực và Số phức Mẫu Excel Lịch & Planner MIỄN PHÍ cho năm 2023Merry Christmas & Happy New Year 2023Range Lookup in Excel – How to lookup the pricing tier? [Formulas]#### in Excel cellBest of the lot
Related TipsExcel Challenges Can you split “The Hangover” expenses? [Excel Homework]Excel Howtos How to write complex Excel formulas (hint. it’s a lot like LEGO)Excel Howtos Sorting values in Olympic Medal Table style [Quick Tip]Excel Howtos How to use Date & Time values in Excel – 10 + 3 tipsExcel Howtos What is XLOOKUP? 13 formula examples to really understand itExcel Howtos Two-level Data Validation [Excel Trick]65 Responses to “Extracting numbers from text in excel [Case study]”
|
