Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I separate text without a space? | Excel Discussion (Misc queries) | |||
Separate text within a cell | Excel Discussion (Misc queries) | |||
How to separate numbers from text?? | Excel Discussion (Misc queries) | |||
separate text into different cells | Excel Worksheet Functions | |||
How do I separate a text value with dashes? | Excel Worksheet Functions |