SEARCHB function
The SEARCHB
function in Jspreadsheet Formulas Pro is used to find the position of a specific text string within another text string, starting from the first character on the left. It is a case-sensitive function that treats double-byte characters as separate entities. If the text you're looking for isn't found, it will return #VALUE!. This can be incredibly useful for locating specific data within a larger text field.
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!. This function is case-sensitive and considers double-byte characters as separate characters.
Category
Text
Syntax
SEARCHB(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 SEARCHB
function is used to find the starting position of a given text string within another text string. It distinguishes between uppercase and lowercase characters.
- The function returns the position of the first character of the found text as a number.
- It starts searching from the first character of the within_text string.
- If the text to search for (find_text) is not found, the function returns an error.
- If the text to search for (find_text) is an empty string, the function returns an error.
- If the within_text string is an empty string, and the find_text is not an empty string, the function returns an error.
- If the within_text string is an empty string, and the find_text is an empty string, the function returns 1.
- The function handles boolean values as text. For example, if the find_text string is TRUE or FALSE, it tries to match the text strings "TRUE" or "FALSE".
- If either of the arguments is a range of cells, only the first cell in the range is used.
Common Errors
Error | Description |
---|---|
#VALUE! | This error is returned if the text to search for (find_text) is not found within the within_text string. |
Best practices
- Use the optional [start_num] argument to skip a specified number of characters at the start of the within_text string.
- When using
SEARCHB
, it may be helpful to wrap the function in anISNUMBER
function to return a boolean value instead of an error if the text to search for is not found.- Keep in mind that
SEARCHB
counts 2 bytes for characters that fall in the double-byte character set, so the returned value may be larger than the actual number of characters.
Usage
A few examples using the SEARCHB function.
SEARCHB("日本","Japan is 日本 in Japanese.") returns 10
SEARCHB("日","Japan is 日本 in Japanese.") returns #VALUE!
SEARCHB("is","The sky is blue.",6) returns 9
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('ZGY3ZmYxMTQ1YWQ3YzBiY2JhMjUzMWYyMTEwYjI1NGNiZTkzOTJjZjBjZmQxN2JmZmIxNGUwZmIxMzYyZjYwNTA5ODI4ZTQ1OTJkNWZiNjBmMjI5YTdlYzUwYjg4ODAwMWI3YzEzZTNlYjU5OGQyMWFmZjI0ZWYwZjFjMWUyZmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Text to Search",
"Search Term",
"Result"
],
[
"Japan is \u65e5\u672c in Japanese.",
"\u65e5\u672c",
"=SEARCHB(B2,A2)"
],
[
"The sky is blue.",
"is",
"=SEARCHB(B3,A3)"
],
[
"Hello \u4e16\u754c World",
"\u4e16\u754c",
"=SEARCHB(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('ZGY3ZmYxMTQ1YWQ3YzBiY2JhMjUzMWYyMTEwYjI1NGNiZTkzOTJjZjBjZmQxN2JmZmIxNGUwZmIxMzYyZjYwNTA5ODI4ZTQ1OTJkNWZiNjBmMjI5YTdlYzUwYjg4ODAwMWI3YzEzZTNlYjU5OGQyMWFmZjI0ZWYwZjFjMWUyZmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Text to Search",
"Search Term",
"Result"
],
[
"Japan is \u65e5\u672c in Japanese.",
"\u65e5\u672c",
"=SEARCHB(B2,A2)"
],
[
"The sky is blue.",
"is",
"=SEARCHB(B3,A3)"
],
[
"Hello \u4e16\u754c World",
"\u4e16\u754c",
"=SEARCHB(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('ZGY3ZmYxMTQ1YWQ3YzBiY2JhMjUzMWYyMTEwYjI1NGNiZTkzOTJjZjBjZmQxN2JmZmIxNGUwZmIxMzYyZjYwNTA5ODI4ZTQ1OTJkNWZiNjBmMjI5YTdlYzUwYjg4ODAwMWI3YzEzZTNlYjU5OGQyMWFmZjI0ZWYwZjFjMWUyZmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Text to Search",
"Search Term",
"Result"
],
[
"Japan is \u65e5\u672c in Japanese.",
"\u65e5\u672c",
"=SEARCHB(B2,A2)"
],
[
"The sky is blue.",
"is",
"=SEARCHB(B3,A3)"
],
[
"Hello \u4e16\u754c World",
"\u4e16\u754c",
"=SEARCHB(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('ZGY3ZmYxMTQ1YWQ3YzBiY2JhMjUzMWYyMTEwYjI1NGNiZTkzOTJjZjBjZmQxN2JmZmIxNGUwZmIxMzYyZjYwNTA5ODI4ZTQ1OTJkNWZiNjBmMjI5YTdlYzUwYjg4ODAwMWI3YzEzZTNlYjU5OGQyMWFmZjI0ZWYwZjFjMWUyZmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhORFE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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: [
[
"Text to Search",
"Search Term",
"Result"
],
[
"Japan is \u65e5\u672c in Japanese.",
"\u65e5\u672c",
"=SEARCHB(B2,A2)"
],
[
"The sky is blue.",
"is",
"=SEARCHB(B3,A3)"
],
[
"Hello \u4e16\u754c World",
"\u4e16\u754c",
"=SEARCHB(B4,A4)"
]
]
}]
});
}
}