+ Reply to Thread
Results 1 to 6 of 6

Is there a formula that will extract the numbers to the left of the cell

  1. #1
    Registered User
    Join Date
    04-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Is there a formula that will extract the numbers to the left of the cell

    Hi there,

    I have a column of data in which most of the data is useless it's just how it exports, all I need are the numbers that are to the left of the cell. Is there a formula that will extract only the numbers I need? below is what the column looks like, I've tried to upload an Excel attachment but it won't let me. Any assistance would be greatly appreciated

    Details
    90 tags for period 27/07/2015 to 24/08/2015
    182 tags for period 27/07/2015 to 23/08/2015
    50 tags for period 17/08/2015 to 24/08/2015
    795 tags for period 27/07/2015 to 24/08/2015
    140 tags for period 03/08/2015 to 16/08/2015
    50 tags for period 27/07/2015 to 02/08/2015
    55 tags for period 27/07/2015 to 24/08/2015
    106 tags for period 24/08/2015 to 24/08/2015
    40 tags for period 17/08/2015 to 24/08/2015
    40 tags for period 24/08/2015 to 24/08/2015
    110 tags for period 27/07/2015 to 02/08/2015
    155 tags for period 24/08/2015 to 24/08/2015
    130 tags for period 03/08/2015 to 09/08/2015
    242 tags for period 03/08/2015 to 16/08/2015
    150 tags for period 27/07/2015 to 24/08/2015
    240 tags for period 17/08/2015 to 24/08/2015
    61 tags for period 03/08/2015 to 16/08/2015
    740 tags for period 27/07/2015 to 16/08/2015
    5 tags for period 24/08/2015 to 24/08/2015
    10 tags for period 03/08/2015 to 16/08/2015
    10 tags for period 10/08/2015 to 23/08/2015
    10 tags for period 03/08/2015 to 16/08/2015
    10 tags for period 27/07/2015 to 09/08/2015
    5 tags for period 27/07/2015 to 02/08/2015
    10 tags for period 17/08/2015 to 24/08/2015
    5 tags for period 27/07/2015 to 02/08/2015
    10 tags for period 17/08/2015 to 24/08/2015
    46 tags for period 27/07/2015 to 24/08/2015
    12 tags for period 27/07/2015 to 24/08/2015
    30 tags for period 27/07/2015 to 23/08/2015
    39 tags for period 27/07/2015 to 23/08/2015
    100 tags for period 27/07/2015 to 24/08/2015
    90 tags for period 03/08/2015 to 16/08/2015
    445 tags for period 27/07/2015 to 24/08/2015
    40 tags for period 27/07/2015 to 23/08/2015
    120 tags for period 27/07/2015 to 16/08/2015
    180 tags for period 24/08/2015 to 24/08/2015
    450 tags for period 27/07/2015 to 24/08/2015
    405 tags for period 27/07/2015 to 24/08/2015
    50 tags for period 27/07/2015 to 24/08/2015
    175 tags for period 27/07/2015 to 24/08/2015
    200 tags for period 27/07/2015 to 24/08/2015
    225 tags for period 27/07/2015 to 24/08/2015
    225 tags for period 27/07/2015 to 24/08/2015
    256 tags for period 10/08/2015 to 24/08/2015
    60 tags for period 24/08/2015 to 24/08/2015
    60 tags for period 17/08/2015 to 23/08/2015
    258 tags for period 27/07/2015 to 09/08/2015
    2480 tags for period 03/08/2015 to 24/08/2015
    58 tags for period 27/07/2015 to 02/08/2015
    97 tags for period 24/08/2015 to 24/08/2015
    391 tags for period 24/08/2015 to 24/08/2015
    76 tags for period 24/08/2015 to 24/08/2015
    111 tags for period 24/08/2015 to 24/08/2015
    135 tags for period 24/08/2015 to 24/08/2015
    18 tags for period 27/07/2015 to 24/08/2015
    367 tags for period 10/08/2015 to 24/08/2015
    30 tags for period 24/08/2015 to 24/08/2015
    75 tags for period 27/07/2015 to 02/08/2015
    137 tags for period 27/07/2015 to 24/08/2015
    113 tags for period 27/07/2015 to 24/08/2015
    1250 tags for period 27/07/2015 to 24/08/2015
    143 tags for period 10/08/2015 to 24/08/2015
    1209 tags for period 10/08/2015 to 24/08/2015
    165 tags for period 27/07/2015 to 23/08/2015
    153 tags for period 10/08/2015 to 24/08/2015
    55 tags for period 24/08/2015 to 24/08/2015
    110 tags for period 24/08/2015 to 24/08/2015
    10 tags for period 24/08/2015 to 24/08/2015
    20 tags for period 27/07/2015 to 23/08/2015
    600 tags for period 17/08/2015 to 24/08/2015
    492 tags for period 27/07/2015 to 23/08/2015
    15 tags for period 27/07/2015 to 16/08/2015
    610 tags for period 17/08/2015 to 23/08/2015
    458 tags for period 27/07/2015 to 02/08/2015
    50 tags for period 24/08/2015 to 24/08/2015
    472 tags for period 03/08/2015 to 09/08/2015
    280 tags for period 27/07/2015 to 02/08/2015
    12 tags for period 27/07/2015 to 24/08/2015
    230 tags for period 27/07/2015 to 02/08/2015
    75 tags for period 27/07/2015 to 02/08/2015
    280 tags for period 17/08/2015 to 23/08/2015
    220 tags for period 17/08/2015 to 24/08/2015
    150 tags for period 27/07/2015 to 02/08/2015
    100 tags for period 27/07/2015 to 02/08/2015
    470 tags for period 27/07/2015 to 24/08/2015
    156 tags for period 10/08/2015 to 23/08/2015
    38 tags for period 27/07/2015 to 02/08/2015
    174 tags for period 27/07/2015 to 16/08/2015
    34 tags for period 03/08/2015 to 24/08/2015
    225 tags for period 27/07/2015 to 24/08/2015
    35 tags for period 10/08/2015 to 16/08/2015
    84 tags for period 27/07/2015 to 16/08/2015
    96 tags for period 27/07/2015 to 23/08/2015
    138 tags for period 27/07/2015 to 16/08/2015
    120 tags for period 17/08/2015 to 23/08/2015
    15 tags for period 27/07/2015 to 24/08/2015
    15 tags for period 27/07/2015 to 24/08/2015

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Is there a formula that will extract the numbers to the left of the cell

    Try

    =LEFT(A1,FIND(" ",A1)-1)+0

  3. #3
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    610

    Re: Is there a formula that will extract the numbers to the left of the cell

    try:

    =TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",100)),100))
    If I've helped U pls click on d *Add Reputation

  4. #4
    Registered User
    Join Date
    04-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Is there a formula that will extract the numbers to the left of the cell

    Thank you both so much, both worked like a charm

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Is there a formula that will extract the numbers to the left of the cell

    You're welcome

  6. #6
    Registered User
    Join Date
    08-15-2008
    Location
    denver
    Posts
    68

    Re: Is there a formula that will extract the numbers to the left of the cell

    You can also use text-to-columns. Select your data that's in a single column, go to Data, Text to columns, select delimited (next), select space (next), select all but the first column under data preview and click Do not import column, and finish. You can play with it, and keep some of the other columns, such as the dates, if those are useful to you.

+ 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. Extract numbers from a sum formula inside a cell
    By cycloneclau in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2014, 01:55 PM
  2. [SOLVED] Formula to use numbers from left and right of cell to create a date
    By maryren in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-19-2013, 04:03 PM
  3. Replies: 2
    Last Post: 01-27-2013, 12:20 AM
  4. [SOLVED] Formula to Extract Numbers from Text Cell
    By MHamid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2012, 04:08 PM
  5. Extract Data to right and left of numbers
    By chl in forum Excel General
    Replies: 1
    Last Post: 03-18-2010, 02:19 PM
  6. 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
  7. [SOLVED] extract number and use in formula from text & numbers in cell
    By ivory_kitten in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-14-2006, 12:45 AM

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