Expand Range of Values to Individual Values

[madhuchelliah]

Hello Friends, I need a solution for my complicated requirement. I have specific format of value range. I need to expand the values as individual values.
Example: Input - 1to10. Output - 1 2 3 4 5 6 7 8 9 10 this is a simple example. My specific format is **150**155. The ** replaces by any of the value it may be numerical or text but the last 3 digits is always numerical value which is the range to expand.
Example: Input - WK150WK155. Output - WK150WK150 WK151WK151 WK152WK152 WK153WK153 WK154WK154 WK155WK155. Please anyone heads up and solve my issue. Thank you.

 

[MickG]

Try this:-
This should now work for all 3 examples:-

Function Mystr(R [COLOR=navy]As[/COLOR] Range) [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] nWd [COLOR=navy]As[/COLOR] Variant, S [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] wSt [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Ed [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Num1 [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] num2 [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Allstr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]With[/COLOR] R
  Sp = Split(.Value, " ")
  [COLOR=navy]For[/COLOR] S = 0 To UBound(Sp)
    [COLOR=navy]For[/COLOR] n = 1 To Len(Sp(S))
        [COLOR=navy]If[/COLOR] n = 1 [COLOR=navy]Then[/COLOR] wSt = Left(Sp(S), 1)
            [COLOR=navy]If[/COLOR] Mid(Sp(S), n + 1, 1) = wSt [COLOR=navy]Then[/COLOR]
                Ed = n: [COLOR=navy]Exit[/COLOR] For
            [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
    Num1 = Mid(Sp(S), Ed - 2, 3): num2 = Right(Sp(S), 3)
    [COLOR=navy]For[/COLOR] nWd = Num1 To num2
        nStr = nStr & Left(.Value, Ed - 3) & nWd & Left([a1], Ed - 3) & nWd & " "
    [COLOR=navy]Next[/COLOR] nWd
 
Allstr = Allstr & IIf(Allstr = "", Trim(nStr), " " & Trim(nStr))
Ed = 0: nStr = "": wSt = ""
[COLOR=navy]Next[/COLOR] S
[COLOR=navy]End[/COLOR] With
Mystr = Allstr
[COLOR=navy]End[/COLOR] Function

Regards Mick

 

[madhuchelliah]

Thanks Rick working great!!!!!!!!!!!

 

[madhuchelliah]

Thanks Mick its working cool

 

[madhuchelliah]

Try replacing the function Mick gave you with this one and see if it works the way you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function Mystr(S As String) As String
  Dim X As Long, Z As Long, Start As Long, Finish As Long, Txt As String, Parts() As String
  Parts = Split(S)
  For X = 0 To UBound(Parts)
    Txt = Left(Parts(X), Len(Parts(X)) / 2 - 3)
    Start = Right(Left(Parts(X), Len(Parts(X)) / 2), 3)
    Finish = Right(Parts(X), 3)
    For Z = Start To Finish
      Mystr = Mystr & " " & Txt & Z & Txt & Z
    Next
  Next
  Mystr = Trim(Mystr)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Hello Rick if my values contains with prefix as
0 its not showing proper values. for example VS001VS010 the result showing is VS1VS1....... instead of VS001VS001...........

 

[Rick Rothstein]

Hello Rick if my values contains with prefix as
0 its not showing proper values. for example VS001VS010 the result showing is VS1VS1....... instead of VS001VS001...........

Okay, give this function a try instead...

[table="width: 500"]
[tr]
	[td]Function Mystr(S As String) As String
  Dim X As Long, Z As Long, Start As Long, Finish As Long, Txt As String, Parts() As String
  Parts = Split(S)
  For X = 0 To UBound(Parts)
    Txt = Left(Parts(X), Len(Parts(X)) / 2 - 3)
    Start = Right(Left(Parts(X), Len(Parts(X)) / 2), 3)
    Finish = Right(Parts(X), 3)
    For Z = Start To Finish
      Mystr = Mystr & " " & Txt & Format(Z, "000") & Txt & Format(Z, "000")
    Next
  Next
  Mystr = Trim(Mystr)
End Function[/td]
[/tr]
[/table]