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)

 

You can find similar Excel Questions and Answer hereunder

1) text functions exact fixed proper rept text

2) Concatenate number with text but keeping in number format in Excel

3) How can I have text autocomplete by typing in a short code for the text?

4) How do I change the rating numbers to rating labels?

5) How can I find the last used cell in a Column in VBA?

6) How can I get the most frequently occurring text in a range?

7) How can I find the least common multiple using Excel?

8) Given a raw data Table, how can I find the value of a field for a specific value of another field?

9) Here an explanation about text box and how to control text boxes in Excel VBA

10) How can I identify all cells with Conditional Formatting in my WorkSheet?

 

Here the previous and next chapter