An UDF for Concatenate Function in Excel

Question

Hello I was wondering whether it is possible to achieve this result using VBA/UDF?

Basically what I wanted to do is to be able to concatenate various string from different cell and achieve the shown result without typing the concatenate formula over and over but only one function with VBA/UDF, so we can get different kind of result based on the chosen cells.

I've looked on ways on the internet by creating module but most of them is use range instead. Sorry if this is inappropriate question because I am not able to show what I have achieve as I am very new to Excel. Really appreciate your help!

Answer 1

Are you really sure you are looking for a user-defined function?
I have the impression that you are looking for absolute and relative references, as in the following screenshot, based on this formula:

=C$35&C27&C$36&D27

As you see, there's a dollar-sign in front of the row numbers for cells "C35" and "C36" (becoming "C$35" and "C$36"). Like this, when I drag the formula to the next row, those rownumbers don't change and the result stays correct.

Answer 2

A Personalized Concat Function

M365 LAMBDA Formula

• In M365, you could create and adjust the following lambda formula:

  =LAMBDA(student,year,"The student named "&student&" is now on year "&year&".")
  

  • Using the Name Manager, you can then create a name, e.g. StudY, and enter the formula in the Refers to text box.
  • Now you can simply use =StudY(C31,D27).

VBA

• In VBA, you could start with something like the following.

Function StudentYear(ByVal Student As String, ByVal Year As Long) As String
    StudentYear = "The student named " & Student _
        & " is now on year " & Year & "."
End Function

Function StudentYear2(ByVal Student As String, ByVal Year As Long) As String
    Const BEFORE As String = "The student named "
    Const BETWEEN As String = " is now on year "
    Const AFTER As String = "."
    StudentYear2 = BEFORE & Student & BETWEEN & Year & AFTER
End Function

Answer 3

Here comes a demostration snippet without any arguments validation.

Option Explicit

Function MyConcat(text1, text2) As String
    Application.Volatile
    MyConcat = Range("C35") & " " & _
        text1 & " " & Range("C36") & " " & text2
End Function