Concatenating adjacent cells ignoring blanks and adding a delimite

[Bob Freeman]

Hello, I am trying to create a formula which concatenates 12 adjacent cells
in a row but excludes any blank cells and puts a delimiting character *
between each instance. Please find a 4 column example below.

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

This will be used in a 10,000 row Excel 2007 spreadsheet and each row is
potentially different - there are at least 5 blank cells on each row and
usually more.

Any help is gratefully received. Many thanks, Bob.

 

[Bob Umlas]

User-defined function:
Alt/F11; Insert/Module; put this in:

Function Cat(Rg As Range, Delimiter As String)
For Each thing In Rg
If thing.Value <> "" Then Cat = Cat & thing.Value & Delimiter
Next
Cat = Left(Cat, Len(Cat) - Len(Delimiter))
End Function

In the worksheet, enter =Cat(A11,"*"), for example
Bob Umlas, Excel MVP

 

[Gary''s Student]

Try the following User Defined Function:

Function kittenate(r As Range) As String
kittenate = ""
star = "*"
For Each rr In r
If rr.Value = "" Then
Else
If kittenate = "" Then
kittenate = rr.Value
Else
kittenate = kittenate & "*" & rr.Value
End If
End If
Next
End Function

UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function, that is =kittenate(A1:Z1)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

 

[JLatham]

Or the one he posted earler TODAY:
http://www.microsoft.com/office/com...8f1dff-7343-4f66-8530-0a0b2bf83395&sloc=en-us