Cell A1 has 345-347,456;567,720
In column B, I would like to extract the above data as under:

B1 345
B2 346
B3 347
B4 456
B5 567
B6 720

Hi,

If I understand correctly then when there is a - you want the numbers
missing in the range. If I'm correct then try this

Sub SplitOut()
rowno = 1
Dim numstring As Variant
numstring = Range("A1").Value
numstring = WorksheetFunction.Substitute(numstring, " ", "")
numstring = WorksheetFunction.Substitute(numstring, ";", ",")
s = Split(numstring, ",")
For x = 0 To UBound(s)
If InStr(s(x), "-") 0 Then
t = Split(s(x), "-")
For y = Val(t(0)) To Val(t(UBound(t)))
Cells(rowno, 8).Value = y
rowno = rowno + 1
Next
Else
Cells(rowno, 8).Value = Val(s(x))
rowno = rowno + 1
End If
Next
End Sub

Mike

"GINN" wrote:

Cell A1 has 345-347,456;567,720
In column B, I would like to extract the above data as under:

B1 345
B2 346
B3 347
B4 456
B5 567
B6 720

Hi ,

should I use this as user-defined function or If I straight away copy your
formula in the reference cell to get the desired answer. Pl. let me know how
to try your suggestion in excel work sheet functions.


"Mike H" wrote:

Hi,

If I understand correctly then when there is a - you want the numbers
missing in the range. If I'm correct then try this

Sub SplitOut()
rowno = 1
Dim numstring As Variant
numstring = Range("A1").Value
numstring = WorksheetFunction.Substitute(numstring, " ", "")
numstring = WorksheetFunction.Substitute(numstring, ";", ",")
s = Split(numstring, ",")
For x = 0 To UBound(s)
If InStr(s(x), "-") 0 Then
t = Split(s(x), "-")
For y = Val(t(0)) To Val(t(UBound(t)))
Cells(rowno, 8).Value = y
rowno = rowno + 1
Next
Else
Cells(rowno, 8).Value = Val(s(x))
rowno = rowno + 1
End If
Next
End Sub

Mike

"GINN" wrote:

Cell A1 has 345-347,456;567,720
In column B, I would like to extract the above data as under:

B1 345
B2 346
B3 347
B4 456
B5 567
B6 720

You would use as a macro.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

On Mon, 21 Sep 2009 10:22:01 -0700, GINN
wrote:

Hi ,

should I use this as user-defined function or If I straight away copy your
formula in the reference cell to get the desired answer. Pl. let me know how
to try your suggestion in excel work sheet functions.


"Mike H" wrote:

Hi,

If I understand correctly then when there is a - you want the numbers
missing in the range. If I'm correct then try this

Sub SplitOut()
rowno = 1
Dim numstring As Variant
numstring = Range("A1").Value
numstring = WorksheetFunction.Substitute(numstring, " ", "")
numstring = WorksheetFunction.Substitute(numstring, ";", ",")
s = Split(numstring, ",")
For x = 0 To UBound(s)
If InStr(s(x), "-") 0 Then
t = Split(s(x), "-")
For y = Val(t(0)) To Val(t(UBound(t)))
Cells(rowno, 8).Value = y
rowno = rowno + 1
Next
Else
Cells(rowno, 8).Value = Val(s(x))
rowno = rowno + 1
End If
Next
End Sub

Mike

"GINN" wrote:

Cell A1 has 345-347,456;567,720
In column B, I would like to extract the above data as under:

B1 345
B2 346
B3 347
B4 456
B5 567
B6 720