Excel Text functions: find, search, len, mid, bahttext
Excel Function FIND
The FIND() function returns the starting index of the specified character or substring within another string.
This function takes three parameters out of which two are mandatory.
The third parameter is optional that specifies the starting index to search for.
If the third parameter is omitted, the search starts from position 1.
Text | FIND() | FORMULA |
BIRD | 3 | =FIND("R",L5) |
Bird12 SINGS | 6 | =FIND("2",L6) |
3455 | 3 | =FIND("55",L7) |
MAN"90 | 1 | =FIND("M",L8) |
That's MiNE | 8 | =FIND("MiN",L9,3) |
Find another example here of the FIND() function.
Excel Function SEARCH
The SEARCH() function also returns the index of the character or substring from within the main string.
The only difference from FIND function is that, this function is Not case sensitive.
This function takes three parameters; find_string,main_string and start index.
Text | SEARCH() | FORMULA |
BIRD | 3 | =SEARCH("r",L17) |
Bird12 SINGS | 3 | =SEARCH("r",L18) |
3455 | 3 | =SEARCH("5",L19) |
MAN"90 | 1 | =SEARCH("man",L20) |
That's MiNE | 9 | =SEARCH("ine",L21) |
Excel Function LEN
The LEN() function is used to find the length of a specified string.
This function returns the number of characters in the specified string including the white space.
Text | LEN() | FORMULA |
BIRD | 4 | =LEN(L29) |
Man and Woman | 13 | =LEN(L30) |
I am here | 9 | =LEN(L31) |
This is me | 10 | =LEN(L32) |
Bird | 5 | =LEN(L33) |
Excel Function MID
The MID() function returns the specified number of characters from the middle of the given string.
This function takes three parameters: string, start index, number of characters.
The start number or index specifies, where to start the search and return.
Text | MID() | FORMULA |
BIRD fly high | fl | =MID(L40,6,2) |
Man and Woman | Man | =MID(L41,1,3) |
I am here | here | =MID(L42,6,4) |
This is me | is | =MID(L43,5,3) |
Animals in zoo. | in z | =MID(L44,9,4) |
Excel Function BAHTTEXT
The BAHTTEXT() function is used to convert a number into Thai text format.
This function takes a single argument which is a number.
This function returns the output suffixed with "baht".
Number | BAHTTEXT | FORMULA |
2 | สองบาทถ้วน | =MID(L40,6,2) |
100 | หนึ่งร้อยบาทถ้วน | =MID(L41,1,3) |
12 | สิบสองบาทถ้วน | =MID(L42,6,4) |
0.1 | สิบสตางค์ | =MID(L43,5,3) |
0.03 | สามสตางค์ | =MID(L44,9,4) |