Excel - A Better Concatenate User Defined Function - Episode 1375

4,052 views · Published 4 April 2011 · 5:05 · Indexed 12 November 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial - A Better Concatenate User Defined Function.

Welcome back to the Learn Excel Podcast! In this episode, I, Bill Jelen from MrExcel, will be sharing a user-defined function that will make your life easier when it comes to concatenating data in Excel. I recently encountered a problem while working on a project with Mike Girvin's "Slaying Excel Dragons" book and accompanying DVD. I needed to list all 53 video topics in a box, separated by bullet characters. Mike had a list in Word, but I wanted to use Excel to solve the problem.

My natural inclination was to use Excel, but I quickly realized that the CONCATENATE function and ampersand symbol were not going to cut it. It would be a tedious and time-consuming process to specify each cell individually. This has been a frustration of mine for a long time, and I finally decided to switch to VBA to solve the issue. It turned out to be a simple solution, and I created a user-defined function called "Join" that accepts a range and a character to put between each cell.

To use this function, simply open the VBA editor (Alt+F11), insert a module, and type "Function Join." The first variable is the range you want to join, and the second is the character you want to use between each cell. I used the MID function to isolate the bullet character from my Word list and the CODE function to determine its code. Then, I used the new user-defined function to join all the cells in my range, separated by the bullet character.

This function saved me a lot of time and frustration, and I hope it will do the same for you. And speaking of time-saving tools, have you checked out Mike's DVD yet? It's a fantastic resource for those who prefer to learn by watching rather than reading. With 53 videos covering everything from pivot tables to VLOOKUPs, it's a comprehensive guide to all things Excel. You can find it at MrExcel.com.

Thank you for tuning in to this episode of the Learn Excel Podcast. I hope you found this user-defined function helpful and that you'll give it a try in your own projects. Don't forget to check out Mike's DVD and all the other great resources available at MrExcel.com. See you next time for another netcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) The problem with concatenation
(00:17) Using Excel to solve the problem
(01:00) Switching to VBA for a better solution
(02:06) Creating a custom function in VBA
(03:09) Isolating the bullet character
(03:44) Final result using the custom function
(04:12) Using the solution in InDesign
(04:32) Promotion for Mike's DVD on Excel tutorials
(04:55) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Concatenate in Excel
Excel bullet character
Excel CHAR function
Excel CODE function
Excel concatenate function
Excel data import from Word
Excel text to columns
Excel VBA for concatenation
Excel VBA Insert Module
Excel VBA Join function
Learn excel from MrExcel Podcast
MrExcel Podcast Episode 1375


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153054/

Microsoft Excel has a CONCATENATE function that does not accept a range.
Today, in Episode #1375, Why doesn't the =CONCATENATE function accept a range? Why can't you specify a separator character? It is not that hard. Today's episode shows a simple macro using a defined function that would solve the problem.

More from this channel