Concatenate in Excel and Google Sheets — Top Methods Explained

Concatenate Functions Across Languages: SQL, JavaScript, and MoreConcatenation — the operation of joining two or more strings (or other sequence-like values) end-to-end — is a basic, ubiquitous task in programming and data work. Different languages and systems provide different functions, operators, and idioms to concatenate values, and each environment has its own quirks, performance considerations, and best practices. This article surveys concatenation across several popular languages and tools — SQL, JavaScript, Python, Java, C#, Bash/shell, Excel/Google Sheets, and a brief note on Unicode and performance — with concrete examples and tips for common pitfalls.

---

What concatenation means in practice

Concatenation typically takes two or more inputs and creates a single output that is their sequence-wise combination. For text, concatenation usually means joining characters; for arrays or lists, it means joining elements; for binary data, it may mean joining bytes. While conceptually simple, concatenation intersects with type conversion, null/undefined handling, encoding (especially Unicode), and performance characteristics (copying vs. streaming).

---

SQL

Concatenation in SQL varies by dialect.

• SQL Standard / PostgreSQL:
  • Operator: ||
  • Example: SELECT ‘Hello’ || ‘ ’ || ‘World’ AS greeting;
  • Handles NULL by producing NULL when any operand is NULL (unless coalesced).
  • To treat NULL as empty string: COALESCE(column, “)
• MySQL:
  • Function: CONCAT(), CONCAT_WS()
  • Example: SELECT CONCAT(‘Hello’, ‘ ‘, ‘World’) AS greeting;
  • CONCAT returns NULL if any argument is NULL; CONCAT_WS skips NULL values (and takes the first argument as separator).
  • Example: SELECT CONCAT_WS(’ ‘, ‘Hello’, NULL, ‘World’) -> ‘Hello World’
• SQL Server (T-SQL):
  • Operator: +
  • Example: SELECT ‘Hello’ + ‘ ’ + ‘World’ AS greeting;
  • + will treat NULL as NULL (resulting in NULL). Use CONCAT() (available in recent versions) to treat NULL as empty.
  • Example: SELECT CONCAT(‘Hello’, ‘ ‘, NULL, ‘World’) -> ‘Hello World’
• Oracle:
  • Operator: ||
  • Example: SELECT ‘Hello’ || ‘ ’ || ‘World’ FROM dual;
  • NVL(column, “) can be used to replace NULLs.

Performance and tips:

• Avoid repeated concatenation in loops in large result sets; prefer building strings with functions that can aggregate (e.g., STRING_AGG in PostgreSQL, GROUP_CONCAT in MySQL) or use procedural language features to build efficiently.
• Be mindful of implicit type conversion (numbers/dates to strings) — use explicit CAST/TO_CHAR formats when needed.

---

JavaScript

JavaScript offers several straightforward ways to concatenate strings:

• + operator:
  • Example: let s = ‘Hello’ + ‘ ’ + ‘World’;
  • Automatically converts non-strings (numbers, booleans) to strings.
• Template literals (ES6):
  • Example: let s = ${firstName} ${lastName}; — recommended for readability and embedding expressions.
• Array join:
  • Example: [‘Hello’, ‘World’].join(’ ‘)
  • Useful when concatenating many parts or conditional pieces.
• String.prototype.concat:
  • Example: ‘Hello’.concat(’ ‘, ‘World’) — less commonly used.
• Handling null/undefined:
  • null and undefined are converted to ‘null’ and ‘undefined’ when coerced to strings; guard with nullish coalescing: ${x ?? ''}

Performance:

• For many small concatenations, + and template literals are fine; for very large or many concatenations (especially in older engines), building arrays and joining can be faster.
• Avoid repeated string concatenation in hot loops if performance matters; consider accumulating pieces and using join or streams.

---

Python

Python provides several idioms depending on the use case:

• + operator:
  • ‘Hello’ + ‘ ’ + ‘World’
  • Simple but creates new string objects each time.
• join:
  • ’ ‘.join([a, b, c]) — the recommended approach when concatenating many strings.
  • join takes an iterable and is efficient because it calculates size once.
• f-strings (Python 3.6+):
  • f”{first} {last}” — concise and efficient for formatting.
• % formatting and str.format:
  • Older styles: ‘%s %s’ % (a, b) or ‘{} {}’.format(a, b)
• Bytes vs. str:
  • For binary data, use b”.join([…]) and be careful about mixing bytes and str.
• None handling:
  • None must be converted explicitly (str(x) or conditional) — otherwise TypeError for join if iterable contains non-str.

Performance tip:

• Use join for concatenating variable-length lists of strings; use io.StringIO for programmatically building very large strings in loops.

---

Java

Java strings are immutable; common approaches:

• + operator:
  • “Hello” + “ ” + “World” — Java compiler often converts chained + into StringBuilder operations in a single expression.
• StringBuilder / StringBuffer:
  • For loops and many concatenations, use StringBuilder (not synchronized) or StringBuffer (synchronized).
  • Example:

    
    StringBuilder sb = new StringBuilder(); sb.append("Hello").append(' ').append("World"); String s = sb.toString(); 

• String.concat():
  • “Hello”.concat(” World”)
• Handling null:
  • Calling methods on null references causes NullPointerException; use Objects.toString(obj, “”) to safely convert.
• Performance:
  • Prefer StringBuilder in loops and when building strings incrementally.

---

C

C# also uses immutable strings and provides efficient builders:

• + operator and interpolation:
  • “Hello” + “ ” + “World” or $“Hello {name}”
• String.Concat and String.Join:
  • String.Concat(a, b, c) and String.Join(“, “, collection)
• StringBuilder:
  • For many appends: var sb = new StringBuilder(); sb.Append(…);
• Null handling:
  • String.Concat treats null as empty string; the + operator will also treat null as empty in many contexts.
• Performance:
  • Use StringBuilder for heavy concatenation; String.Join for collections.

---

Bash / Shell

Concatenation in shell scripts is often just adjacency or expansion:

• Variables:
  • s=”\(a\)b” or s=”\({a}-\){b}”
• Echo/printf for output:
  • printf “%s%s ” “\(a" "\)b”
• Arrays:
  • Join with loops or IFS-based techniques for joining array elements.
• Note:
  • Be careful with word splitting and quoting; always quote variables when concatenating content that may include spaces or glob characters.

---

Excel and Google Sheets

Concatenation in spreadsheets can be done several ways:

• Ampersand (&):
  • =A1 & “ ” & B1
• CONCAT / CONCATENATE:
  • CONCAT(A1, “ “, B1) — CONCATENATE is older but still available in some versions.
• TEXTJOIN (Excel) / JOIN (Sheets):
  • TEXTJOIN(” “, TRUE, range) — can ignore empty cells.
• Handling numbers/dates:
  • Use TEXT(value, format) to format numbers/dates when concatenating.
• Empty cells:
  • Ampersand treats empty cells as empty string; functions may behave differently — TEXTJOIN has an option to ignore empties.

---

Unicode, Encodings, and Internationalization

• Always know whether your environment uses UTF-8, UTF-16, or other encodings.
• JavaScript strings are UTF-16 code units; slicing and length can be surprising with astral characters (use Array.from or spread to handle code points).
• Normalization: visually identical Unicode can have different code point sequences (NFC vs NFD). Normalize when comparing or concatenating if necessary: in JavaScript use str.normalize(), in Python use unicodedata.normalize().
• Collation vs. concatenation: joining strings doesn’t change sorting/collation — take locale into account when formatting or comparing results.

---

Performance considerations

• Repeatedly concatenating immutable strings one-by-one creates many temporary objects in most languages; use language-specific builders (StringBuilder, join, arrays + join).
• For very large output, consider streaming APIs (write directly to files or network) instead of assembling a huge in-memory string.
• Profilers can reveal whether concatenation is a bottleneck; optimize only when needed.

---

Common pitfalls and best practices

• Null/None/undefined handling: Know how your language treats nulls — explicit coalescing is often needed.
• Type coercion: Numbers, objects, and dates may implicitly become strings; prefer explicit formatting for control.
• Encoding issues: Ensure consistent encoding (UTF-8 is standard across modern stacks).
• Security: When concatenating for SQL queries or HTML, avoid direct concatenation of untrusted input — use parameterized queries and proper escaping/encoding to prevent injection.
• Readability: Prefer template literals, f-strings, or format functions for readability over long + chains.

---

Quick reference examples

• SQL (Postgres): SELECT ‘A’ || ‘B’;
• JavaScript: ${a} ${b} or [‘a’,‘b’].join(’ ‘)
• Python: ’ ‘.join([a, b]) or f”{a} {b}”
• Java: new StringBuilder().append(a).append(b).toString()
• C#: String.Concat(a, b) or new StringBuilder().Append(a).ToString()
• Bash: s=”\(a\)b”
• Excel: =A1 & “ ” & B1 or =TEXTJOIN(” “, TRUE, A1:B1)

---

Conclusion

Concatenation is simple conceptually but interacts with many language-specific behaviors: null handling, encoding, mutability, and performance. Use the idioms your environment provides (join, builders, template literals) for clarity and efficiency, and always be mindful of type conversion and security when combining strings from varied sources.