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

B

bellaexcel

New Member
Joined
Aug 14, 2018
Messages
10
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:

[TABLE="width: 235"]
<colgroup><col></colgroup><tbody>[TR]
[TD]37441; 37443-37448; 3745; 37460; 3749

[/TD]
[/TR]
[TR]
[TD]37449; 37477; 37488; 37493-37495; 37498

[/TD]
[/TR]
[TR]
[TD]37410

[/TD]
[/TR]
[TR]
[TD]297

[/TD]
[/TR]
[TR]
[TD]29763-29764; 29773-29776[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 235"]
<tbody>[TR]
[TD]37441; 37443; 37444; 37445; 37446; 37447; 37448; 3745; 37460; 3749

[/TD]
[/TR]
[TR]
[TD]37449; 37477; 37488; 37493; 37494; 37495; 37498

[/TD]
[/TR]
[TR]
[TD]37410

[/TD]
[/TR]
[TR]
[TD]297

[/TD]
[/TR]
[TR]
[TD]29763; 29764; 29773; 29774; 29775; 29776; [/TD]
[/TR]
</tbody>[/TABLE]

Appreciate any advice as to how i should approach this.

Thanks! :)
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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
 
Upvote 0
alansidman said:
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
Click to expand...

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
 
Upvote 0
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?

[TABLE="class: grid, width: 400"]
<colgroup><col></colgroup><tbody>[TR]
[TD]37441; 37443-37448; 3745;[/TD]
[/TR]
[TR]
[TD]37460; 3749[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]37449; 37477; 37488;
[/TD]
[/TR]
[TR]
[TD]37493-37495; 37498[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]37410[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]297[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]29763-29764; 29773-29776[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Akuini said:
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?

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="width: 166"]37441; 37443-37448;[/TD]
[/TR]
[TR]
[TD]3745; 37460; 3749[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]37449; 37477; 37488;[/TD]
[/TR]
[TR]
[TD]37493-37495; 37498[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]37410[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]297[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]29763-29764; 29773-29776[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 400"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Hi Akuini,

Thanks for your reply.

There are no blank row separating it.

It looks like this below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]37441; 37443; 37444; 37445; 37446; 37447; 37448; 3745; 37460; 3749[/TD]
[/TR]
[TR]
[TD]37449; 37477; 37488; 37493-37495; 37498[/TD]
[/TR]
[TR]
[TD]37410[/TD]
[/TR]
[TR]
[TD]297[/TD]
[/TR]
[TR]
[TD]29763-29764; 29773-29776[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
Bella
 
Upvote 0
If I understand your request correctly, give this UDF (user defined function) a try...
Code: 
[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.
 
Upvote 0
Rick Rothstein said:
If I understand your request correctly, give this UDF (user defined function) a try...
Code: 
[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.
Click to expand...


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:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]37441; 37443; 37444; 37445; 37446; 37447; 37448; 3745; 37460; 3749[/TD]
[/TR]
[TR]
[TD]37449; 37477; 37488; 37493-37495; 37498[/TD]
[/TR]
[TR]
[TD]37410[/TD]
[/TR]
[TR]
[TD]297[/TD]
[/TR]
[TR]
[TD]29763-29764; 29773-29776

I'd like to add the number "00" infront of all of the individual number, making it:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]0037441; 0037443; 0037444; 0037445; 0037446; 0037447; 0037448; 003745; 0037460; 003749[/TD]
[/TR]
[TR]
[TD]0037449; 0037477; 0037488; 0037493; 0037494; 0037495; 0037498[/TD]
[/TR]
[TR]
[TD]0037410[/TD]
[/TR]
[TR]
[TD]00297[/TD]
[/TR]
[TR]
[TD]0029763; 0029764; 0029773, 0029774, 0029775, 0029776

Thanks,
Bella[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
bellaexcel said:
One other quick question if I may ask, how should i go about adding the number "00" in the front of every character?
Click to expand...
I think this should do what you want...
Code: 
[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]
 
Upvote 0
Rick Rothstein said:
I think this should do what you want...
Code: 
[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]
Click to expand...

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
 
Upvote 0
bellaexcel said:
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.
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

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