I've got a range that can be of varying size, depending on the
information entered. I'm able to strip out the blank cells so that i
am left with a range of cells that all have data in them. Now i want
to take the entries in these cells and combine them into one cell
seperated by columns. For example if i have

A
1 Word
2 Some
3 14
4 Other

then i want a cell to be Word, Some, 14, Other

All of the examples i've found through searching don't seem to be
working, any ideas?

Craig

Im getting booted from a public cpu right now. I have a custom
function to send ya. Get it to you later today.

Mark

Ok, Here it is. Just copy this into a new module. If you don't know
how to work with macros or vba or my instructions are inadequate, Email
me @ and I'll help you out.



'--------------------------------------------------------------------
' FUNCTION BUILDTEXT
' ------------------
' *** INPUT: ***
' ------------------
'
' PIECES: This is a RANGE Object. Select all of
' the cells that you want to use to
' create the final Concatenated String.
'
' optional SEPARATOR: This may be any character on the
' keyboard although most likely it will be
' a COMMA. Because of this, This parameter
' is optional. By Default a COMMA will be
' used.
'
' optional SPACECNT: This is the number of Blank Spaces that
' will appear after the SEPARATOR. By
' default only (1) Blank will follow each
' SEPARATOR.
' -------------------
' *** OUTPUT: ***
' -------------------
'
' BUILDTEXT
' This is a STRING. Each CELL's text
' in the selected range will be
' Concatenated together with the other
' CELLS' text. SEPARATORS & BlankSpaces
' will be added between each one.
' Altogether they will form the text which
' will appear in the CELL that calls this
' function.
'
'--------------------------------------------------------------------
' HOW TO USE
'--------------------------------------------------------------------
'*** EXAMPLES ****** FORMULA ****** RESULT
'--------------------------------------------------------------------
' Cell Text =BuildText(I1:I4) Aqua, Blush, Brick, Brown
' ----- -----
' I1 = Aqua =BuildText(I1:I4,",",1) Aqua, Blush, Brick, Brown
' I2 = Blush
' I3 = Brick =BuildText(I1:I4,";",2) Aqua; Blush; Brick;
Brown
' I4 = Brown
'--------------------------------------------------------------------

Public Function BuildText(Pieces As Range, _
Optional Separator As String = ",", _
Optional SpaceCnt As Integer = 1) As String

Dim strTemp As String
Dim strDel As String
Dim CellCnt As Integer
Dim rngTemp As Range
'--------------------------------------------------------------------
strDel = Separator & String(SpaceCnt, " ")
CellCnt = Pieces.Cells.Count
For Each rngTemp In Pieces

If strTemp = "" Then
'This is the first time through,only add delimeter _
with spaces
strTemp = rngTemp & strDel

Else

If CellCnt = 1 Then
'This is the last time through, only add text
strTemp = strTemp & rngTemp
BuildText = strTemp
Debug.Print "BuildText will return " & BuildText

Else
'Combine the partially constructed text, _
new text & the Delimeter with spaces
strTemp = strTemp & rngTemp & strDel

End If

End If
CellCnt = CellCnt - 1
Debug.Print "strTemp looks like this: " & strTemp
Next rngTemp

End Function

Lemme know what happens. Mark

your words are in a2 to a5

enter ", " comma and a space in cell A1

enter =A2&A1&A3&A1&A4&A1&A5 in B1
--
Greetings from New Zealand
Bill K




"Craig" wrote in message
ups.com...
I've got a range that can be of varying size, depending on the
information entered. I'm able to strip out the blank cells so that i
am left with a range of cells that all have data in them. Now i want
to take the entries in these cells and combine them into one cell
seperated by columns. For example if i have

A
1 Word
2 Some
3 14
4 Other

then i want a cell to be Word, Some, 14, Other

All of the examples i've found through searching don't seem to be
working, any ideas?