Efficient programming by making string concatenation convenient with VBA

background

--There are "&" and "CONCATENATE ()" in the method of string concatenation in Excel. --However, both methods require you to specify each cell individually. - =A1&A2&A3 - =CONCATENATE(A1,A2,A3) ――I can't select a range even though I have a function (what is the function really for?) --There is a site that describes VBA that makes this range selectable. ――In addition to simple string concatenation, in my case, I thought that it would be more convenient to be able to concatenate strings in the following cases, so I created a function with reference to the above site. --I've been using functions for over 5 years now, but it's very convenient and I use them quite often.

Function code

• https://github.com/9en/useful-tool/blob/master/VBA_function/ConcatenateRangeText.bas

How to create a function (How to register in Excel)

--There are many articles on the net, so please check them yourself.

Example of use

Delimiter "comma"

Excel

function

=ConcatenateRangeText_comma(B1:B3)

result

dt,product,price

Delimiter "space"

Excel

function

=ConcatenateRangeText_space(B1:E1)
=ConcatenateRangeText_space(B2:E2)

result

,SUM(CASE WHEN product = 'car' THEN 1 ELSE 0 END) AS product_car
,SUM(CASE WHEN product = 'bike' THEN 1 ELSE 0 END) AS product_bike

Delimiter "pipe"

Excel

function

=ConcatenateRangeText_separator(A1:D1,"|")
=ConcatenateRangeText_separator(A2:D2,"|")
=ConcatenateRangeText_separator(A3:D3,"|")

result

|2|SQL|
|3|Markdown|
|4|R,Python|

Delimiter "comma" quote "single quote"

Excel

function

=ConcatenateRangeText_singlequote_comma(B1:B3)

result

'dt','product','price'

Other

――We have some code similar to this.

reference

• http://www.relief.jp/itnote/archives/001342.php