Jenny

Try this UDF.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

usage is: =ConCatRange(I1:I20)

Ignores balnk cells and adds a space between each non-blank cell value.

i.e. Survey ResultsCust Focus becomes

Survey Results Cust Focus


Gord Dibben MS Excel MVP

On Fri, 6 Apr 2007 09:36:00 -0700, Jenny B.
wrote:

Good Afternoon and Happy Holiday,

I think I've finally got one that might offer at least a sort of challenge.

I have a formula already in place (that works flawlessly) that searches
Column B for a Max amount and returns a Text value to Column I if it meets
certain criteria. I have 21 rows in Column B that could theoretically return
21 Text values to Column I again if they meet the criteria.

My problem comes from the way I'm trying to extract the results. I'm using
an "&:" formula to total up the results of Column I so it will show a text
value at the bottom. Since there may be more than one word, I'm trying to
separate them without using a generic &" "& formula. I don't want to use
that because if there are only 2 values that meet the criteria, I than have
dozens of spaces before or after the word and I'm trying to merge this also
into a formatted letter I've created in another Excel portion. I've dabbled
with a Search formula, Trim Formula and can't quite seem to come up with
anything that will recognize a merged word (it can be recognized by a Caps
next to a lower case (exp. BillClinton).

Any help or thoughts would be greatly appreciated.

Thank you - Jenny B.


Column A Column I

Survey Results Survey Results
Satisfaction Index
Job Satisfaction
Cust Focus Cust Focus
___________________________________________

Merge Cell Results after I Total
Survey ResultsCust Focus