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:

[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!

 

[bellaexcel]

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.

I am getting it for cells which are pretty large in size.

An example would be:

474000-474001; 474010-474011; 474014; 47402-47403; 474045-474050; 474055; 474060-474064; 47407; 474100-474101; 474106-474107; 474117-474119; 47450-47452; 474539; 47454; 474550-474551; 474559; 474566-474569; 474576-474579; 474583-474584; 474586; 474593-474594; 47464; 474650; 474654; 474660-474663; 474670; 474673-474675; 474714-474717; 474720-474722; 474744; 474754; 474757-474759; 474833-474834; 474837; 474840; 474850; 474893; 475800040; 475800060; 475800070-475800075; 4758000898-4758000899; 47580009; 4758001200-4758001202; 475931-475932; 47598-47599; 47920; 479210; 479217; 47922; 479237; 47924; 479257; 47926; 479277; 47928; 479297; 47930; 479317; 47932; 479337; 47934; 479357; 47936; 479377; 47938; 479391; 479410-479412; 47942; 479442-479443; 479447-479449; 479664; 479692; 47980; 479815-479819; 47982; 479830-479837; 47984; 479854; 47986; 479876; 47988; 479899; 479967-479968

It will give me an error.

However if i were to delete part of the data and use:

474000-474001; 474010-474011; 474014; 47402-47403; 474045-474050; 474055; 474060-474064; 47407; 474100-474101; 474106-474107; 474117-474119; 47450-47452; 474539; 47454; 474550-474551; 474559; 474566-474569; 474576-474579; 474583-474584; 474586; 474593-474594; 47464; 474650; 474654; 474660-474663; 474670; 474673-474675; 474714-474717; 474720-474722; 474744; 474754; 474757-474759

The formula would work fine.

Thanks,
Bella

 

[Rick Rothstein]

I am getting it for cells which are pretty large in size.

An example would be:

474000-474001; 474010-474011; 474014; 47402-47403; 474045-474050; 474055; 474060-474064; 47407; 474100-474101; 474106-474107; 474117-474119; 47450-47452; 474539; 47454; 474550-474551; 474559; 474566-474569; 474576-474579; 474583-474584; 474586; 474593-474594; 47464; 474650; 474654; 474660-474663; 474670; 474673-474675; 474714-474717; 474720-474722; 474744; 474754; 474757-474759; 474833-474834; 474837; 474840; 474850; 474893; 475800040; 475800060; 475800070-475800075; 4758000898-4758000899; 47580009; 4758001200-4758001202; 475931-475932; 47598-47599; 47920; 479210; 479217; 47922; 479237; 47924; 479257; 47926; 479277; 47928; 479297; 47930; 479317; 47932; 479337; 47934; 479357; 47936; 479377; 47938; 479391; 479410-479412; 47942; 479442-479443; 479447-479449; 479664; 479692; 47980; 479815-479819; 47982; 479830-479837; 47984; 479854; 47986; 479876; 47988; 479899; 479967-479968

It will give me an error.

The problem was values like 4758000898 inside your cell... when I tried to iterate them in a loop, the loop counter Z could not handle them because Z was declared as a Long and 4758000898 is bigger than the maximum value a Long can be. I have fixed the code so that it should now be able to handle values up to 28 digits long.

[table="width: 500"]
[tr]
	[td]Function NumberRanges(ByVal sInput As String) As Variant
  Dim X As Long, Z As Variant, 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 = CDec(sRange(0)) To CDec(sRange(1)) Step Sgn(CDec(sRange(1)) - CDec(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]

The problem was values like 4758000898 inside your cell... when I tried to iterate them in a loop, the loop counter Z could not handle them because Z was declared as a Long and 4758000898 is bigger than the maximum value a Long can be. I have fixed the code so that it should now be able to handle values up to 28 digits long.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function NumberRanges(ByVal sInput As String) As Variant
  Dim X As Long, Z As Variant, 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 = CDec(sRange(0)) To CDec(sRange(1)) Step Sgn(CDec(sRange(1)) - CDec(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]

Thanks Rick, that solved most of the errors but one.

This one is returning an error but none of the numbers are longer than 28 figures?

6661018-6661021; 6661023-6661032; 6661034-6661043; 6661045-6661054; 6661056-6661065; 6661067-6661076; 6661078-6661087; 6661089-6661098; 6661101-6661109; 666111; 6661120-6661121; 6661123-6661132; 6661134-6661143; 6661145-6661154; 6661156-6661165; 6661167-6661176; 6661178-6661187; 6661189-6661198; 6661201-6661210; 6661212-6661221; 6661223-6661232; 6661234-6661243; 6661245-6661254; 6661256-6661265; 6661267-6661276; 6661278-6661287; 6661289-6661298; 6661301-6661310; 6661312-6661321; 6661323-6661332; 6661334-6661343; 6661345-6661354; 6661356-6661365; 6661367-6661376; 6661378-6661387; 6661389-6661398; 6661401-6661410; 6661412-6661421; 6661423-6661432; 6661434-6661443; 6661445-6661454; 6661456-6661465; 6661467-6661476; 6661478-6661487; 6661489-6661498; 6661501-6661510; 6661512-6661521; 6661523-6661532; 6661534-6661543; 6661545-6661554; 6661556-6661565; 6661567-6661576; 6661578-6661587; 6661589-6661598; 6661601-6661610; 6661612-6661621; 6661623-6661632; 6661634-6661643; 6661645-6661654; 6661656-6661665; 6661667-6661676; 6661678-6661683; 6662101-6662110; 6662112-6662121; 6662123-6662132; 6662134-6662143; 6662145-6662154; 6662156-6662165; 6662167-6662176; 6662178-6662187; 6662189-6662198; 6662201-6662210; 6662212-6662219; 666222; 6662230-6662232; 6662234-6662243; 6662245-6662254; 6662256-6662265; 6662267-6662276; 6662278-6662287; 6662289-6662298; 6662301-6662310; 6662312-6662321; 6662323-6662332; 6662334-6662343; 6662345-6662354; 6662356-6662365; 6662367-6662376; 6662378-6662387; 6662389-6662398; 6662401-6662410; 6662412-6662421; 6662423-6662432; 6662434-6662443; 6662445-6662454; 6662456-6662465; 6662467-6662476; 6662478-6662487; 6662489-6662498; 6662501-6662510; 6662512-6662521; 6662523-6662532; 6662534-6662543; 6662545-6662554; 6662556-6662565; 6662567-6662576; 6662578-6662587; 6662589-6662598; 6662601-6662610; 6662612-6662613; 6662983-6662987; 6662989-6662998; 6663001-6663010; 6663012-6663021; 6663023-6663032; 6663034-6663043; 6663045-6663054; 6663056-6663065; 6663067-6663076; 6663078-6663087; 6663089-6663098; 6663101-6663110; 6663112-6663121; 6663123-6663132; 6663134-6663143; 6663145-6663154; 6663156-6663165; 6663167-6663176; 6663178-6663187; 6663189-6663198; 6663201-6663210; 6663212-6663221; 6663223-6663232; 6663234-6663243; 6663245-6663254; 6663256-6663265; 6663267-6663273; 6663573-6663576; 6663578-6663587; 6663589-6663598; 6663601-6663610; 6663612-6663621; 6663623-6663632; 6663634-6663643; 6663645-6663654; 6663656-6663665; 6663667-6663676; 6663678-6663687; 6664930-6664932; 6664934-6664943; 6664945-6664954; 6664956-6664965; 6664967-6664976; 6664978-6664987; 6664989-6664998; 6665001-6665010; 6665012-6665021; 6665023-6665032; 6665034-6665043; 6665045-6665054; 6665056-6665065; 6665067-6665076; 6665078-6665087; 6665089-6665098; 6665101-6665110; 6665112-6665121; 6665123; 6665236-6665243; 6665245-6665254; 6665256-6665265; 6665267-6665276; 6665278-6665287; 6665289-6665298; 6665301-6665310; 6665312-6665321; 6665323-6665332; 6665334-6665343; 6665345-6665354; 6665356-6665365; 6665367-6665376; 6665378-6665387; 6665389-6665398; 6665401-6665410; 6665412-6665421; 6665423-6665432; 6665434-6665443; 6665445-6665454; 6665456-6665465; 6665467-6665476; 6665478-6665487; 6665489-6665498; 6665501-6665510; 6665512-6665521; 6665523-6665532; 6665534-6665543; 6665545-6665549; 666555; 6665560-6665565; 6665567-6665576; 6665578-6665587; 6665589-6665598; 6665601-6665610; 6665612-6665621; 6665623-6665632; 6665634-6665643; 6665645-6665654; 6665656-6665665; 6665667-6665676; 6665678-6665687; 6665689-6665698; 6665701-6665710; 6665712-6665721; 6665723-6665732; 6665734; 6665901-6665910; 6665912-6665921; 6665923-6665932; 6665934-6665943; 6665945-6665949; 666595-666596; 6665970-6665976; 6665978-6665987; 6665989-6665997; 6692246-6692254; 6692256-6692265; 6692267-6692276; 6692278-6692287; 6692289-6692298; 6692310; 6692312-6692321; 6692323-6692332; 6692334-6692343; 6692345-6692354; 6692356-6692365; 6692367-6692376; 6692378-6692387; 6692389-6692398; 6692401-6692410; 6692412-6692421; 6692423-6692432; 6692434-6692443; 6692445-6692454; 6692456-6692465; 6692467-6692476; 6692478-6692487; 6692489-6692498; 6692501-6692510; 6692512-6692521; 6692523-6692532; 6692534-6692543; 6692545-6692554; 6692556-6692565; 6692567-6692576; 6692578-6692587; 6692589-6692598; 6692601-6692610; 6692612-6692621; 6692623-6692632; 6692634-6692643; 6692645-6692654; 6692656-6692665; 6692667-6692676; 6692678-6692687; 6692689-6692698; 6692701-6692710; 6692712-6692721; 6692723-6692732; 6692734-6692743; 6692745-6692754; 6692756-6692765; 6692767-6692776; 6692778-6692787; 6692789-6692798; 6692801-6692810; 6692812-6692821; 6692823-6692832; 6692834-6692843; 6692845-6692854; 6692856-6692865; 6692867-6692876; 6692878-6692887; 6692889-6692898; 6692901-6692910; 6692912-6692921; 6692923-6692932; 6692934-6692943; 6692945-6692954; 6692956-6692965; 6692967-6692976; 6692978-6692987; 6692989-6692998; 6693001-6693010; 6693012-6693021; 6693023-6693032; 6693034-6693043; 6693045-6693054; 6693056-6693065; 6693067-6693076; 6693078-6693087; 6693089-6693098; 6693101-6693110; 6693112-6693121; 6693123-6693132; 6693134-6693143; 6693145-6693154; 6693156-6693165; 6693167-6693176; 6693178-6693187; 6693189-6693198; 6693208-6693210; 6693212-6693221; 6693223-6693232; 6693234-6693243; 6693245-6693254; 6693256-6693265; 6693267-6693276; 6693278-6693287; 6693289-6693298; 6693301-6693310; 6693312-6693321; 6693323-6693330; 6693332-6693343; 6693345-6693354; 6693356-6693365; 6693367-6693376; 6693378-6693387; 6693389-6693398; 6693408-6693410; 6693412-6693421; 6693423-6693432; 6693434-6693443; 6693445-6693454; 6693456-6693465; 6693467-6693476; 6693478-6693487; 6693489-6693498; 6693501-6693510; 6693512-6693521; 6693523-6693532; 6693534-6693543; 6693545-6693554; 6693556-6693565; 6693567-6693576; 6693578-6693587; 6693589-6693598; 6693601-6693610; 6693612-6693621; 6693623-6693632; 6693634-6693643; 6693645-6693654; 6693656-6693665; 6693667-6693676; 6693678-6693687; 6693689-6693698; 6693708-6693710; 6693712-6693721; 6693723-6693732; 6693734-6693743; 6693745-6693754; 6693756-6693765; 6693767-6693776; 6693778-6693787; 6693789-6693795; 6697918-6697921; 6697923-6697932; 6697934-6697943; 6697945-6697954; 6697956-6697965; 6697967-6697976; 6697978-6697987; 6697989-6697998; 6698001-6698010; 6698012-6698021; 6698023-6698032; 6698034-6698043; 6698045-6698054; 6698056-6698065; 6698067-6698076; 6698078-6698087; 6698089-6698098; 6698101-6698110; 6698112-6698121; 6698123-6698132; 6698134-6698143; 6698145-6698154; 6698156-6698165; 6698167-6698176; 6698178-6698187; 6698189-6698198; 6698201-6698210; 6698212-6698221; 6698223-6698232; 6698234-6698243; 6698245-6698254; 6698256-6698265; 6698267-6698276; 6698278-6698287; 6698289-6698298; 6698301-6698310; 6698312-6698321; 6698323-6698332; 6698334-6698343; 6698345-6698354; 6698356-6698365; 6698367-6698376; 6698378-6698387; 6698389-6698398; 6698401-6698410; 6698412-6698421; 6698423-6698432; 6698434-6698443; 6698445-6698454; 6698456-6698465; 6698467-6698476; 6698478-6698487; 6698489-6698498; 6698501-6698510; 6698512-6698521; 6698523-6698532; 6698534-6698543; 6698545-6698554; 6698556-6698565; 6698567-6698576; 6698578-6698587; 6698589-6698598; 6698601-6698610; 6698612-6698621; 6698623-6698632; 6698634-6698643; 6698645-6698654; 6698656-6698665; 6698667-6698676; 6698678-6698687; 6698689-6698698; 6698701-6698710; 6698712-6698721; 6698723-6698732; 6698734-6698743; 6698745-6698754; 6698756-6698765; 6698767-6698776; 6698778-6698787; 6698789-6698798; 6698801-6698810; 6698812-6698821; 6698823-6698832; 6698834-6698843; 6698845-6698854; 6698856-6698865; 6698867-6698876; 6698878-6698879; 669888; 6698890-6698898; 6698901-6698910; 6698912-6698921; 6698923-6698932; 6698934-6698943; 6698945-6698954; 6698956-6698965; 6698967-6698976; 6698978-6698987; 6698989-6698998; 6699001-6699010; 6699012-6699017

Thanks,
Bella

 

[Rick Rothstein]

I think that after the ranges are expanded, you are exceeding the number of characters that a "paragraph" in a cell can have. I do not believe there is anything I can do to overcome this problem.

 

[bellaexcel]

I understand, thank you so much for your help Rick!

Really appreciate it. You have saved me so much time and effort.

Cheers,
Bella