Results 1 to 5 of 5

Convert list of alpha numeric into ranges

Threaded View

  1. #1
    Registered User
    Join Date
    04-25-2017
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    13

    Convert list of alpha numeric into ranges

    HI- I have a list of alpha numeric strings where some follow contiguous ranges and then at times they don't. I'm looking to take this list and convert it to ranges instead of the list I have. for example, the list starts at ABC000000001 and is contiguous until it gets to ABC000048185 (Cell B2819). So the first range would be ABC000000001- ABC000048185. The next range would be ABC000057279-ABC000057305 and then ABC000060977-ABC000061022 and then ABC000096509-ABC000096541 and then ABC000096542-ABC000112813. I want to eliminate all of the contiguous strings and just list the ranges. Every time there is a gap, that is a new range. By gap I mean the difference between the value in the B column and the value in the cell below in the A column is greater than 1. The 2,821 rows where ABC000000001 goes to ABC000048185 would be converted to one row ABC000000001- ABC000048185. I am able to get the ending strings for all of the ranges with a formula but getting the corresponding beginning strings is giving me a problem. I attached the spreadsheet. Thanks!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert Alpha characters back to numeric
    By CNE5x in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2013, 11:46 PM
  2. [SOLVED] Convert alpha to numeric & then create formula - Thanks for reading
    By NylaTai in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-18-2013, 02:23 PM
  3. VBA Excel 2007 - Convert column alpha to numeric value
    By bmack in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-06-2013, 12:05 AM
  4. Convert alpha-numeric code to date
    By Buchu in forum Excel General
    Replies: 13
    Last Post: 06-04-2012, 12:51 PM
  5. Convert alpha-numeric to numeric
    By michael mac in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-12-2008, 04:13 PM
  6. Replies: 1
    Last Post: 11-28-2006, 02:42 AM
  7. Convert Alpha to Numeric
    By Vicki in forum Excel General
    Replies: 2
    Last Post: 03-21-2005, 08:06 AM
  8. convert cells containing alpha to numeric
    By ezu in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-09-2005, 06:06 PM

Tags for this Thread

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