Separate Text Results
 

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

Separate Text Results
 

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