# The Join Function

The Join function concatenates a bunch of strings in an array into one long string. I rarely use this function, but maybe I should start, as JP suggests. I wondered if it was really that much faster.

The next time I need to concatenate 100,000 characters will be the first time, but I’ll be sure to use Join. When I changes lBLOCK to 1,000, I get this.

## 10 thoughts on “The Join Function”

1. dermotb says:

this seems to be just as fast as Join (I’m not saying it’s better, just different)

s = String(lLOOP, “|”)
For i = 1 To lLOOP
Mid\$(s, i, 1) = “a”
Next i

2. Tom says:

I noticed that the outputs are not the same. Concetante does not have a space in the output. Example strings are shown below.

Concetante: 1831
aaaaaaaaaaaaaaaaa

Join: 62
a a a a a

Preserve: 118
a a a a a a a a

Block: 71
a a a a a a

3. JP says:

Why do you use an API instead of the Timer function? Just curious.

4. Pete C says:

What’s “Concetante” then?

5. Well, what do you know. Join’s delimiter defaults to a space. I had no idea.

I heard the API was more precise, but I don’t know that for certain.

6. Rick Rothstein (MVP - Excel) says:

@dermotb,

You posted this…

s = String(lLOOP, “|”)
For i = 1 To lLOOP
Mid\$(s, i, 1) = “a”
Next i

If all you are doing is creating multiple copies of the letter “a”, why create multiple copies of the vertical bar first only to replace them later on, just create multiple copies of the letter “a” directly…

s = String(lLoop, “a”)

I would also note that if you wanted to do your replacement later on, why use a loop when you can do this instead…

s = String(lLoop, “|”)
…..
…..
s = Replace(s, “|”, “a”)

and avoid the loop altogether. Building on this idea, you can use the Space function (you could also use the String function if you wanted to type more characters for some reason) and the Replace function, in combination, in order to produce multiple copies of text strings containing more than one character (the String function is limited to repeating single characters, so it has no advantage over using the Space function). For example, let’s say you wanted to produce the text string “ABCABCABC…ABC” where the ABC is repeated, say, 20 times; you can do it this way…

s = Replace(Space(20), ” “, “ABC”)

7. fzz says:

Concatenation stinks when everything to be concatenated is already in one 1-D array, but mashing many separate strings in different variables into a single array just so one may use Join could be just as inefficient.

If it’s the string allocations rather than the string copying that’s the performance drag, it’s best to allocate one huge string buffer and fill it using Mid\$(..) = .., as dermotb suggests. That’s the classic approach, and probably close to how Join works. The problem with Join is its single data argument syntax.

8. dermotb says:

Rick, naturally if all the characters were the same you’d use STRING. However, I assumed that Dick knew this too, and that he only used the same character for testing purposes, and that in practice they would be different.

9. I do agree with Dermotb that if all the characters are the same, we should use STRING. I think whether to use concatenation or join is also depends on the situation. If you have an array of string, especially the strings are long, I think join could be faster. It also depends on how many element you have, i guess :)

10. hans schraven says:

If you want to populate an array with the same multi-character string, an alternative can be:

sq = ([transpose(left(“ABCDEFG”&row(1:400),7))]

if you want to join the array to 1 string without separating spaces:

c01=Join([transpose(left(“ABCDEFG”&row(1:400),7))],“”)

Posting code? Use <pre> tags for VBA and <code> tags for inline.