CONCAT function

PRO

The CONCAT function in Jspreadsheet Formulas Pro is a handy tool that helps you combine or join two or more pieces of text into a single string. Essentially, it merges separate text elements together. For instance, you can use CONCAT to merge a first and a last name into a full name. This function makes it easier to handle and organize your text data effectively.

Documentation

Joins two or more text strings into one string.

Syntax

CONCAT(text1, [text2], ...)

Parameter	Description
`text`	The first text string to concatenate.
`textN`	Optional. Additional text string to concatenate.

Behavior

The CONCAT function combines two or more text strings into one string. Here's how it handles different types of inputs:

Empty cells: If CONCAT function is applied on empty cells, it will simply ignore them and return the non-empty cells only. It does not produce any error or return a null string.
Text: The CONCAT function can concatenate text strings, numbers, and cell references that contain text strings.
Booleans: If CONCAT function is applied on cells containing boolean values (TRUE or FALSE), it will return the boolean values as text strings.
Errors: If one of the references in CONCAT function contains an error, the function will return that error. For instance, if you're concatenating cells A1 and A2, and if A1 contains an error, CONCAT function will return that error.

Common Errors

Error	Description
#VALUE!	This error is displayed when the CONCAT function is used with a range of cells, which is not allowed. You must explicitly reference each cell you wish to concatenate.
#REF!	This error occurs when the referenced cell is invalid. For example, if you delete a cell that is being referenced by the CONCAT function, #REF! error is displayed.

Best practices

When using CONCAT, always make sure to properly reference the cells you want to concatenate. Avoid selecting a range of cells as it would lead to a #VALUE! error.

Use the CONCAT function to combine text strings, numbers, or cell references that contain text and/or numbers.

Be mindful of the order in which you are concatenating cells, as CONCAT will combine the strings in the order they are referenced in the function.

Use delimiters like spaces, commas, etc., within your CONCAT function to make the final result more readable. For instance, you can use =CONCAT(A1, " ", B1) to add a space between the text in cell A1 and B1.

Usage

A few examples using the CONCAT function.

CONCAT("Hello", " ", "world") returns "Hello world"  
CONCAT("Mary", " had a little lamb.") returns "Mary had a little lamb."  
CONCAT("Today is ", TEXT(TODAY(), "mm/dd/yyyy")) returns a string that includes today's date

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZTUxMTY2MDEyN2I2YmQ2MzQwNThjOTdlYTA2Y2QwM2M2OTgxZjVhOTJjZjE1OWE5N2U3ZGFiZDExY2M2NzgyNjY1OWE2OTZlNjkzNWNmMDQzMjk0MGVlZjZiZDM5MjU2N2Y2ZDliMTkyMGJjNDYwNjc1ZDk0NTc4NjdkMjU2OTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pOVGcxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "John",
        "Smith",
        "=CONCAT(A1,\" \",B1)"
    ],
    [
        "Sarah",
        "Johnson",
        "=CONCAT(A2,\" \",B2)"
    ],
    [
        "Mike",
        "Davis",
        "=CONCAT(A3,\" \",B3)"
    ],
    [
        "Hello",
        "World",
        "=CONCAT(A4,\" \",B4)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('ZTUxMTY2MDEyN2I2YmQ2MzQwNThjOTdlYTA2Y2QwM2M2OTgxZjVhOTJjZjE1OWE5N2U3ZGFiZDExY2M2NzgyNjY1OWE2OTZlNjkzNWNmMDQzMjk0MGVlZjZiZDM5MjU2N2Y2ZDliMTkyMGJjNDYwNjc1ZDk0NTc4NjdkMjU2OTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pOVGcxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "John",
        "Smith",
        "=CONCAT(A1,\" \",B1)"
    ],
    [
        "Sarah",
        "Johnson",
        "=CONCAT(A2,\" \",B2)"
    ],
    [
        "Mike",
        "Davis",
        "=CONCAT(A3,\" \",B3)"
    ],
    [
        "Hello",
        "World",
        "=CONCAT(A4,\" \",B4)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('ZTUxMTY2MDEyN2I2YmQ2MzQwNThjOTdlYTA2Y2QwM2M2OTgxZjVhOTJjZjE1OWE5N2U3ZGFiZDExY2M2NzgyNjY1OWE2OTZlNjkzNWNmMDQzMjk0MGVlZjZiZDM5MjU2N2Y2ZDliMTkyMGJjNDYwNjc1ZDk0NTc4NjdkMjU2OTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pOVGcxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "John",
        "Smith",
        "=CONCAT(A1,\" \",B1)"
    ],
    [
        "Sarah",
        "Johnson",
        "=CONCAT(A2,\" \",B2)"
    ],
    [
        "Mike",
        "Davis",
        "=CONCAT(A3,\" \",B3)"
    ],
    [
        "Hello",
        "World",
        "=CONCAT(A4,\" \",B4)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZTUxMTY2MDEyN2I2YmQ2MzQwNThjOTdlYTA2Y2QwM2M2OTgxZjVhOTJjZjE1OWE5N2U3ZGFiZDExY2M2NzgyNjY1OWE2OTZlNjkzNWNmMDQzMjk0MGVlZjZiZDM5MjU2N2Y2ZDliMTkyMGJjNDYwNjc1ZDk0NTc4NjdkMjU2OTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pOVGcxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "John",
        "Smith",
        "=CONCAT(A1,\" \",B1)"
    ],
    [
        "Sarah",
        "Johnson",
        "=CONCAT(A2,\" \",B2)"
    ],
    [
        "Mike",
        "Davis",
        "=CONCAT(A3,\" \",B3)"
    ],
    [
        "Hello",
        "World",
        "=CONCAT(A4,\" \",B4)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub