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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920