Excel lấy ký tự thứ 3

Facebook

Twitter

LinkedIn

Share this tip with your colleagues

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week

Learn & be awesome

• Tagged under advanced excel, array formulas, cleanup data, downloads, Excel Howtos, find, iferror, Learn Excel, Microsoft Excel Formulas, MIN[], sumproduct
• Category. Excel Howtos

PrevPreviousUse MAX to find latest date in a list [Quick tip]

NextJoin our Financial Modeling Class before fee hike [Quick update]Next

Welcome to Chandoo. org

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

Read my story • FREE Excel tips book

Want an AWESOME
Excel Class?

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

Best of the lot

• Excel for beginners
• Advanced Excel Skills
• Excel Dashboards
• Complete guide to Pivot Tables
• Top 10 Excel Formulas
• Excel Shortcuts
• #Awesome Budget vs. Actual Chart
• 40+ VBA Examples

Related Tips

65 Responses to “Extracting numbers from text in excel [Case study]”

1. Gregor Erbach says.

   I have learned one important lesson in my career. don't use Excel as a word processor
   Excel is very ill-suited to handling tasks like pattern matching in text [regular expressions]
   For example, one could use a Perl script [from the CMD command line] to extract numbers from the text, something like
    
   perl -n -e "m/[[\d\,\. ]*]/; print "$1\n" IN > OUT
    
   The point I am trying to make is, if text processing is a substantial part of your tasks, it is worth the effort learning a tool that is more suited to the task
    

   • Hui. says.

     I agree with what your saying about suitability of tools to task,

     but seriously perl -n -e “m/[[\d\,\. ]*]/; print “$1\n” IN > OUT

     Doesn't just roll off your tongue 

     and very few PC's would even have Perl installed

   • Chandoo says.

     Interesting point. I would have used VBA or some variation of that to clean data. But as Hui says Perl is not something many [including me] have on our computers. Plus I suck at regular expressions. I have tried long and hard to learn them, but I guess my mind is not wired to understand how they work 🙁

   • Jim nói.

     The VBA engine for regular expressions works fine in Excel and Access. I agree if you are doing a substantial amount of text cleaning then doing so exclusively in Excel is probably not the best course, but using regular expressions in Perl or any other flavor doesn't really provide any advantage over using regex in Excel. At the end of the day, regex is regex [with the exception of minor syntactic differences]

     Chandoo, a lot of people are intimidated by regex just like people are intimidated by formulas or macros. Once you learn a handful of rules and concepts, you can really do some damage with regex, and not just for data cleaning. You can validate input, parse,  transform [e. g. firstname lastname --> lastname, firstname], etc. Once you pick it up you'll see many opportunities where it's useful and sometimes superior to another approach [and sometimes not].  My brain is not wired to understand multiple nested formulas or complicated array formulas so I guess regex is just easier to me. I would add that the regular expression approach not only cleans this data, but is flexible enough to account for other variations. try it on the other records you have and see how well it performs. Either way, it's great to know there are multiple approaches to solving the problem

2. Bhavani Seetal Lal says.

   How to sign inn into chandoo. org???

3. Vijay Kumar says.

   Dear Chandoo
   Awesome, you are such a genius, how can you think like "what the excel will think"? Such ideas implementation. Very great . But lastly, i just wanted to know that in your formula =SUMPRODUCT[COUNTIF[lstDigits,MID[B4,ROW[$A$1. $A$200],1]]], why we can't use 1 on behalf of ROW[$A$1. $A$200], obviously if we will input this formula somewhere in excel, it results 1. So what is the logic to use row function instead of simple 1
   Please explain, i am eager to learn the logic of inputting or building a formulas. Can you suggest me the source like web url or books to build better formulas in our excel work
    

   • Luke M says.

     Within SUMPRODUCT, that bit of formula is actually producing an array of numbers from 1 to 200, eg
     {1,2,3,4,. 200}
     To see this in the formula, highlight that portion within the SUMPRODUCT and hit F9.  

4. Hui. says.

   In the first example above, I would store lstNumbers as a Named Formula

   lstNumbers ={0;1;2;3;4;5;6;7;8;9}

5. Cliff Beacham says.

   I do not know why you would not want to use a UDF - Here is the code for one I wrote that extracts Numbers, Letters, Commas and spaces to clean up data  - amend as you wish for ANY ASCII characters
   Function NumbersAndLettersOnly[instring] As String
   Dim StringLength As Integer ‘to hold string length
   Dim i As Integer ‘counter for loop
   Dim AsciiVal ‘to hold working character
   Dim WorkingString As String ‘to build output string
   instring = Trim[instring] ‘Drop leading & trailing spaces
   StringLength = Len[instring] ‘Count number of characters in string
   For i = 1 To StringLength ‘Loop thru each character in the string
   AsciiVal = Asc[Mid[instring, i, 1]]
   Nếu AsciiVal >= 48 Và AsciiVal = 97 Và AsciiVal = 1 Thì
   ReDim rslt[0 To allMatches. Đếm - 1]
   For i = 0 To allMatches. Đếm - 1
   kết quả [trong] = tất cả các trận đấu [i]. Giá trị
   tiếp theo tôi
   RegExFind = rslt
   Khác
   RegExFind = ""
   kết thúc nếu
   chức năng kết thúc

   ************************************************

   và cách sử dụng là

   =RegExFind[B4,"\d{2,7}[\. ]?\d{2,7}[\. \d{2}. ,0{1,2}]?"]

   Biểu thức \d{2,7}[\. ]?\d{2,7}[\. \d{2}. ,0{1,2}]? . Chỉ cần sử dụng biểu thức này. \d+ sẽ khớp với tất cả trừ hai trong số các bản ghi mẫu

   Chúc mừng,
   -Jim

6. Godsbod nói.

   Giải pháp rõ ràng là tạo một bảng câu hỏi có cấu trúc hơn, với trường giá trị riêng biệt cho loại tiền và số lượng

   nhưng thách thức đang phát sinh gần như là một sản phẩm phụ xứng đáng

7. Juan Fernandez nói.

   Công thức này có thể trích xuất nhiều số
   
   http. //www. get-kỹ thuật số-trợ giúp. com/2011/11/17/tách-số-từ-một-chuỗi-văn-bản/

   Trân trọng

   • Elias nói.

     @ Juan,

     Công thức đó không hoạt động trong trường hợp này

     Trân trọng

8. Carl nói.

   Tôi nghĩ vấn đề lớn hơn là vấn đề được tạo ra bằng cách tách các số ra khỏi các chữ cái. Mục tiêu là để tìm ra những gì mọi người đang kiếm được trong công việc excel của họ và hiện tại chúng tôi có các con số nhưng không có tham chiếu đến đơn vị tiền tệ, vì vậy bạn không thể tính trung bình những con số này để tìm ra số tiền trung bình mà một nhân viên thông thạo excel kiếm được. Chúng tôi phải quay lại và xác định loại tiền tệ và tính tỷ giá hối đoái.  I think @Godsbod had it right. Bài học quan trọng hơn là suy nghĩ thông qua bảng câu hỏi để có được kết quả dễ quản lý hơn, đó là điều chúng tôi thực sự muốn

9. Trích xuất số từ văn bản bằng Excel VBA - Cách thực hiện?. Chandoo. org - Học Microsoft Excel trực tuyến nói.

   [. ] tuần chúng ta đã thảo luận về cách trích xuất số từ văn bản trong Excel bằng công thức. Trong các bình luận, khá nhiều người cho rằng nên sử dụng VBA [Macros] để [. ]

10. r nói.

    @Elias
    công thức của bạn rất đẹp. Nhưng tôi không hiểu tại sao phải sử dụng ma trận hằng số
    sử dụng ROW[1. 10]-1 và 1234567890. tốt hơn
    Ngoài ra, công thức của bạn không hoạt động trong các trường hợp tương tự như thế này ABCD 1234 EFGH. Bạn cần thực hiện những thay đổi nhỏ. Trong khi đó, tôi đề xuất cái này ngắn hơn và cũng hoạt động trong những trường hợp này
    =LEFT[REPLACE[A1,1,MIN[FIND[ROW[1. 10]-1,A1&57321^2]]-1,],FIND[" ",REPLACE[A1,1,MIN[FIND[ROW[1. 10]-1,A1&57321^2]]-1,]&" "]-1]
    Ah. 57321 là một số pandigital 🙂
    Trân trọng
    r
     
     
     

    • Elias nói.

      @r rất vui được gặp bạn xung quanh
      Ngoài số lượng ký tự, tôi không thấy bất kỳ lợi thế nào khi sử dụng tùy chọn hàng trên mảng hằng số. Tuy nhiên, việc sử dụng các hằng số sẽ tránh việc sử dụng nhập mảng và các công thức không bị lộn xộn nếu bạn chèn hoặc xóa các hàng
      Trân trọng

11. r nói.

    @elias
    bạn nói đúng nhưng tôi không thể nhìn vào công thức
    chỉ là yếu tố thẩm mỹ. không thể nói "đẹp" bởi một ma trận hằng số
    Trân trọng
    r
     

12. r nói.

    @elias
    tôi đã thay đổi công thức của bạn. vì vậy nó hoạt động với ABCD 1234 EFGH
    =--GIỮA[TRÁI[A1,TÌM[" ",A1&" ",MIN[TÌM[ROW[1. 10]-1,A1&57321^2]]]-1],MIN[TÌM[ROW[1]. 10]-1,A1&57321^2]],20]
    bạn nghĩ sao?
    Trân trọng
    r
     

    • Elias nói.

      @r

      Tôi thích cái đó, nhưng nó không hoạt động với những trường hợp như thế này ABCD12345EFGH. Vì vậy, những gì về cái này?

      =--TRA CỨU[9. 9E+307,--TRÁI[MID[A1,MIN[TÌM[ROW[1. 10]-1,A1&57321^2]],255], HÀNG[1. 255]]]

      Trân trọng

      • Jim nói.

        Giải pháp RegEx của tôi ở trên xử lý cả ABCD 1234 EFGH và ABCD12345EFGH

        Chúc mừng,
        -Jim

13. r nói.

    ồ @elias. tuyệt vời. tôi thích nó
    và, tại sao không như vậy
    =-LOOKUP[0,-LEFT[MID[A1,MIN[FIND[ROW[1. 10]-1,A1&57321^2]],255], HÀNG[1. 255]]]
    🙂
    r
     

14. r nói.

    ufff. vì thế
    =-LOOKUP[0,-LEFT[MID[A1,MIN[FIND[ROW[1. 10]-1,A1&57321^2]],255], HÀNG[1. 255]]]

    • Elias nói.

      @r
      Vâng. Tôi thích cách sử dụng dấu âm ở 2 vị trí khác nhau

      Trân trọng

    • bẽn lẽn nói.

      Tuyệt vời. Tôi đã có thể sử dụng công thức này để trích xuất số đầu tiên từ danh pháp HGVS [xã hội biến đổi bộ gen người]

      c. 247_248delCG 247

      p. Glam789_Arg790del 789

      các c. hoặc p. đôi khi bị bỏ qua và các số có thể có độ dài thay đổi [1-12 chữ số]

      Tôi muốn hiểu cách thức hoạt động của công thức này. Về cơ bản tôi biết tra cứu, trái, giữa, phải và tìm nhưng điều này là trên đầu của tôi

      Cảm ơn bạn

15. Trích xuất ngày tháng từ văn bản - Cách viết Công thức Excel?. Chandoo. org - Học Microsoft Excel trực tuyến nói.

    [. ] Trích xuất số từ văn bản bằng công thức [. ]

16. joel nói.

    Ông. chandoo,
    xin nhờ bạn giúp đỡ
    Tôi muốn có mã cho Excel 2010, để liệt kê trên một trang tính tất cả các macro từ sổ làm việc excel
    làm ơn, hãy giúp tôi trong việc này

    Cảm ơn rât nhiều

17. Raj nói.

    Tôi muốn phân tách thành từng cột như 25 PF 105 E01 XXXX, từ các ví dụ bên dưới, vui lòng trợ giúp hoặc viết thư srinivasmr@rediffmail. com

    25PF105E01XXXX

    100SP105E02XXXX

18. Jim nói.

    Chào Raj,

    Sử dụng chức năng RegExReplace tôi đã đăng ở trên và sau đó sử dụng chức năng này

    =RegExReplace[A1,"[^\d{2,3}][\w{2}][\d{3}][\w{3}[\w{4}]]","$1 $2 $3

    nơi văn bản bạn muốn phân tích nằm trong ô A1.  

    Và kết quả là

    25 PF 105 XXXX
    100 SP 105 XXXX

    Nó đưa ra các giả định sau

    - Phần đầu ít nhất là 2 nhưng nhiều hơn 3 chữ số
    - Phần thứ hai là 2 nhân vật
    - Phần thứ ba gồm ba chữ số
    - Phần thứ tư gồm 4 ký tự chữ và số

    Chúc mừng,
    -Jim
            

    • rao nói.

      Xin chào

      bạn có thể cho tôi biết làm thế nào để tìm hiểu regex cho tôi biết các trang web để tìm hiểu cho tôi

      id thư của tôi. raoexcel@gmail. com

19. Rahim Zulfiqar Ali nói.

    =IF[ISERROR[MID[B11,FIND["/",B11,1]-2,10]],"",[MID[B11,FIND["/",B11,1]-2,10]]

20. Santoshi nói.

    Xin chào,

    Tôi gặp sự cố khi có một chuỗi số và chữ cái được phân tách bằng dấu phẩy. [Q123,125-129,QA123,127-129]

    Tôi muốn mở rộng các số bên trong dấu gạch nối để nó trở thành
    [Q123,125,126,127,128,129,QA123,127,128,129]

    Điều này có thể hoàn thành chỉ bằng các công thức không

21. Vijay Verma nói.

    Kiểm tra cái này. Đây có vẻ là điều đúng đắn để làm
    http. //www. diễn đàn excel. com/excel-formulas-and-functions/653043-extracting-numbers-from-alphanumeric-strings. html
    Điều này nói như sau -
    Thử cái này

    A1. abc123def456ghi789

    Đầu tiên, tạo một công thức được đặt tên
    Tên trong sổ làm việc. tuần tự
    đề cập đến. =ROW[INDEX[$1. $65536,1,1]. CHỈ SỐ[$1. $65536,255,1]]

    CÔNG THỨC Mảng này loại bỏ TẤT CẢ các số không phải là số khỏi một chuỗi
    B1. =SUM[IF[ISNUMBER[1/[MID[A1,seq,1]+1]],MID[A1,seq,1]*10^MMULT[-[seq