Share via

how to extract 9 digit numerical value from a sentence ?

Anonymous
2024-09-09T04:47:17+00:00

I want to extract 9 digit numerical value from a sentence,

Sample : S.No. 787324320, Key No. WRUU7CXR2

Sentence contains multiple spaces and sometimes no text.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-09-09T08:43:44+00:00

    For example:

    =LET(c, TRIM(MID(A1, SEQUENCE(LEN(A1)-8), 9)), XLOOKUP(1, ISNUMBER(--c)*(LEN(c)=9), c))

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-09-09T09:56:24+00:00

    Dear Ashu3639,

    Thanks for visiting Microsoft Community.  I am happy to help you.

    I understand that you want to extract numbers from a sentence in Excel.

    You can use a dynamic array formula to extract all the numbers. Assuming the sentence is in cell A1, you can use the following formula:

    =TEXTJOIN(", ", TRUE, IF(ISNUMBER(--MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)), MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1), ""))

    Step:

    • Place the sentence in cell A1.
    • Enter the above formula in the other cells.
    • Press Enter and this formula will extract all the numbers and display them separated by commas.

    Best regards

    Miyeon.S - MSFT |Microsoft Community Support Specialist

    1 person found this answer helpful.
    0 comments
  2. Vijay A. Verma 104.8K Reputation points Volunteer Moderator
    2024-09-20T08:51:04+00:00

    If somebody is looking for REGEX solution (the person needs to be on Insider Beta Channel), here is the solution

    =REGEXEXTRACT(A2,"\b\d{9}\b|$")

    0 comments
  3. Anonymous
    2024-09-20T06:16:43+00:00

    Dear Ashu3639,

    It's been a long time since I've heard back from you.

    Has your problem been resolved? Please let me know if the formula I provided works.

    Looking forward to your reply.

    Miyeon.S - MSFT |Microsoft Community Support Specialist

    0 comments
  4. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-09-09T23:38:56+00:00

    Hi,

    In cell B2, enter this formula

    =LET(s,1*TEXTSPLIT(A2,{" ",",",".",":"}),FILTER(s,LEN(IFERROR(s,""))=9))

    Hope this helps.

    0 comments