PDA

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



samueljack
04-28-2009, 02:20 AM
Hi,

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

Thanks

georgiboy
04-28-2009, 02:42 AM
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
04-28-2009, 05:30 AM
Thanks a lot that works perfectly

oleneazer
05-01-2009, 04:38 AM
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
05-01-2009, 08:44 PM
Thanks a lot for showing interest. Thank you.