Turn numbers separated by a dash into comma separated series

[HowdeeDoodee]

Is there any way to turn two numbers within a cell separated by a dash into a series of numbers separated by commas?

Here is an example from the actual data BEFORE:

; Enn 6:16-20 ;
; 1Ctcc 6:3-15,50-53 ;
; Ent 4:14-16,27-31 ;
; Edr 28:40-43 ;
; Eefx 5:20,21 ; Newt 14:2-5,10 ; Nubb 16:3-11,41 ; Naab 20:2 ;
; Newt 16:46-48 ;
; Newt 20:10-12 ;
; Naab 20:12,23-29 ;
; Jtwq 12:13-15 ;
; Darew 1:6-20 ; Darew 2:17,49 ; Dnnrt 3:12-30 ; Httdb 11:34 ;
; Enn 4:1-15,25 ;
; 2Std 20:14-22 ;
; 1Std 8:1-5 ; 1Cdtdd 6:28 ;
; 1Std 22:20-23 ; 1Std 22:6-19 ;


Here is a sample of the data AFTER the macro is run
; Enn 6:16,17,18,19,20 ;
; 1Ctcc 6:3,4,5,6,7,8,50,51,52,53 ;
; Ent 4:14,15,16,27,28,29,30,31 ;

Thank you in advance for any replies.

 

[Jon von der Heyden]

Hi,

I recommend using find and replace (Ctrl+H). Highlight range and replace "-" with ",". Can be automated within VBA through recoding process and adapting to run on selection rather than specific range.

Regards

 

[HowdeeDoodee]

Thank you for the response. I will work on your suggestion. In the meantime, could you or someone else explain what you mean by "and adapting to run on selection rather than specific range."

In terms of making a recording, the number of integers and commas to replace each pair of integers separated by a dash will likely be different for each pair. What kind of code do I use to grab both integers on each side of the dash and then convert to a comma separated string?

Thank you again for the response.

 

[Jon von der Heyden]

Hi,

Use this code in a new module (Alt+F11 / Insert->Module).

Sub FindReplace()
    Selection.Replace What:="-", Replacement:=","
End Sub

 

[HowdeeDoodee]

Thank you for the reply. I don't need to replace just one dash with one comma which is what your code above does.

I need to replace one dash with a series of numbers separated by commas with the series of numbers determined by the integer to the left and to the right of the dash.

Programmtically, I know it will take string parsing, a For Next loop, asigning of the left integer and right integer to separate variables, then replacing the dash with the set of integers separated by commas as determined by the For Next loop. How do I do this?

 

[Jon von der Heyden]

Hi,

Have a look at this post and see how you may adapt it to your needs: http://www.mrexcel.com/board2/viewtopic.php?t=174585&highlight=

Regards

 

[HowdeeDoodee]

Excellent referral. Thank you very much.