SEARCH function
PRO
BASIC
The SEARCH
function in Jspreadsheet Formulas Pro is a useful tool that helps you find the starting point of a specific text string within a larger string. It begins its search from the leftmost character. If the specific text you're searching for is present, the function will give you its position in the larger string. However, if the text is not found within the larger string, it will simply return '#VALUE!'.
Documentation
Returns the starting position of a text string within another text string, starting from the leftmost character. If the text is not found, returns #VALUE!
Category
Text
Syntax
SEARCH(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 starting position of the search. Default is 1. |
Behavior
The SEARCH
function is used to find the position of a particular string within another string. The function returns the start position of the first occurrence of the search text within the text. The search is not case-sensitive.
- If the
SEARCH
function does not find the search text within the text, it returns an#VALUE!
error. - If the start number provided is less than 1, the function returns
#VALUE!
error. - If the start number provided is greater than the length of the text, the function returns
#VALUE!
error. - The function will ignore empty cells.
- The function can handle text, and it will treat numbers as text.
- The function cannot directly handle boolean values. If a boolean value is provided, it will be treated as text.
- The function is not case-sensitive, so it will treat 'A' and 'a' as the same.
Common Errors
Error | Description |
---|---|
#VALUE! |
This error is returned if the SEARCH function does not find the search text within the text, or if the start number provided is less than 1 or greater than the length of the text. |
Best practices
- Use the
ISERROR
function to handle possible errors that might occur when theSEARCH
function does not find the search text within the text.- Be mindful when using the start number argument. Make sure it is not less than 1 and not greater than the length of the text.
- Remember that
SEARCH
function treats numbers as text, so if you are trying to find a numerical value, ensure your search term is formatted as text.
Usage
A few examples using the SEARCH function.
SEARCH("blue", "The sky is blue.")
// Returns 12 because "blue" starts at the 12th character.
SEARCH("red", "The sky is blue.")
// Returns #VALUE! because "red" is not found.
SEARCH("sky", "The sky is blue.", 5)
// Returns #VALUE! because the search starts at the 5th character and "sky" occurs before that.
SEARCH("SKY", "The sky is blue.")
// Returns 5 (case-insensitive).
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('NDYxZGY0NTkwMGNjMjc2MDBmZTAzMWQ1MWJlODdkNzE2MmVlNGIxNjczNWI1YmE0NDcwNGViZWEwNGFhZDZlMWRkMGY5ZDA3MDY1Y2U0MDRiZTZmNDlkNGMxZTc4M2FjMzc5MDc1ZmY2YTZhMjNlZjZlYzgwNDkwYjBkMDE0OTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Email Address",
"Search Term",
"Position"
],
[
"[email protected]",
"@",
"=SEARCH(B2,A2)"
],
[
"[email protected]",
"business",
"=SEARCH(B3,A3)"
],
[
"[email protected]",
"xyz",
"=SEARCH(B4,A4)"
]
]
}]
});
</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('NDYxZGY0NTkwMGNjMjc2MDBmZTAzMWQ1MWJlODdkNzE2MmVlNGIxNjczNWI1YmE0NDcwNGViZWEwNGFhZDZlMWRkMGY5ZDA3MDY1Y2U0MDRiZTZmNDlkNGMxZTc4M2FjMzc5MDc1ZmY2YTZhMjNlZjZlYzgwNDkwYjBkMDE0OTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Email Address",
"Search Term",
"Position"
],
[
"[email protected]",
"@",
"=SEARCH(B2,A2)"
],
[
"[email protected]",
"business",
"=SEARCH(B3,A3)"
],
[
"[email protected]",
"xyz",
"=SEARCH(B4,A4)"
]
];
// 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('NDYxZGY0NTkwMGNjMjc2MDBmZTAzMWQ1MWJlODdkNzE2MmVlNGIxNjczNWI1YmE0NDcwNGViZWEwNGFhZDZlMWRkMGY5ZDA3MDY1Y2U0MDRiZTZmNDlkNGMxZTc4M2FjMzc5MDc1ZmY2YTZhMjNlZjZlYzgwNDkwYjBkMDE0OTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Email Address",
"Search Term",
"Position"
],
[
"[email protected]",
"@",
"=SEARCH(B2,A2)"
],
[
"[email protected]",
"business",
"=SEARCH(B3,A3)"
],
[
"[email protected]",
"xyz",
"=SEARCH(B4,A4)"
]
]
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('NDYxZGY0NTkwMGNjMjc2MDBmZTAzMWQ1MWJlODdkNzE2MmVlNGIxNjczNWI1YmE0NDcwNGViZWEwNGFhZDZlMWRkMGY5ZDA3MDY1Y2U0MDRiZTZmNDlkNGMxZTc4M2FjMzc5MDc1ZmY2YTZhMjNlZjZlYzgwNDkwYjBkMDE0OTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pNekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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 Term",
"Position"
],
[
"[email protected]",
"@",
"=SEARCH(B2,A2)"
],
[
"[email protected]",
"business",
"=SEARCH(B3,A3)"
],
[
"[email protected]",
"xyz",
"=SEARCH(B4,A4)"
]
]
}]
});
}
}