+ Reply to Thread
Results 1 to 17 of 17

Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

  1. #1
    Registered User
    Join Date
    01-26-2015
    Location
    Berlin
    MS-Off Ver
    Office 2010
    Posts
    44

    Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Hi,

    it would be great, if someone more proficiend than I am can help me. I would like to convert a list of ranges of numbers into sequences of these numbers. There are three types, e.g.:

    "34f" should be converted to "34 35" (each number in its own cell of course)
    "34ff" to "34 35 36"
    "34-38" to "34 35 36 37 38"


    Thank you very much in advance!
    Cheers!

    EDIT: Initial post had an error: "34f" should be converted to "34 35" (not to "12 13")
    Last edited by roderh; 01-26-2015 at 04:27 PM.

  2. #2
    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: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Hi,

    Is this an exact replica of your current data? i.e. are there only three choices? These things are always easier if we can see the request in the context of the workbook concerned.
    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.

  3. #3
    Registered User
    Join Date
    01-26-2015
    Location
    Berlin
    MS-Off Ver
    Office 2010
    Posts
    44

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Thanks for replying so fast!
    Yes, there are only these three choices (which I have called "types") (with other numbers of course). But - of course - in the Input-list there will be numbers, which should not be converted (e.g. "15" to "15"). I attached a file; maybe it is more clear.

    TIA!
    Attached Files Attached Files

  4. #4
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    The following macro & function transforms a string consisting of numbers separated by commas, but with some representing series in which the first & last numbers are joined by a hyphen, into a simple list of consecutive numbers. As coded, it provides for different separators & joiners, but doesn’t cater for a final '&' or 'and'. I'll leave it for you to adapt for use in your workbook.
    Please Login or Register  to view this content.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Registered User
    Join Date
    01-26-2015
    Location
    Berlin
    MS-Off Ver
    Office 2010
    Posts
    44

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Thank you very much macropod!
    I am able to copy this (at least to me) impressive piece of code into a VBAProject and I can run it, but thats it. I am very sorry to say, but I am just not skilled enough to use this code-"module". I do not know how to send such a string to this code-"module" nor do I know how to save the output.
    If you could implement your code into my example above ("example.xlsx"), that would be great. Then, I think, I could do the rest by myself.
    TIA!

    Edit: I may be wrong, but it does not seem to work with strings like "12ff" or "12f".
    Last edited by roderh; 01-26-2015 at 04:23 PM.

  6. #6
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Try using the Function with:
    Please Login or Register  to view this content.
    Do note that macros cannot be saved in xlsx workbooks - only in xls & xlsm workbooks.
    Last edited by macropod; 01-26-2015 at 06:50 PM.

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Try
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-26-2015
    Location
    Berlin
    MS-Off Ver
    Office 2010
    Posts
    44

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Thank you again macropod and thank you jindon!
    At the moment I can understand jindons code better. Two problems, though:
    1) A "single" number will not have an output, e.g. "12" will not be converted to "12"
    2) I can't adapt the code from a single row (e.g.: "b3") to a "matrix" (e.g.: "b3:c5"), i.e. I don't know how to add additional rows to the code

    TIA!
    Attached Files Attached Files
    Last edited by roderh; 01-26-2015 at 05:31 PM.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Can you show me your desired result in your latest workbook?

  10. #10
    Registered User
    Join Date
    01-26-2015
    Location
    Berlin
    MS-Off Ver
    Office 2010
    Posts
    44

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    arigato gozaimasu!
    Here it is:
    Attached Files Attached Files
    Last edited by roderh; 01-26-2015 at 05:41 PM.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Selection the range, B3:E5, first then run the code.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-26-2015
    Location
    Berlin
    MS-Off Ver
    Office 2010
    Posts
    44

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Sorry, I just do not know, how I can insert the range (i.e.: b3:d5) into your code!
    Greetings!

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Hummmm...
    Replace the code with the following
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    01-26-2015
    Location
    Berlin
    MS-Off Ver
    Office 2010
    Posts
    44

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Thanks for your patience!
    At the moment, output does not not seem correct (see "Screenshot.gif" or "example with code (4).xlsm"). The three-digit-numbers in "456ff" are cut to two-digit-numbers "45 46 47". Same problem with "34f" and "54f".
    Output should be like this (see "example.xls")
    Attached Images Attached Images
    Attached Files Attached Files

  15. #15
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Try:
    Please Login or Register  to view this content.

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Ahhh, I had accidentally changed your desired result by my code.....
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    01-26-2015
    Location
    Berlin
    MS-Off Ver
    Office 2010
    Posts
    44

    Re: Convert number-ranges to sequence of numbers (e.g. 142-145 to 142, 143, 144, 145)

    Thank you again jindon and macropod!

    Both codes seem to work as expected - hard to choose.

    I am very grateful; you saved my a lot of time!

+ 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] UDF to number a sequence of numbers of letters
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-25-2014, 12:53 AM
  2. Replies: 1
    Last Post: 11-07-2014, 12:10 AM
  3. Replies: 6
    Last Post: 11-26-2013, 05:21 PM
  4. [SOLVED] How do I find a missing number in a sequence of numbers?
    By Nash in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. How do I find a missing number in a sequence of numbers?
    By Nash in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-11-2005, 12:05 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