VBA Splitting the text 1-8 to (1,2,3,4,5,6,7,8)

[samueljack]

Hi,

Can you help to split a cell value from 1-8 to 1,2,3,4,5,6,7,8

Thanks

[georgiboy]

You could use a custom function like this...

Function TextSplit(rCell As Range)
Dim l As Long
Dim r As Long

l = Left(rCell.Value, WorksheetFunction.Find("-", rCell.Value) - 1)
r = Right(rCell.Value, Len(rCell.Value) - WorksheetFunction.Find("-", rCell.Value))

For x = l To r
If x <> r Then
TextSplit = TextSplit & x & ","
Else
TextSplit = TextSplit & x
End If
Next x

End Function

And use it with the formula..

=TextSplit(A1)

Hope this helps

[samueljack]

Thanks a lot that works perfectly

[oleneazer]

Hi,

An another "similar" solution :


Function csplit(k As Range)
Dim i&, n&
n = Split(k, "-")(1)
For i = 1 To n
csplit = IIf((i <> n), csplit & i & ",", csplit & i)
Next i
End Function
Bye

[samueljack]

Thanks a lot for showing interest. Thank you.