TEXTAFTER function

PRO

The TEXTAFTER function in Jspreadsheet Formulas Pro is used to extract a part of a text string that comes after a specified character or string. For instance, if you have a cell with the text "hello-world" and you use the TEXTAFTER function with "-" as the specified character, the function will return "world". This can be especially useful when you need to separate information in a cell based on specific delimiters or separators.

Documentation

Returns the substring of text after a specified character or string.

Syntax

TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Parameter	Description
`text`	The text from which to extract the substring.
`delimiter`	The character or string after which to start extracting the substring.
`[instance_num]`	Optional. The instance number of the delimiter to use if there are multiple occurrences. Default is 1.
[match_mode]	Optional. Defines case sensitivity. 0 = case-sensitive (default), 1 = case-insensitive.
[match_end]	Optional. Determines whether to search from the end of the text. FALSE = from start (default), TRUE = from end.
`[if_not_found]`	Optional. The value to return if the delimiter is not found in the text. Default is an empty string.

Behavior

The TEXTAFTER function is used to extract the substring that comes after a specific character or string in a given text string.
It is case-sensitive and treats lower-case and upper-case letters as distinct characters.
If the given text string is empty, TEXTAFTER will return an empty string.
If the delimiter is not found in the text string, TEXTAFTER will return an empty string.
returns the substring after the first occurrence of the delimiter, but you can use [instance_num] to specify a different occurrence
TEXTAFTER will treat boolean values (TRUE or FALSE) as text strings. If used as a delimiter, TRUE is treated as the string "TRUE" and FALSE as "FALSE".
If the input is a numerical value, TEXTAFTER will treat it as a text string and perform the operation.

Common Errors

Error	Description
#VALUE!	This error occurs if the provided arguments are not valid, such as if the first argument is not a text string or if the delimiter is not a text string.
#N/A	This error occurs if the delimiter is not found in the text string.

Best practices

Always make sure the input is a text string. If you're using a cell reference, ensure that it contains text and not numerical or error values.

Be aware that TEXTAFTER is case-sensitive. If your delimiter is not being found, check that the case matches exactly with the delimiter in the text string.

If your text string or delimiter may contain leading or trailing spaces, consider using the TRIM function to remove them before using TEXTAFTER.

If you want to extract text after the last occurrence of a delimiter, use [match_end] = TRUE

Usage

A few examples using the TEXTAFTER function.

TEXTAFTER("apple,banana,cherry", ",") returns "banana,cherry"  
TEXTAFTER("Hello World", "World") returns ""  
TEXTAFTER("John Smith", " ") = "Smith"  
TEXTAFTER("a-b-c-d", "-", 2) returns "c-d"  
TEXTAFTER("apple|banana|cherry", "/", , , , "Not Found") returns "Not Found"

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('MmI3Yjg4NmM5MDRlMGVmY2U3MDBmNjg5MDE2ZWRkZWNlNTM0MmVjN2E3NDljNzA5MjI4Y2M0NTIyMWQzNDM4Mzc1ZTk1OWIxNjJiMzg4ZGNjZmY3OGY0MWNlZDk1NTU5YWU3Yzk4ODFlNWNjYTI0NGFhMTg5Nzk1M2RkODc4MjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNREk1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Email Address",
        "Domain",
        "Username"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A2,\"@\")",
        "=TEXTAFTER(A2,\"@\",,-1)"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A3,\"@\")",
        "=TEXTAFTER(A3,\"@\",,-1)"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A4,\"@\")",
        "=TEXTAFTER(A4,\"@\",,-1)"
    ]
]
  }]
});
</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('MmI3Yjg4NmM5MDRlMGVmY2U3MDBmNjg5MDE2ZWRkZWNlNTM0MmVjN2E3NDljNzA5MjI4Y2M0NTIyMWQzNDM4Mzc1ZTk1OWIxNjJiMzg4ZGNjZmY3OGY0MWNlZDk1NTU5YWU3Yzk4ODFlNWNjYTI0NGFhMTg5Nzk1M2RkODc4MjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNREk1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Email Address",
        "Domain",
        "Username"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A2,\"@\")",
        "=TEXTAFTER(A2,\"@\",,-1)"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A3,\"@\")",
        "=TEXTAFTER(A3,\"@\",,-1)"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A4,\"@\")",
        "=TEXTAFTER(A4,\"@\",,-1)"
    ]
];

    // 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('MmI3Yjg4NmM5MDRlMGVmY2U3MDBmNjg5MDE2ZWRkZWNlNTM0MmVjN2E3NDljNzA5MjI4Y2M0NTIyMWQzNDM4Mzc1ZTk1OWIxNjJiMzg4ZGNjZmY3OGY0MWNlZDk1NTU5YWU3Yzk4ODFlNWNjYTI0NGFhMTg5Nzk1M2RkODc4MjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNREk1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Email Address",
        "Domain",
        "Username"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A2,\"@\")",
        "=TEXTAFTER(A2,\"@\",,-1)"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A3,\"@\")",
        "=TEXTAFTER(A3,\"@\",,-1)"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A4,\"@\")",
        "=TEXTAFTER(A4,\"@\",,-1)"
    ]
]

        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('MmI3Yjg4NmM5MDRlMGVmY2U3MDBmNjg5MDE2ZWRkZWNlNTM0MmVjN2E3NDljNzA5MjI4Y2M0NTIyMWQzNDM4Mzc1ZTk1OWIxNjJiMzg4ZGNjZmY3OGY0MWNlZDk1NTU5YWU3Yzk4ODFlNWNjYTI0NGFhMTg5Nzk1M2RkODc4MjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNREk1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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: [
    [
        "Email Address",
        "Domain",
        "Username"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A2,\"@\")",
        "=TEXTAFTER(A2,\"@\",,-1)"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A3,\"@\")",
        "=TEXTAFTER(A3,\"@\",,-1)"
    ],
    [
        "[email protected]",
        "=TEXTAFTER(A4,\"@\")",
        "=TEXTAFTER(A4,\"@\",,-1)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub