+ Reply to Thread
Results 1 to 9 of 9

Isolating numbers from string

  1. 07-02-2014, 04:54 PM #1
    omaribookal
    omaribookal is offline
    Registered User
    Join Date
    06-30-2014
    Location
    Grafton Mass
    MS-Off Ver
    2010
    Posts
    20

    Isolating numbers from string

    This has been bugging me for a while now and I tried using an extract function to achieve what I want but there are just too much data for it to work properly. I have file names with part numbers and not all the file names are the same length, some are alpha numberic and some are just plain weird but they all have a 3 digit number i need to extract. I want to be able to extract the the three digit number in each file name and place it into the cell next to it, preferable on the same worksheet.

    Here are some examples

    11006_11007_593 STRIKEOVER_NC.ppt = 593
    11000_01_907_A2.ppt = 907
    10979_064.ppt = 064
    10948_49_50_907_A2.ppt = 907

    There are 2000 of these file names, about 50 variations of that 3 digit number.

    any suggestions?
    Reply With QuoteRegister To Reply
  2. 07-02-2014, 05:11 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,308

    Re: Isolating numbers from string

    Given the examples provided, I'd use the Split function with underscore as the delimiter. I'd then loop through the array entries to find and return the first three digit numeric string.

    Can't do it now as I'm on holiday with no computer to hand. But that'a the basic principle.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 07-02-2014, 05:23 PM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Isolating numbers from string

    I thought that I'd try a "BRUTE FORCE" solution, to come up with a monster equation....

    Try this for formula fear...

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by Glenn Kennedy; 07-02-2014 at 05:46 PM.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  4. 07-02-2014, 05:45 PM #4
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Isolating numbers from string

    Hi Omar,

    I put the number in a message box, what do you really want to do with it?

    Please Login or Register  to view this content.
    *It works, one at a time on the active cell
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  5. 07-02-2014, 06:32 PM #5
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Isolating numbers from string

    glenn you can shorten that a bit
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    or maybe this array entered formula=MID(A1,MATCH(1,IF(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))>=65,IF(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))<90,1,0)),0)-4,3)
    Last edited by martindwilson; 07-02-2014 at 10:36 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  6. 07-02-2014, 09:50 PM #6
    lecxe
    lecxe is offline
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: Isolating numbers from string

    Hi
    Welcome to the board

    This solution extracts the first group of 3 digits preceded and followed by non-digits:

    Ex.:
    11000_01_907_A2.ppt = 907
    11000_A01_907_A2.ppt = 907
    11000_AB-CD_907_1234.ppt = 907
    11000_012_9074_A2.ppt = 012

    Try:

    =MID(A1,MATCH(0,MMULT(ABS(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1)-4))+{0,1,2,3,4},1))-{0,1,1,1,0}),{1;1;1;1;1}),0)+1,3)

    This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.
    Reply With QuoteRegister To Reply
  7. 07-02-2014, 11:23 PM #7
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Isolating numbers from string

    Or this

    =MID(SUBSTITUTE(A1," ","_"),SEARCH("_???_",SUBSTITUTE(A1," ","_"))+1,3)

    A
    B
    1
    		 11006_11007_593 STRIKEOVER_NC.ppt 593
    2
    		 11000_01_907_A2.ppt 907
    3
    		 10948_49_50_907_A2.ppt 907
    Last edited by AlKey; 07-02-2014 at 11:26 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  8. 07-03-2014, 02:49 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Isolating numbers from string

    Omaribookall,

    Is there always only 1 3-digit number in each string, or can there be two or more?
    Reply With QuoteRegister To Reply
  9. 07-03-2014, 03:08 AM #9
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Isolating numbers from string

    You may also try this code to achieve this....
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards
    sktneer

    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Isolating numbers in varying length alphanumeric string with or without a dash
    By Rysky75L9 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-14-2012, 02:05 PM
  2. change a cell with a letter and string of numbers to a word and a string of numbers
    By dlamin130 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-23-2012, 04:58 PM
  3. Isolating and reformatting a string of text within a cell
    By adelcap in forum Excel General
    Replies: 2
    Last Post: 04-26-2012, 05:35 AM
  4. isolating addess components in a string
    By Watchdog in forum Excel General
    Replies: 1
    Last Post: 04-29-2008, 04:20 PM
  5. [SOLVED] isolating a string containing a % symbol
    By bobadigilatis in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-27-2006, 07:35 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1