+ Reply to Thread
Results 1 to 5 of 5

How to convert a range of numbers to sequence of numbers in an array

  1. #1
    Registered User
    Join Date
    11-25-2015
    Location
    Richmond VA USA
    MS-Off Ver
    2016
    Posts
    2

    How to convert a range of numbers to sequence of numbers in an array

    I need to covert a list of years from this format
    2005-2008
    2009-2009
    2009-2012
    2009-2014

    to

    2005, 2006, 2007 ,2008
    2009
    2009, 2010, 2011, 2012
    2009, 2010, 2011, 2012, 2013, 2014

    Any help with a formula to do this would be great.

  2. #2
    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: How to convert a range of numbers to sequence of numbers in an array

    Maybe like this

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


    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    2005-2008
    2005
    2006
    2007
    2008
    2
    2009-2009
    2009
    3
    2009-2012
    2009
    2010
    2011
    2012
    4
    2009-2014
    2009
    2010
    2011
    2012
    2013
    2014
    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

  3. #3
    Registered User
    Join Date
    11-25-2015
    Location
    Richmond VA USA
    MS-Off Ver
    2016
    Posts
    2

    Re: How to convert a range of numbers to sequence of numbers in an array

    Thanks but that doesn't seem to format it correctly, I need to to end up in one cell and formatted like this
    2009-2014 would have to be 2009, 2010, 2011, 2012, 2013, 2014

  4. #4
    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: How to convert a range of numbers to sequence of numbers in an array

    Quote Originally Posted by MetisConnect View Post
    Thanks but that doesn't seem to format it correctly, I need to to end up in one cell and formatted like this
    2009-2014 would have to be 2009, 2010, 2011, 2012, 2013, 2014
    For this you would need a VBA solution.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,300

    Re: How to convert a range of numbers to sequence of numbers in an array

    Please Login or Register  to view this content.

    Data in A1

    in B1 =arr(A1)

+ 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. [SOLVED] Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)
    By roderh in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-27-2015, 12:54 PM
  2. Replies: 8
    Last Post: 06-28-2014, 03:26 PM
  3. [SOLVED] Convert numbers stored as text to numbers for range of data that changes
    By excel2425 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2014, 01:57 PM
  4. Replies: 3
    Last Post: 08-20-2013, 03:59 AM
  5. [SOLVED] Convert all numbers stored as text or custom formatted to numbers &no decimals - 40 sheets
    By synses in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2013, 01:46 AM
  6. Convert Range of numbers to Either 1,0
    By Descent73 in forum Excel General
    Replies: 5
    Last Post: 11-21-2012, 03:20 PM
  7. [SOLVED] Random numbers but 3 numbers in sequence not allowed.
    By NewGen in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-31-2012, 07:51 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