Separate a range of numbers into individual numbers within the same cell

[bellaexcel]

Hi excel gurus,

I have a set of data with many number range within a cell which i want to separate into individual numbers.

Example of the cells as follows:

37441; 37443-37448; 3745; 37460; 3749

37449; 37477; 37488; 37493-37495; 37498

37410

297

29763-29764; 29773-29776

<colgroup><col></colgroup><tbody>
</tbody>

What I am looking to do is to separate it into:

37441; 37443; 37444; 37445; 37446; 37447; 37448; 3745; 37460; 3749

37449; 37477; 37488; 37493; 37494; 37495; 37498

37410

297

29763; 29764; 29773; 29774; 29775; 29776;

<tbody>
</tbody>

Appreciate any advice as to how i should approach this.

Thanks!

 

[alansidman]

On the ribbon, select data, select text to columns.
Delimited
select semi-colon and dash (-) as the delimiters
Select a column that you wish the data to placed

 

[bellaexcel]

On the ribbon, select data, select text to columns.
Delimited
select semi-colon and dash (-) as the delimiters
Select a column that you wish the data to placed

Hi Alan,

Thanks for help.

I did try your method but it wouldn't work as I have some numbers which are 1011-1015 and I would like to sort it into 1011; 1012; 1013; 1014; 1015. Your method is currently only separating it into 1011; 1015.

Any other tips?

Cheers,
Bella

 

[Akuini]

Hi, bellaexcel
I don't quite understand the criteria to separate the number.
Are there groups of number in rows separated by a blank row?

Can you post the data using excel table?
Does your original data look like this?

37441; 37443-37448; 3745;

37460; 3749

37449; 37477; 37488;

37493-37495; 37498

37410

297

29763-29764; 29773-29776

<colgroup><col></colgroup><tbody>
</tbody>

 

[bellaexcel]

Hi, bellaexcel
I don't quite understand the criteria to separate the number.
Are there groups of number in rows separated by a blank row?

Can you post the data using excel table?
Does your original data look like this?

37441; 37443-37448;

3745; 37460; 3749

37449; 37477; 37488;

37493-37495; 37498

37410

297

29763-29764; 29773-29776

<tbody>
</tbody>

<tbody>
</tbody>

Hi Akuini,

Thanks for your reply.

There are no blank row separating it.

It looks like this below:

37441; 37443; 37444; 37445; 37446; 37447; 37448; 3745; 37460; 3749

37449; 37477; 37488; 37493-37495; 37498

37410

297

29763-29764; 29773-29776

<tbody>
</tbody>

Thanks,
Bella

 

[Rick Rothstein]

If I understand your request correctly, give this UDF (user defined function) a try...

[table="width: 500"]
[tr]
	[td]Function NumberRanges(ByVal sInput As String) As Variant
  Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
  If sInput Like "*# #*" Then GoTo Bad
  sInput = Replace(Replace(Replace(sInput, " ", ""), ";", ","), Chr(160), "")
  If sInput Like "*[!0-9,-]*" Or sInput Like "*[,-][,-]*" Or _
     Not sInput Like "*#" Or Not Val(sInput) Like "[1-9]*" Then GoTo Bad
  sNumbers = Split(sInput, ",")
  For X = 0 To UBound(sNumbers)
    If sNumbers(X) Like "*-*" Then
      If sNumbers(X) Like "*-*-*" Then GoTo Bad
      sRange = Split(sNumbers(X), "-")
      sNumbers(X) = ""
      For Z = sRange(0) To sRange(1) Step Sgn(sRange(1) - sRange(0) + 1)
        sNumbers(X) = sNumbers(X) & "," & Z
      Next
      sNumbers(X) = Mid(sNumbers(X), 2)
    Else
      sNumbers(X) = Val(sNumbers(X))
    End If
  Next
  NumberRanges = Join(sNumbers, "; ")
  Exit Function
Bad:
  NumberRanges = Array()
  MsgBox """" & sInput & """" & vbLf & vbLf & "The specified range of values is incorrectly formed!", vbCritical
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumberRanges just like it was a built-in Excel function. For example,

=NumberRanges(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[bellaexcel]

If I understand your request correctly, give this UDF (user defined function) a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function NumberRanges(ByVal sInput As String) As Variant
  Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
  If sInput Like "*# #*" Then GoTo Bad
  sInput = Replace(Replace(Replace(sInput, " ", ""), ";", ","), Chr(160), "")
  If sInput Like "*[!0-9,-]*" Or sInput Like "*[,-][,-]*" Or _
     Not sInput Like "*#" Or Not Val(sInput) Like "[1-9]*" Then GoTo Bad
  sNumbers = Split(sInput, ",")
  For X = 0 To UBound(sNumbers)
    If sNumbers(X) Like "*-*" Then
      If sNumbers(X) Like "*-*-*" Then GoTo Bad
      sRange = Split(sNumbers(X), "-")
      sNumbers(X) = ""
      For Z = sRange(0) To sRange(1) Step Sgn(sRange(1) - sRange(0) + 1)
        sNumbers(X) = sNumbers(X) & "," & Z
      Next
      sNumbers(X) = Mid(sNumbers(X), 2)
    Else
      sNumbers(X) = Val(sNumbers(X))
    End If
  Next
  NumberRanges = Join(sNumbers, "; ")
  Exit Function
Bad:
  NumberRanges = Array()
  MsgBox """" & sInput & """" & vbLf & vbLf & "The specified range of values is incorrectly formed!", vbCritical
End Function[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumberRanges just like it was a built-in Excel function. For example,

=NumberRanges(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Hi Rick,

Thanks for your help. This works like a charm and is what I was looking for.

One other quick question if I may ask, how should i go about adding the number "00" in the front of every character?

Instead of:

37441; 37443; 37444; 37445; 37446; 37447; 37448; 3745; 37460; 3749

37449; 37477; 37488; 37493-37495; 37498

37410

297

29763-29764; 29773-29776 I'd like to add the number "00" infront of all of the individual number, making it: 0037441; 0037443; 0037444; 0037445; 0037446; 0037447; 0037448; 003745; 0037460; 003749 0037449; 0037477; 0037488; 0037493; 0037494; 0037495; 0037498 0037410 00297 0029763; 0029764; 0029773, 0029774, 0029775, 0029776 Thanks, Bella <tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

One other quick question if I may ask, how should i go about adding the number "00" in the front of every character?

I think this should do what you want...

[table="width: 500"]
[tr]
	[td]Function NumberRanges(ByVal sInput As String) As Variant
  Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
  If sInput Like "*# #*" Then GoTo Bad
  sInput = Replace(Replace(Replace(sInput, " ", ""), ";", ","), Chr(160), "")
  If sInput Like "*[!0-9,-]*" Or sInput Like "*[,-][,-]*" Or _
     Not sInput Like "*#" Or Not Val(sInput) Like "[1-9]*" Then GoTo Bad
  sNumbers = Split(sInput, ",")
  For X = 0 To UBound(sNumbers)
    If sNumbers(X) Like "*-*" Then
      If sNumbers(X) Like "*-*-*" Then GoTo Bad
      sRange = Split(sNumbers(X), "-")
      sNumbers(X) = ""
      For Z = sRange(0) To sRange(1) Step Sgn(sRange(1) - sRange(0) + 1)
        sNumbers(X) = sNumbers(X) & ",00" & Z
      Next
      sNumbers(X) = Mid(sNumbers(X), 2)
    Else
      sNumbers(X) = "00" & Val(sNumbers(X))
    End If
  Next
  NumberRanges = Join(sNumbers, "; ")
  Exit Function
Bad:
  NumberRanges = Array()
  MsgBox """" & sInput & """" & vbLf & vbLf & "The specified range of values is incorrectly formed!", vbCritical
End Function[/td]
[/tr]
[/table]

 

[bellaexcel]

I think this should do what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function NumberRanges(ByVal sInput As String) As Variant
  Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
  If sInput Like "*# #*" Then GoTo Bad
  sInput = Replace(Replace(Replace(sInput, " ", ""), ";", ","), Chr(160), "")
  If sInput Like "*[!0-9,-]*" Or sInput Like "*[,-][,-]*" Or _
     Not sInput Like "*#" Or Not Val(sInput) Like "[1-9]*" Then GoTo Bad
  sNumbers = Split(sInput, ",")
  For X = 0 To UBound(sNumbers)
    If sNumbers(X) Like "*-*" Then
      If sNumbers(X) Like "*-*-*" Then GoTo Bad
      sRange = Split(sNumbers(X), "-")
      sNumbers(X) = ""
      For Z = sRange(0) To sRange(1) Step Sgn(sRange(1) - sRange(0) + 1)
        sNumbers(X) = sNumbers(X) & ",00" & Z
      Next
      sNumbers(X) = Mid(sNumbers(X), 2)
    Else
      sNumbers(X) = "00" & Val(sNumbers(X))
    End If
  Next
  NumberRanges = Join(sNumbers, "; ")
  Exit Function
Bad:
  NumberRanges = Array()
  MsgBox """" & sInput & """" & vbLf & vbLf & "The specified range of values is incorrectly formed!", vbCritical
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick,

Thanks for your code, it works very well.

I am just wondering why do i get a "#VALUE!" error for some of my cells? And i also get a pop up saying the specified range of values is incorrectly formed.

Thanks,
Bella

 

[Rick Rothstein]

I am just wondering why do i get a "#VALUE!" error for some of my cells? And i also get a pop up saying the specified range of values is incorrectly formed.

That would (should) mean that the value the function is evaluating is not formed from semi-colon delimited numbers (either with or without a dash between two of them). Blank cells will trigger that result for sure... if you are getting it for other values, tell me what those other values are.