+ Reply to Thread
Results 1 to 2 of 2

Replace hyphenated sequence with incremental numbers

  1. #1
    Registered User
    Join Date
    07-03-2021
    Location
    Scarborough, England
    MS-Off Ver
    365
    Posts
    4

    Question Replace hyphenated sequence with incremental numbers

    I have a query I'm trying to solve but not sure where to start with this one. If I have a range, where certain cells use a hyphen to represent a sequence, I need to be able to replace the hyphen with the sequence itself, separated by spaces

    Example data:
    A B C D E F
    Row 1 2 5 6 7 8-14
    Row 2 2 4 6 8 10
    Row 3 2 4 6 8-11
    Row 4 2 4 6 8-12
    Row 5 2 4 6 8 13-19
    Row 6 2 4 6 8 13-19
    Row 7 2 4 6 8 13-19
    Row 8 2-5 7 8 13-15 22-25


    Target:
    A B C D E F
    Row 1 2 5 6 7 8 9 10 11 12 13 14
    Row 2 2 4 6 8 10
    Row 3 2 4 6 8 9 10 11
    Row 4 2 4 6 8 9 10 11 12
    Row 5 2 4 6 8 13 14 15 16 17 18 19
    Row 6 2 4 6 8 13 14 15 16 17 18 19
    Row 7 2 4 6 8 13 14 15 16 17 18 19
    Row 8 2 3 4 5 7 8 13 14 15 22 23 24 25

    Background:
    This is to tidy up a bill of materials for work, where the formatting of legacy products is terrible. Rather than using a standard format (which is now in place... D3 D4 D5 D8 D9 D10), the files were written as D3-5/8-D10 (or multiple combinations of hyphens, slashes and commas. I've already used a formula to separate the alpha characters and park them in an unaffected range, while using VBA to delimit the text into separate columns, and regex to remove the inconsistent alpha characters. The plan is to remove all variables, and then combine them into a single string.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,475

    Re: Replace hyphenated sequence with incremental numbers

    Give this a go.

    Please Login or Register  to view this content.
    Last edited by davesexcel; 07-03-2021 at 10:26 AM.

+ 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. Replies: 1
    Last Post: 04-28-2021, 02:09 PM
  2. [SOLVED] Separate sequence of numbers with sequence of letters
    By dell001 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-02-2020, 07:09 AM
  3. Sorting hyphenated numbers
    By brendaxx in forum Excel General
    Replies: 8
    Last Post: 11-03-2017, 03:34 PM
  4. [SOLVED] Replace hyphenated prefix of concatenated columns removing to much
    By capson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-14-2015, 05:02 PM
  5. [SOLVED] Adding and incremental sequence to each new row
    By coopedup in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2013, 07:56 AM
  6. Incremental find and replace
    By dougsc in forum Access Programming / VBA / Macros
    Replies: 5
    Last Post: 08-19-2010, 06:28 PM
  7. Sorting hyphenated numbers
    By Connie Martin in forum Excel General
    Replies: 5
    Last Post: 01-20-2005, 08: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