CODEWITHSUNDEEP
excelvbaconcatenationuser-defined-functionscustom-functions-excel
Aija
Aija

Reputation: 9

Concatenate function in Excel

I want to concatenate strings from different cells and achieve the shown result without typing the concatenate formula over and over but only one function with VBA/UDF, to get different results based on the chosen cells.

The result I want to achieve is under the Result cell

Upvotes: 0

Views: 138

Answers (3)

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 0

Dominique
Dominique

Reputation: 17493

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

Excel screenshot

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.

Upvotes: 1

taller
taller

Reputation: 18778

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

Upvotes: 0

Related Questions