+ Reply to Thread
Results 1 to 9 of 9

Extract Numbers from String

Hybrid View

  1. 01-23-2017, 05:03 PM #1
    SanchoPanza1
    SanchoPanza1 is offline
    Registered User
    Join Date
    02-09-2016
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Extract Numbers from String

    So, I am trying to extract numbers from a Measurements column for product that we sell. I want to extract all numbers from the Left up until text starts and I want to extract all numbers from Right until text starts.

    I started with the following Formulas as follows:

    =LEFT(T2,LEN(T2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},T2&"0123456789"))-1)
    &
    =RIGHT(T2,LEN(T2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},T2&"0123456789"))-1)

    However, those formulas work for some fields & not for others. I have attached the file I have been working on.

    Any help would be greatly appreciated.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-23-2017, 05:18 PM #2
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: Extract Numbers from String

    B2: Copy Down
    =-LOOKUP(1,-(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))))&"**0"))

    C2: Copy Down
    =-LOOKUP(1,-(MID(SUBSTITUTE(A3,B3,"",1),MIN(FIND({1,2,3,4,5,6,7,8,9,0},SUBSTITUTE(A3,B3,"",1)&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(A3,B3,"",1)))))&"**0"))

    See attached.

    Formula based on concept found in link.
    https://excelxor.com/2015/06/29/simu...ing/#more-4923

    EDIT: I assumed text could change and isn't a constant. If it's constant go with AlKey's formula.
    Attached Files Attached Files
    Last edited by CK76; 01-23-2017 at 05:32 PM.
    Reply With QuoteRegister To Reply
  3. 01-24-2017, 10:55 AM #3
    SanchoPanza1
    SanchoPanza1 is offline
    Registered User
    Join Date
    02-09-2016
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Extract Numbers from String

    Thank you it worked
    Reply With QuoteRegister To Reply
  4. 01-23-2017, 05:20 PM #4
    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: Extract Numbers from String

    Enter formula in B2 and drag it to C2 and then down
    Formula: copy to clipboard 
    =--MID(SUBSTITUTE("X"&$A2,"X",REPT(" ",50)),50*COLUMNS($A:A),50)

    v A B C
    1 Measurements 1st Last
    2 5X16 5 16
    3 5X16 5 16
    4 5X16 5 16
    5 5X16 5 16
    6 5X16 5 16
    7 5X16 5 16
    8 5X16 5 16
    9 5X16 5 16
    10 5X16 5 16
    11 5X16 5 16
    12 5X16 5 16
    13 5X16 5 16
    14 5X16 5 16
    15 5X16 5 16
    16 6X6 6 6
    17 6X6 6 6
    18 6X8 6 8
    19 6X8 6 8
    20 6X8 6 8
    Last edited by AlKey; 01-23-2017 at 06:10 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
  5. 01-24-2017, 10:56 AM #5
    SanchoPanza1
    SanchoPanza1 is offline
    Registered User
    Join Date
    02-09-2016
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Extract Numbers from String

    I can't believe how many variations solve this. Thank you
    Reply With QuoteRegister To Reply
  6. 01-23-2017, 05:33 PM #6
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Extract Numbers from String

    Hi,

    Why not just
    Formula: copy to clipboard 
    =LEFT(A2,SEARCH("x",A2)-1)

    and
    Formula: copy to clipboard 
    =RIGHT(A2,LEN(A2)-SEARCH("X",A2))
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  7. 01-24-2017, 10:57 AM #7
    SanchoPanza1
    SanchoPanza1 is offline
    Registered User
    Join Date
    02-09-2016
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Extract Numbers from String

    Thank you....this also works
    Reply With QuoteRegister To Reply
  8. 01-24-2017, 04:28 PM #8
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Extract Numbers from String

    My pleasure and thanks for the rep.
    Reply With QuoteRegister To Reply
  9. 01-23-2017, 06:03 PM #9
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Extract Numbers from String

    Enter in B2 and fill down and across
    Formula: copy to clipboard 
    =IFERROR(IF(B$1="1st",LEFT($A2,FIND("X",$A2)-1),MID($A2,FIND("X",$A2)+1,10)),"")

    If there is a possibility of lower case Xs this will take care of that possibility
    Formula: copy to clipboard 
    =IFERROR(IF(B$1="1st",LEFT($A2,SEARCH("X",$A2)-1),MID($A2,SEARCH("X",$A2)+1,10)),"")
    Last edited by newdoverman; 01-23-2017 at 06:19 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    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. [SOLVED] Extract numbers only from string
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-04-2015, 06:43 AM
  2. How to extract numbers from a string
    By Ian99099 in forum Excel General
    Replies: 4
    Last Post: 02-28-2015, 11:22 AM
  3. Extract numbers from a string
    By keepers123 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-17-2013, 09:02 AM
  4. Extract numbers from a string
    By mkhan123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-03-2011, 03:22 PM
  5. Extract last 4 numbers from string
    By DarkBeer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2010, 11:16 AM
  6. Extract numbers from a string
    By andyiain in forum Excel General
    Replies: 6
    Last Post: 09-06-2006, 07:10 AM
  7. Extract numbers from a string ?
    By Jello in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-03-2005, 07: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1