Micky was correct..............change the -2 to -1 in this case.
Gord
On Wed, 15 Sep 2010 13:43:40 GMT, Katherine Berchtold
<(E-Mail Removed)> wrote:
>Hi Gord,
>
>I used the macro you posted to concatenate a range of data and exclude the blanks.
>
>This works great, the only problem is that it cuts off the last letter of the last word.
>
>Please help!
>
>Thank you.
>
>Katherine
>
>> On Tuesday, January 12, 2010 8:23 AM Bob Freeman wrote:
>
>> Hello,
>>
>> I am trying to create a result field, concatenating populated cells from the
>> previous 12 columns on that line, but excluding blank cells and putting a *
>> delimiting character between each instance - please find below a 4 column
>> example.
>>
>> ID 1 2 3 4 Result
>> Z A C D A*C*D
>> Y B C B*C
>> X A B D A*B*D
>>
>> Each of the 10,000 lines of the spreadsheet is different - there are at
>> least 5 blank cells on each line
>>
>> Any help gratefully received. I am working in Excel 2007
>>
>> Many thanks.
>>
>> Bob
>
>
>>> On Tuesday, January 12, 2010 3:33 PM ????? (????) ????? wrote:
>
>>> Try this:
>>> http://img690.imageshack.us/img690/5826/nonamee.png
>>> Micky
>>>
>>>
>>> "Bob Freeman" wrote:
>
>
>>>> On Tuesday, January 12, 2010 5:26 PM Gord Dibben wrote:
>
>>>> This UDF will concatenate a range and exclude blanks.
>>>>
>>>> Function ConCatRange(CellBlock As Range) As String
>>>> 'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
>>>> 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) - 2)
>>>> End Function
>>>>
>>>> =concatrange(range)
>>>>
>>>>
>>>> Gord Dibben MS Excel MVP
>
>
>>>>> On Wednesday, January 13, 2010 3:21 AM ????? (????) ????? wrote:
>
>>>>> I assume you meant:
>>>>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>>>> Micky
>>>>>
>>>>>
>>>>> "Gord Dibben" wrote:
>
>
>>>>>> On Wednesday, January 13, 2010 12:43 PM Gord Dibben wrote:
>
>>>>>> I meant ConCatRange = Left(sbuf, Len(sbuf) - 2)
>>>>>>
>>>>>> Try it using ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>>>>>
>>>>>> See the difference?
>>>>>>
>>>>>>
>>>>>> Gord
>>>>>>
>>>>>> <micky-a*at*tapuz.co.il> wrote:
>
>
>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>> Silverlight Custom ValidationSummary with Data Form
>>>>>> http://www.eggheadcafe.com/tutorials...data-form.aspx
Similar Threads
