Concatenate and Format Cells Cleanly (Custom Excel Function)

Problem:

You have many cells in a spreadsheet row that you would like to merge into a single cell. At the same time you would like to divide the values with a uniform separator so that you can generate an easily readable report, or generate output that you can feed to another application. You would like to skip blank cells.

One possible use case is a spreadsheet where you have a single Millennium bibliographic record number, but with one to many variable fields that are associated with that bib number, as would be the case with call numbers for all attached items.

[For a straightforward way to clean up concatenated cells, you might be able to use the process described here.]

Solution:

This approach defines a custom Excel function that you can use like an existing Excel function.

Process:

Open the spreadsheet that you would like to work on.

Press <Alt><F11> to open the Microsoft Visual Basic For Applications Editor.

Insert a new module (Insert>>Module).

Copy and paste the following text into the Module window to define a new custom function named "MyMerge." Sometimes you may pick up unseen extraneous web formatting if copying from this page, so you may need to remove the formatting. One way to do this easily is to open a new Notepad document, paste in the copied text, highlight and copy the Notepad text, then paste it into Excel. 

Function MyMerge(Rng As Range)
For Each Cell In Rng
If Cell.Value <> "" Then Temp = Temp & Cell.Value & " | "
Next Cell
Temp = Mid(Temp, 1, Len(Temp) - 3)
MyMerge = Temp
End Function

The module window will look something like this:

Return to your spreadsheet.

Select a cell in an empty column.

Call your custom funtion as if it were any other Excel function, with an equal sign followed by the function name. In this case you'd enter the text:

=MyMerge(C1:Z1) [Substitute the starting cell in your row to be merged for "C1" and the ending cell of your range for "Z1".]

Hit <Enter> to see your function in action. You can copy this cell down through the rest of your spreadsheet row, and each row will concatenate into the cell in this column.

If you'd like to save the function along with your spreadsheet data, save your spread as a macro-enabled workbook, file extension xlsm.

Modifications:

As written this function separates your values with <space><pipe><space>. To use alternate separaters, replace the " | " at the end of the third row of the function with whatever you'd like to use for the separators. Your values must be within quote marks as you see here. For example, to separate your values with <comma><space> modify the third row to:

If Cell.Value <> "" Then Temp = Temp & Cell.Value & ", "

Or, to separate your cells with <space><semicolon><space> use:

If Cell.Value <> "" Then Temp = Temp & Cell.Value & " ; " 

Acknowledgements:

This work is based, in-part, on a post by user Cozmosis on Yahoo Answers: https://answers.yahoo.com/question/index?qid=20081009120447AAImye4