Concatenate function

Oh, man, I can’t tell how useful that concatenate function is.

One repetitive use I found is to create OR/AND conditions for Access queries. I copy and paste the field values of a column from Access, do some filtering and my conditions are ready. Then I use this concat2 function to create a string to use in my Access query.

For example, look at this screen shot:

Example of using the concat function

use of concat function

The Range A1:A4 houses the string condition I want to use in my Access query to restrict the fruits from my data. In cell B1 I have the formula

=PERSONAL.XLS!concat2(A1:A4,CHAR(34) & " or " & CHAR(34))

, and the return string from this function is listed in cell B1.

Now, all I have to do is copy and paste this in Access criteria and put a quotation mark at the beginning and at the end of this string.

I have found one more use of this when I want to store some values in an Array, using the Array function in VBA. In this example, I use a comma, given by the function CHAR(44), instead of string OR, and this function returns a string that I can use in VBA to store these values in an array using the Array function.

About the Author

The author of Tableau Data Visualization Cookbook and an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.

Leave a Reply 0 comments