View Full Version : Solved: combining lists into a string

11-12-2007, 08:32 AM
i was wondering if there is a way to take a range of cells and turn them into a string with the same string between each cell value. i realize this is a rather easy thing to do in code, but is there a way to do it live on the sheet?


1 "a" " f "
2 "b"
3 "c"
4 "d"

take colume A and combind with B1 as the spacer resulting the the string "a f b f c f d"

11-12-2007, 09:53 AM
=A1&" "&B1&" "&A2&" "&B1&" "&A3&" "&B1&" "&A4
Code is better though as this is quite hard to see and more difficult to maintain.

11-12-2007, 11:02 AM
i was hoping excel had a function where all you had to do was pass it the two ranges and it would give you the output. if that dosen't exist then i will definetly do it in code.

11-12-2007, 11:40 AM
Perhaps a UDF like this.
Function cocatinateWithDelimiter(delimiter As String, ParamArray subStrings() As Variant) As String
Dim xVal As Variant, oneCell As Range
For Each xVal In subStrings
Select Case TypeName(xVal)
Case "String", "Byte", "Integer", "Long", "Single", "Double"
cocatinateWithDelimiter = cocatinateWithDelimiter & delimiter & CStr(xVal)
Case "Range"
For Each oneCell In xVal
cocatinateWithDelimiter = cocatinateWithDelimiter & delimiter & CStr(oneCell.Value)
Next oneCell
End Select
Next xVal
cocatinateWithDelimiter = Mid(cocatinateWithDelimiter, Len(delimiter) + 1)
End Function

11-12-2007, 01:38 PM
Thank you all for the help. i guess i am doing it in code.