FIND function
PRO
BASIC
The FIND
function in Jspreadsheet Formulas Pro is a tool that allows you to locate the position of a specific sequence of characters (a string) within a larger string. It's important to note that this function is case sensitive, meaning it distinguishes between upper and lower case letters. The search process begins from the left side of the string, moving towards the right.
Documentation
Returns the position of a string within another, but allowing for case sensitivity. Search starts from the left hand side of the string.
Category
Text
Syntax
FIND(find_text, within_text, start_num)
Parameter | Description |
---|---|
find_text |
The text to find. |
within_text |
The text to search within. |
start_num |
Optional. The position to start the search. If omitted, search starts at the beginning of the string. |
Behavior
The FIND
function is used to find the starting position of a specified text string within another text string. Its syntax is FIND(find_text, within_text, [start_num])
. The find_text
is the text you want to find, within_text
is the text containing the text you want to find, and start_num
(optional) is the position in the within_text
where you want to start the search.
FIND
is case-sensitive.- It returns the position of the first character of
find_text
insidewithin_text
. - It will return an error if the
find_text
is not found within thewithin_text
. - If
start_num
is not specified, the function begins the search from the first character of thewithin_text
. - In case of empty cells,
FIND
will return an error. FIND
does not support wildcards like "?" and "*".- The function does not handle booleans or errors. If
find_text
orwithin_text
is a boolean or error,FIND
will return an error.
Common Errors
Error | Description |
---|---|
#VALUE! | Returned when the find_text is not found, when start_num is out of range, when inputs are booleans/errors, or when find_text is empty. |
Best practices
- Always ensure that the
find_text
is properly formatted and is present in thewithin_text
to avoid #VALUE! errors.- Be aware that
FIND
is case-sensitive. If you want a case-insensitive function, useSEARCH
instead.- Be careful when using
start_num
. Make sure it is within the length ofwithin_text
.- Remember that
FIND
does not support wildcards. If you need to use wildcards, useSEARCH
instead.
Usage
A few examples using the FIND function.
FIND("o","Hello World") → Returns 5 (position of first lowercase "o")
FIND("O","Hello World") → Returns #VALUE! (no uppercase "O" found)
FIND("l","Hello World",3) → Returns 3 (first "l" found starting at position 3)
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('OGFjMTAyMjQyODk1ZTNmYzY4ZThmMjMzMjNjOTBhYTNiMGFkZTlmZjFmODliMzAxODNjMmRmN2M5N2YwYTI1ZTk4NmIwNjE2MmRmNzI4ZjI5YzY4OGViM2FhMzMxOGMyOWQ0MTJlZTZmNjNkM2E0ZTJjNjcxOTRhZWM0ZWVlNWEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Email Address",
"Search For",
"Position"
],
[
"[email protected]",
"@",
"=FIND(B2,A2)"
],
[
"[email protected]",
".",
"=FIND(B3,A3)"
],
[
"[email protected]",
"com",
"=FIND(B4,A4)"
],
[
"[email protected]",
"L",
"=FIND(B5,A5)"
]
]
}]
});
</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('OGFjMTAyMjQyODk1ZTNmYzY4ZThmMjMzMjNjOTBhYTNiMGFkZTlmZjFmODliMzAxODNjMmRmN2M5N2YwYTI1ZTk4NmIwNjE2MmRmNzI4ZjI5YzY4OGViM2FhMzMxOGMyOWQ0MTJlZTZmNjNkM2E0ZTJjNjcxOTRhZWM0ZWVlNWEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Email Address",
"Search For",
"Position"
],
[
"[email protected]",
"@",
"=FIND(B2,A2)"
],
[
"[email protected]",
".",
"=FIND(B3,A3)"
],
[
"[email protected]",
"com",
"=FIND(B4,A4)"
],
[
"[email protected]",
"L",
"=FIND(B5,A5)"
]
];
// 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('OGFjMTAyMjQyODk1ZTNmYzY4ZThmMjMzMjNjOTBhYTNiMGFkZTlmZjFmODliMzAxODNjMmRmN2M5N2YwYTI1ZTk4NmIwNjE2MmRmNzI4ZjI5YzY4OGViM2FhMzMxOGMyOWQ0MTJlZTZmNjNkM2E0ZTJjNjcxOTRhZWM0ZWVlNWEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Email Address",
"Search For",
"Position"
],
[
"[email protected]",
"@",
"=FIND(B2,A2)"
],
[
"[email protected]",
".",
"=FIND(B3,A3)"
],
[
"[email protected]",
"com",
"=FIND(B4,A4)"
],
[
"[email protected]",
"L",
"=FIND(B5,A5)"
]
]
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('OGFjMTAyMjQyODk1ZTNmYzY4ZThmMjMzMjNjOTBhYTNiMGFkZTlmZjFmODliMzAxODNjMmRmN2M5N2YwYTI1ZTk4NmIwNjE2MmRmNzI4ZjI5YzY4OGViM2FhMzMxOGMyOWQ0MTJlZTZmNjNkM2E0ZTJjNjcxOTRhZWM0ZWVlNWEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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",
"Search For",
"Position"
],
[
"[email protected]",
"@",
"=FIND(B2,A2)"
],
[
"[email protected]",
".",
"=FIND(B3,A3)"
],
[
"[email protected]",
"com",
"=FIND(B4,A4)"
],
[
"[email protected]",
"L",
"=FIND(B5,A5)"
]
]
}]
});
}
}