ExcelMatic

Home / Text Functions

Showing posts with label Text Functions. Show all posts

Function Value()

Value() This function is used to convert a number, stored as a text, into value Syntax : = Value (Number _text) Number_text: Number stored a...

Function Trim()

Dhakkanz 2:04 PM Add Comment

Trim() T his function removes extra blank spaces from the given text. It removes text from text from – the start of the text string, at the ...

Function Substitute()

Dhakkanz 1:29 PM Add Comment

Substitute() This function replaces existing text within an old text, with a new text string. Note that this function is case sensitive , i....

Function Search()

Dhakkanz 1:12 PM Add Comment

Search() This function returns the position at which a specified substring (character or a set of characters) is first found. Note that it r...

Function Mid()

Dhakkanz 12:59 PM Add Comment

Mid() This function is used to extract a set number of characters (a substring) from the within a string Syntax : = Mid (Base_string, Start_...

Function Len()

Dhakkanz 12:49 PM Add Comment

Len() This function returns the length of the string, in terms of number of characters in it, including spaces, speacial characters, line fe...

Function Find()

Dhakkanz 1:03 AM Add Comment

Find() This function searches for a text string within a text string, but this function is case sensitive. This function considers ' App...

Function Exact()

Dhakkanz 12:51 AM Add Comment

Exact() This function checks whether the 2 text strings are exactly same or not. It returns TRUE or FALSE as per the case. This function is...

Subscribe to: Posts ( Atom )

Showing posts with label Text Functions. Show all posts

Sunday, June 21, 2009

Function Value()

Value()

This function is used to convert a number, stored as a text, into value

Syntax:
= Value (Number _text)

Number_text: Number stored as a string/text

Example:

In the above example, I have used two functions deliberately to show the actual application of this function. In the above example, the Mid() function extracts the numbers as text, from the string – ‘B99’.

And the function Value() converts the number, extracted as text back into a proper number. The difference between the two is that a number stored as text cannot be used for any calculations, whereas a proper number can be used for calculations

Note: I have used two functions in two different cells in the above example. This, instead can be written as:

= Value( Mid( B3,2,2) )

Function Trim()

Trim()

This function removes extra blank spaces from the given text. It removes text from text from – the start of the text string, at the end of the text string, and any extra spaces between the words within a text string

Syntax:

= Trim (String)

String: a text string that may contain any extra spaces which needs to cleaned from the string

Example:

Note: In the above example, the extra spaces (represented by ‘|’ symbol) were removed using the Trim() function.

Function Substitute()

Substitute()

This function replaces existing text within an old text, with a new text string. Note that this function is case sensitive, i.e. it will treat 'A' and 'a' as different

Syntax:

= Substitute (Base_Text, Replace_Text, New_Text, Instance_num)

Base_text = Is the main text within which a part of the text is to be replaced

Replace_text = Is that part of the Base Text which is to be replaced

New_text = New text string that will replace the old text within the Base Text

Instance_num = The instance of the Replace_text which is to be replaced. This is optional - if it is mentioned, then that instance of the Replace Text gets replaced, else all instances of the Replace Text gets replaced

Example:

For the below examples, let us assume that cell A1 contains text - My name is Excel Matic

= Substitute (A1, "Excel Matic", "Mohit Khurana") - result will be - My name is Mohit Khurana
= Substitute (A1, "e", "u") - result will be - My namu is Excul Matic
= Substitute (A1, "e", "u", 2) - result will be - My name is Excul Matic

Note: In the above examples, note the highlighted text. In example 3 - only the second instance of the letter 'e' has been replaced with 'u'

Function Search()

Search()

This function returns the position at which a specified substring (character or a set of characters) is first found. Note that it returns position of the first occurence of the substring. This function is a non-case sensitive function, i.e. it considers 'E' and 'e' as same

Syntax:

= Search (Substring, Base_string, Start_pos)

Substrin = Is the string that is to be found within the Base String

Base_string = The string from which the substring is to bea searched / located

Start_pos = Position within the Base String from where the search should start finding

Example:

= Search ("E", "Excel Matic", 1) - result will be - 1
= Search ("m", "Excel Matic", 1) - result will be - 7
= Search ("E", "Excel Matic", 2) - result will be - 4 (though we started finding 'e' from second position, but the functions returns the position relative to original string, without ignoring the initials characters)
= Search (" ", "Excel Matic", 1) - result will be - 6 (Spaces, special characters can also be found using this function)
= Search ("E", "Excel Matic", 20) - result will be - #VALUE! (This will return an error as it is not able to find 'E' beyond the original length of the text)
= Search ("G", "Excel Matic", 1) - result will be - #VALUE! (This will return an error as it is not able to find 'G' which is not present in the Base String)

Function Mid()

Mid()

This function is used to extract a set number of characters (a substring) from the within a string

Syntax:

= Mid (Base_string, Start_pos, Num_chars)

Base_string = The stringfrom which the characters are to be extracted

Start_pos = Position within the Base String from where the substring is to be extracted

Num_chars = Is the length of the substring that is to be extracted from the Base String

Example:

= Mid ("Excel Matic",1 , 1) - result will be - E
= Mid ("Excel Matic",1 , 2) - result will be - Ex
= Mid ("Excel Matic",1 ,31) - result will be - Exc
= Mid ("Excel Matic",1 , 4) - result will be - Exce
= Mid ("Excel Matic",1 , 5) - result will be - Excel
= Mid ("Excel Matic",7 , 1) - result will be - M
= Mid ("Excel Matic",7 , 2) - result will be - Ma
= Mid ("Excel Matic",7 , 3) - result will be - Mat
= Mid ("Excel Matic",7 , 4) - result will be - Mati
= Mid ("Excel Matic",7 , 5) - result will be - Matic

Note: In case you specify a position which is out of the range of the length (i.e., is larger than the length of the string, this function will return a null value. And, in case you return the length of substring which exceeds the number of characters post the start-position, the function will only return a substring till the end of Base String.

Function Len()

Len()

This function returns the length of the string, in terms of number of characters in it, including spaces, speacial characters, line feeds, etc.

Syntax:

= Len (String)

String = The string for which the length is to be determined

Example:

= Len ("Excel") - result will be - 5
= Len ("Matic") - result will be - 5
= Len ("ExcelMatic") - result will be - 10
= Len ("Excel Matic") - result will be - 11
= Len ("## @@ $$") - result will be - 8 (The string here contains 6 special characters and 2 spaces)

Saturday, June 13, 2009

Function Find()

Find()

This function searches for a text string within a text string, but this function is case sensitive. This function considers 'Apple' and 'apple' as different text strings. This function returns the position at which the text string is located. And, if the string is not found, it retuns the error (#Value!)

Syntax:

= Find (Search_text, Base_text, Start_pos)

Search_text = Text string which is to be found in the Base Text

Base_text = Text string within which the Search Text is to be found

Start_pos = position in the Base Text from which the function should start looking for the Search Text. This is input is optional

Examples:

= Find ("e", "Excel Matic") - result will be - 4
= Find ("E", "Excel Matic") - result will be - 1
= Find ("c", "Excel Matic") - result will be - 3
= Find ("c", "Excel Matic",5) - result will be - 11
= Find ("S", "Excel Matic") - result will be - #Value!

Function Exact()

Exact()

This function checks whether the 2 text strings are exactly same or not. It returns TRUE or FALSE as per the case. This function is case-sensitive and treats 'A' different from 'a'

Syntax:

= Exact (Text1, Text2)

Text1, Text2 = are 2 text strings to be compared

Example:

Following Examples will make the usage of Exact() function clear

= Exact ("Excel, "Matic") - result will be - FALSE
= Exact ("Excel, "excel") - result will be - FALSE
= Exact ("matic, "Matic") - result will be - FALSE
= Exact ("Excel, "Excel") - result will be - TRUE
= Exact ("Matic, "Matic") - result will be - TRUE