Creating Custom Functions in Excel to Concatenate Strings and Use Them Across All Files

Excel is a versatile tool that allows users to manipulate data efficiently. One of the powerful features of Excel is the ability to create custom functions (User Defined Functions or UDFs) to perform specific tasks, such as concatenating strings. In this blog post, we will go through the steps to create a custom function that concatenates strings and how to use it across different Excel files.

What Are Custom Functions?

Custom functions allow you to extend Excel's capabilities by writing your own formulas. You can create functions that perform calculations or manipulate data in ways that built-in functions may not cover.

Step-by-Step Guide to Create a Custom Concatenate Function

Step 1: Open the Visual Basic for Applications (VBA) Editor

1. Open Excel.

2. Press ALT + F11 to open the VBA editor.

Step 2: Insert a New Module

1. In the VBA editor, right-click on any item in the Project Explorer.

2. Select Insert > Module.

Step 3: Write Your Custom Concatenate Function

1. In the new module, write your custom function using the following format:

Function C_Concatenate(ByVal mask As String, ParamArray tokens()) As String

    Dim counter As Long

    For counter = 0 To UBound(tokens)

        mask = Replace(mask, "{" & counter & "}", tokens(counter))

    Next

    

    MyInterpolation = mask

End Function

We can name the method anything we like. I recommend prefixing the methods with something common. This will help in finding your custom methods easy.  

Step 4: Save Your Workbook

1. Save your workbook as a Macro-Enabled Workbook (.xlsm format).

   
   

Using Your Custom Concatenate Function

Step 1: Use the Custom Function in Your Worksheet

1. Go back to your Excel worksheet.

2. In any cell, use your custom function like this:

   =C_Concatenate("Test {0} {1} ", "Hello", "World")

Step 2: Use Your Custom Function in Other Files

To use your custom function in other Excel workbooks, you can either copy the module or create an Excel Add-in.

Option 1: Copy the Module to Other Workbooks

1. Open the workbook where you want to use the function.

2. Open the VBA editor (ALT + F11).

3. Copy the module with your custom function by right-clicking on it and selecting Export File.

4. In the new workbook, right-click Modules in the Project Explorer and select Import File. Choose the exported module.

   
   

Option 2: Create an Excel Add-in

1. Open the workbook with your custom function.

2. Save it as an Excel Add-in (.xlam format) by selecting File > Save As and choosing the add-in format.

3. Install the add-in by going to File > Options > Add-ins, selecting Excel Add-ins from the Manage dropdown, and clicking Go.

4. In the Add-ins dialog, click Browse and select your .xlam file.

Step 3: Use the Function in Other Workbooks

Once the add-in is installed, you can use your custom function in any Excel file by typing it in as you would with any built-in function.

Conclusion

Creating custom functions in Excel to concatenate strings can save you time and enhance your data manipulation capabilities. By following the steps outlined in this post, you can easily create, save, and use your custom concatenate function across multiple Excel files. Enjoy streamlining your Excel tasks!

Extract All sheets inside a folder to individual Excel File

It was supposed to be a peaceful Friday night in office. As I was about to leave the office on time, I was bombed with a very stupid task. Actually, We had some 8-10 files related to some important data and each Excel file contained 10-15 sheets. The task was to extract all those sheets into individual excel file. For me, to do this manually was like digging the well with spoon. It would have taken hours to complete the task. So I thought of trying hands on VBA. It was pretty easy and quick to do this with VBA and within half hour, I was done with my code and task to Extract sheet to files. (Note that I have written the code from scratch. I found below mentioned code segments on some sites and adjusted it to meet my goal.) Lets see how we did that.

First, I tried to extract the excel sheets for the single Excel file.(Please note that i have written this code. Copied it from this location)

Sub Splitbook()

Dim xPath As String

'Pick current excel path
xPath = Application.ActiveWorkbook.Path

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Loop through all sheets
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    
    'Save excel file with sheet name
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Well, that works pretty straight. It extracted all the sheets into files(naming sheet name as file name) Now, the next challenge was to loop through all the files in a folder, process each file and loop through its sheets. For that, I created a new Macro-supported file (.xlsm) file. Pressed [Alt] + [F11]. This opens up the code window. In that, go to Menu -> Insert -> Module and pasted these lines -

Sub SplitAllBook()

Dim fso As Object
Dim xPath As String
Dim wbk As Workbook
Dim Filename As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

xPath = "YOUR_SOURCE_FOLDER_NAME_HERE"

'Pick only ".xlsx" files. In case you want to process older version of files, please change the extension to .xls
Filename = Dir(xPath & "*.xlsx")

'Loop through all files
Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN

    Set wbk = Workbooks.Open(Filename:=xPath & Filename)

    'Use fso to remove the extension from file name. We used this name to Name excel file.
    Set fso = CreateObject("scripting.filesystemobject")
    Dim tempFileName As String
    tempFileName = fso.GetBaseName(Filename)
    
    'Loop through all sheets
    For Each xWs In wbk.Sheets

        xWs.Copy
        'I have used naming convention as - OriginalFileName_SheetName.xlsx to avoid name conflict. You can put any logic you want.
        Application.ActiveWorkbook.SaveAs Filename:="YOUR_DESTINATION_FOLDER_NAME_HERE\" & tempFileName & "_" & xWs.Name & ".xlsx"
        Application.ActiveWorkbook.Close False

    Next

    wbk.Close True
    Filename = Dir

Loop

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

With that, we are done. Run you macro and you should get all the sheets created as new excel files in the destination folder.

Tip -
 
I faced a couple of problems while running the code.
1. Unique Name for files - Figure out a way to provide a unique name to your file or else the macro will throw the error.

2. Folder Path - Define your folder path correctly with all the "\" etc wherever required. In case a problem occurs, a quick debug will solve it easily.

Sample Code

Thank you.