+ Reply to Thread
Results 1 to 4 of 4

Extracting all 9 digit numbers from a string

  1. #1
    Registered User
    Join Date
    01-23-2013
    Location
    Austin TX
    MS-Off Ver
    2016
    Posts
    2

    Question Extracting all 9 digit numbers from a string

    Hello Excel experts,

    I have a column of data that may or may not contain multiple 9-digit numbers. I need to extract all of the 9-digit numbers (and only the 9-digit numbers) so that I can use them in other operations. I've been able to successfully grab the first or last, but I can't wrap my head around getting them all.

    Examples:

    TEXT Expected Output
    #157664855, #158992804 157664855,158992804
    161924305 161924305
    The bear in 087593184 has 3433 kittens 087593184
    Blocked by 162979696 162979696
    #123476548, 233059382, and #344094323 123476548,233059382,344094323

    Note the delimiter in the output isn't important, as long as there is some delimiter that I can use to further break down the results.
    Last edited by brafish; 01-14-2020 at 03:34 PM.

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,007

    Re: Extracting all 9 digit numbers from a string

    Not sure it's possible with a formula. Here's a UDF that will do it:

    Please Login or Register  to view this content.
    Usage example:

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Registered User
    Join Date
    01-23-2013
    Location
    Austin TX
    MS-Off Ver
    2016
    Posts
    2

    Re: Extracting all 9 digit numbers from a string

    Thank you WBD. You saved me a ton of time!

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Extracting all 9 digit numbers from a string

    It's not universal solution because now only for 3and less number for example but add another part is not problem.
    Array formula

    =SUBSTITUTE(TRIM(
    IFERROR(MID(A1;SMALL(IF(IFERROR((TEXT(--MID(A1;ROW($1:$99);9);REPT(0;9))=MID(A1;ROW($1:$99);9))*(TEXT(--MID(A1;ROW($1:$99);10);REPT(0;10))<>MID(A1;ROW($1:$99);10)););ROW($1:$99));1);9);"")&" "&
    IFERROR(MID(A1;SMALL(IF(IFERROR((TEXT(--MID(A1;ROW($1:$99);9);REPT(0;9))=MID(A1;ROW($1:$99);9))*(TEXT(--MID(A1;ROW($1:$99);10);REPT(0;10))<>MID(A1;ROW($1:$99);10)););ROW($1:$99));2);9);"")&" "&
    IFERROR(MID(A1;SMALL(IF(IFERROR((TEXT(--MID(A1;ROW($1:$99);9);REPT(0;9))=MID(A1;ROW($1:$99);9))*(TEXT(--MID(A1;ROW($1:$99);10);REPT(0;10))<>MID(A1;ROW($1:$99);10)););ROW($1:$99));3);9);"")
    );" ";",")
    Attached Files Attached Files
    Last edited by BMV; 01-14-2020 at 04:06 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extracting specific digit from string??
    By ciapul12 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-22-2016, 05:08 AM
  2. Replies: 8
    Last Post: 01-07-2016, 11:51 AM
  3. [SOLVED] Finding 6 digit numbers in a text string
    By Niallerz in forum Excel General
    Replies: 10
    Last Post: 11-29-2015, 06:41 PM
  4. instances of a single digit in a string of numbers
    By rockchalk313 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-19-2014, 08:32 AM
  5. Extracting a digit from set of numbers
    By st_judeu@yahoo.com in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-28-2013, 05:31 AM
  6. Retrieving 3 and 5 Digit Numbers from a Text String
    By AntiVirus2011 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-23-2012, 01:08 PM
  7. Return a digit in a string of numbers
    By W M in forum Excel General
    Replies: 5
    Last Post: 05-11-2005, 02:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1