+ Reply to Thread
Results 1 to 5 of 5

formula to find first SET of numbers in a text string

  1. 09-01-2017, 05:37 PM #1
    krunk
    krunk is offline
    Registered User
    Join Date
    02-20-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2010
    Posts
    74

    formula to find first SET of numbers in a text string

    Hi, I am looking for a formula to extract the first set of numbers from a text string.. I have found formula's to extract the first number and or all numbers but not the first set. Here is an example of what the text string looks like and you will understand what i mean by set. Does anyone know of a solution?

    Text string:
    "Period 1
    Actual
    2017"

    The reason i can't just find the first is because there are 12 months in a year and I need a formula to identify the period without the remaining text. Mind you I have years of data to pull.

    I appreciate the help. Thanks again.
    Reply With QuoteRegister To Reply
  2. 09-01-2017, 05:44 PM #2
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: formula to find first SET of numbers in a text string

    Hi krunk! A small sample workbook (NOT a picture) would help us to solve your problem quickly and accurately.
    Remove any sensitive or extraneous info, we just need some “realistic” data for testing purposes.
    Please include examples of any variations the code or formula must address.
    Also, PLEASE simulate some results to demonstrate what you want.

    To attach a workbook:
    Click Edit Post (or just start a new reply.)
    Click Go Advanced
    Scroll down to Manage Attachments and click.
    Now just Browse for your file, then click Upload. Simple!
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
    Reply With QuoteRegister To Reply
  3. 09-01-2017, 06:01 PM #3
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: formula to find first SET of numbers in a text string

    Given as a pattern [always starts with "Period" + line break (char(10) always follows first number], this might work (assuming string is in A1): 
    Please Login or Register  to view this content.
    Last edited by leelnich; 09-01-2017 at 06:04 PM.
    Reply With QuoteRegister To Reply
  4. 09-01-2017, 06:51 PM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,747

    Re: formula to find first SET of numbers in a text string

    Another way.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Edit If the first number is followed by any text which Excel can interpret as a month ("mmm", "mmmm") or exponential E then this will likely fail.
    Like leelnich says workbook data will help us know more.
    Last edited by FlameRetired; 09-01-2017 at 06:57 PM.
    Dave
    Reply With QuoteRegister To Reply
  5. 09-02-2017, 09:06 AM #5
    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: formula to find first SET of numbers in a text string

    Hope this is what you looking for
    Enter formula in cell B1 and drag formula across to C1 and down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    v A B C
    1 Period 11
    Actual
    2017    		 11 2017
    2 Period 3
    Actual
    2018    		 3 2018
    3 Period 12
    Actual
    2019    		 12 2019
    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
+ 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] formula to find starting position and count numbers in a string
    By craig04 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-07-2017, 03:33 PM
  2. Formula to find and return text string within a cell full of text strings
    By LightingPop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2013, 11:45 PM
  3. [SOLVED] Find partial text string within another text string return original text into cell.
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-17-2013, 02:58 PM
  4. [SOLVED] Need a Macro / VBA to find numbers in a text string and add a thousands comma
    By chicolocal in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-27-2012, 03:12 PM
  5. Formula to find a number in a string of numbers separated by a symbol
    By cslm001 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-12-2012, 11:23 AM
  6. Formula to Find a specific text within a text string
    By ewong in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-13-2012, 01:36 PM
  7. Find, return string address in a range of cells with numbers and text
    By Vera22 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-09-2007, 08:37 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