Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenating adjacent cells ignoring blanks and adding a delimite
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenating adjacent cells ignoring blanks and adding a delimite
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(A1:D1,"*"), for example Bob Umlas, Excel MVP "Bob Freeman" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenating adjacent cells ignoring blanks and adding a delimite
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/Writin...ionsInVBA.aspx -- Gary''s Student - gsnu201001 "Bob Freeman" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenating adjacent cells ignoring blanks and adding a deli
Or the one he posted earler TODAY:
http://www.microsoft.com/office/comm...395&sloc=en-us "מיכאל (מיקי) אבידן" wrote: Did you bother to read the responses to your IDENTICAL question posted TWO WEEKS ago !? Micky "Bob Freeman" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenating non adjacent cells | Excel Discussion (Misc queries) | |||
Concatenating cells but excluding blanks | Excel Discussion (Misc queries) | |||
How can I ignore blanks when concatenating cells in Excel? | Excel Discussion (Misc queries) | |||
Ignoring blanks from Column(s) | Excel Worksheet Functions | |||
Ignoring blanks | Excel Discussion (Misc queries) |