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 UDF:-
Where R = the cell with the string

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
[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]
[COLOR=navy]With[/COLOR] R
    [COLOR=navy]For[/COLOR] n = 1 To Len(.Value)
        [COLOR=navy]If[/COLOR] n = 1 [COLOR=navy]Then[/COLOR] wSt = Left(.Value, 1)
        If Mid(.Value, n + 1, 1) = wSt Then Ed = n 
    [COLOR=navy]Next[/COLOR] n
    Num1 = Mid(.Value, Ed - 2, 3): num2 = Right(.Value, 3)
    [COLOR=navy]For[/COLOR] nWd = Num1 To num2
        nStr = nStr & Left([a1], Ed - 3) & nWd & Left([a1], Ed - 3) & nWd & " "
    [COLOR=navy]Next[/COLOR] nWd
Mystr = Trim(nStr)
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] Function

Regards Mick

 

[madhuchelliah]

Hello Mick, Thanks for your solution. I don't have much knowledge in macros. Could you please guide me how to use this.

 

[MickG]

Try this:-

To Save and Run Function:-
Copy code from Thread
In Your Data sheet , Click"Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click"Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

Assuming Basic string is in"A1".
Then In "B1" Write :- =Mystr(a1)
Click "Enter"
"B1" should now show Expectedresults.

Regrds Mick

 

[madhuchelliah]

Hi Mick, i did as per your instructions but i am not getting any results. I have doubt on the second paragraph you entered. Where i have to give my input? Could you please clarify on this?

 

[MickG]

Have a look at this example file:-
https://app.box.com/s/btwy869yeiwlsog56g4m2a5hrgexyc5s

 

[madhuchelliah]

Thanks Mick. Working Great. First time seeing this type of codes. I am just familiar with basic macros which starts with Sub and end with End Sub. Thanks again.

 

[MickG]

You're welcome

 

[madhuchelliah]

Hello Mick this value 1A5501A551 is not expanding. I cant figure out the issue. One more issue if i have multiple values in a cell it is not working for example WK150WK151 WK155WK156 the mentioned value should expand like this WK150WK150 WK151WK151 WK155WK155 WK156WK156. But its not expanding like i mentioned. Please help me. Thank you.

 

[Rick Rothstein]

Hello Mick this value 1A5501A551 is not expanding. I cant figure out the issue. One more issue if i have multiple values in a cell it is not working for example WK150WK151 WK155WK156 the mentioned value should expand like this WK150WK150 WK151WK151 WK155WK155 WK156WK156. But its not expanding like i mentioned. Please help me. Thank you.

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

[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 & Z & Txt & Z
    Next
  Next
  Mystr = Trim(Mystr)
End Function[/td]
[/tr]
[/table]