Advance text to column

L

luvbite38

Active Member
Joined
Jun 25, 2008
Messages
368
Hi all,

I have a data which looks like this

1,3,5-10,20,22

and I want a macro to convert the data something like this:
<TABLE style="WIDTH: 198pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=261><COLGROUP><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" span=9 width=29><TBODY><TR style="HEIGHT: 22.5pt; mso-height-source: userset" height=30><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 22pt; HEIGHT: 22.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=30 width=29>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 22pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=29>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 22pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=29>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 22pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=29>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 22pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=29>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 22pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=29>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 22pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=29>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 22pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=29>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 22pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=29>22</TD></TR></TBODY></TABLE>

so everytime it see the - sign it should make up the numbers between the first (in our case 3) and the last (10) and does the text delimited, comma sep:

can someone please crack this for me......

A big thanks in advance.....
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Code: 
[COLOR="Blue"]Sub[/COLOR] SplitNumbers()

    [COLOR="Blue"]Dim[/COLOR] startNum [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], endNum [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], j [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], iCol [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], iRow [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] arr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], arr2 [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    
    iRow = Range("A1").Row
    iCol = Range("A1").Column + 1
    arr = Split(Range("A1"), ",")
    
    [COLOR="Blue"]For[/COLOR] i = 0 [COLOR="Blue"]To[/COLOR] UBound(arr)
        [COLOR="Blue"]If[/COLOR] InStr(arr(i), "-") > 0 [COLOR="Blue"]Then[/COLOR]
            arr2 = Split(arr(i), "-")
            startNum = arr2(0)
            endNum = arr2(1)
            [COLOR="Blue"]For[/COLOR] j = startNum [COLOR="Blue"]To[/COLOR] endNum
                Cells(iRow, iCol) = j
                iCol = iCol + 1
            [COLOR="Blue"]Next[/COLOR]
        [COLOR="Blue"]Else[/COLOR]
            Cells(iRow, iCol) = arr(i)
            iCol = iCol + 1
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]Next[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]
 
Upvote 0
May God give you loads of birds...it works like a magic....

I just need one modfication, If possible...

it retains the actual value i.e. 1,3,5-7 in cell A1,

is there a possiblility it removes that value
and starts from Cell A1
somehting like
1 3 5 6 7 from Cell A1 rightwards?
 
Upvote 0
woohoooo..... I changed

iCol = Range("A1").Column + 1
to
iCol = Range("A1").Column

and it is flying like a superman.....\\\

dude you're a super duper zuper star.........
 
Upvote 0
First of all million thanks for helping dude.... you;re a life saver.....

just a curious case of Mr Luvbite....

can I run this macro for more than one cell same time

like on

A1
A2
A3
.
.
.
A15

Thanks in advance....
 
Upvote 0
No problem...
Code: 
[COLOR="Blue"]Sub[/COLOR] SplitNumbers()

    [COLOR="Blue"]Dim[/COLOR] startNum [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], endNum [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], lastRow [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], j [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], f [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], iCol [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], iRow [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] arr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], arr2 [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    
    
    [COLOR="Blue"]For[/COLOR] f = Cells(Rows.Count, "A").End(xlUp).Row [COLOR="Blue"]To[/COLOR] 1 [COLOR="Blue"]Step[/COLOR] -1
    
        [COLOR="Blue"]With[/COLOR] Cells(f, "A")
            iRow = .Row
            iCol = .Column
            arr = Split(.Value, ",")
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
        
        [COLOR="Blue"]For[/COLOR] i = 0 [COLOR="Blue"]To[/COLOR] UBound(arr)
            [COLOR="Blue"]If[/COLOR] InStr(arr(i), "-") > 0 [COLOR="Blue"]Then[/COLOR]
                arr2 = Split(arr(i), "-")
                startNum = arr2(0)
                endNum = arr2(1)
                [COLOR="Blue"]For[/COLOR] j = startNum [COLOR="Blue"]To[/COLOR] endNum
                    Cells(iRow, iCol) = j
                    iCol = iCol + 1
                [COLOR="Blue"]Next[/COLOR]
            [COLOR="Blue"]Else[/COLOR]
                Cells(iRow, iCol) = arr(i)
                iCol = iCol + 1
            [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        [COLOR="Blue"]Next[/COLOR]
    
    [COLOR="Blue"]Next[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]
 
Upvote 0
I love you .... this what I'd say if you were a gal......

Man, you're a true excel guru........

simply genius

Sub Thanks ()

MsgBox("Dude Thanks a Allion to Zillionssss")

:)
 
Upvote 0
Not at all! :)

P.S. You forgot "End Sub"! :) :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
854
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
608
epactheactor
E
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
358
esben
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,215,632
Messages
6,125,909
Members
449,274
Latest member
mrcsbenson

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top