O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Concatenating Data

Problem

How can I combine text strings together?

Solution

The ampersand character (&) is the concatenation character. Here are some examples:

  • "Hello" & "Goodbye" returns "HelloGoodbye"

  • "Hello " & "Goodbye" returns "Hello Goodbye"

  • "Hello" & " " & "Goodbye" returns "Hello Goodbye"

Notice that in both the second and third examples, there's a space between Hello and Goodbye. The second example provides the space because there is a space after the Hello within the quotation marks, and the third example provides the space as an independent string that is concatenated with the two others.

Tip

The + operator can also be used for string concatenation.

Discussion

You often need to consider spaces when concatenating text. Simply combining two values may not produce the desired result—for example, concatenating George and Washington produces GeorgeWashington, which is probably not what you need. Therefore, concatenating a space between other words is a common requirement.

Usually, manually adding a space to the end of each of the first strings (for example, changing George to George) is infeasible. When processing a large number of records, this method would take an impractical amount of time, and would also be error-prone.

Continuing with this example, say you need to combine all of the first and last names in your records into single full-name values. The technique, as previously mentioned, is to concatenate a space between the first and last names. But what about any stray spaces that ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required