+ Reply to Thread
Results 1 to 5 of 5

Extract numbers from a the left and to the right of a string

  1. #1
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Extract numbers from a the left and to the right of a string

    Hi,

    I could use some help with extracting two different set of numbers from a cell that contains both numbers and text. Not sure if this helps but the number on the right will have 1-6 digits, the number on the left will have a minimum will have a minimum of 3 digits with no maximum. I attached a spreadsheet of the data and the two columns that will display the result of extracting the two numbers.

    This site kind of acknowledges cells with more than one number but doesn't necessarily show how to extract either number: http://chandoo.org/wp/2012/06/19/ext...om-text-excel/
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Re: Extract numbers from a the left and to the right of a string

    I found partial the solution:
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    but when "TBD." is displayed" the "-2" takes off the last two numbers from the 4 digit number.
    Last edited by T86157; 01-08-2017 at 09:13 PM. Reason: Thought I solved but was only partial

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Extract numbers from a the left and to the right of a string

    Try these...

    Entered in C2:

    =IFERROR(--LEFT(A2,FIND(".",A2)-1),"None")

    Entered in D2:

    =IFERROR(-LOOKUP(0,-MID(A2,FIND(".",A2)+3,{1,2,3,4,5,6})),"None")

    Select C2:D2 and copy down as needed.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Re: Extract numbers from a the left and to the right of a string

    This worked extremely well in addition to my other thread request. Thanks again, Tony!

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Extract numbers from a the left and to the right of a string

    You're welcome. Thanks for the feedback!

+ 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. Is there a formula that will extract the numbers to the left of the cell
    By benji1973 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-04-2015, 10:11 AM
  2. [SOLVED] Extract numbers only from string
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-04-2015, 06:43 AM
  3. How to extract numbers from a string
    By Ian99099 in forum Excel General
    Replies: 4
    Last Post: 02-28-2015, 11:22 AM
  4. Extract numbers from a string
    By keepers123 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-17-2013, 09:02 AM
  5. [SOLVED] Sum numbers left of a character from within a string
    By BarryTSL in forum Excel General
    Replies: 14
    Last Post: 04-17-2012, 01:11 PM
  6. Extract Data to right and left of numbers
    By chl in forum Excel General
    Replies: 1
    Last Post: 03-18-2010, 02:19 PM
  7. extract numbers with specific text from right or left
    By darkhangelsk in forum Excel General
    Replies: 2
    Last Post: 08-15-2009, 02:38 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