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...

Function Concatenate()

Dhakkanz 12:42 AM Add Comment

Concatenate() This function concatenate (joins) two or more text strings, including numbers and other symbols in text format Syntax : = Conc...

Function Rept()

Dhakkanz 2:06 PM Add Comment

Rept() This function fills a cell with the given string repeated 'n' number of times Syntax : = Rept (Text , Number of Times) Text ...

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

Function Concatenate()

Concatenate()

This function concatenate (joins) two or more text strings, including numbers and other symbols in text format

Syntax:

= Concatenate (Text1, Text2, Text...)

Text1, Text2, Text.. = are the text strings which are required to be contactenated

Example:

= Concatenate ("Excel", "Matic") - result will be -ExcelMatic
= Concatenate ("Excel", " ", "Matic") - result will be -Excel Matic (note the space between words)
= Concatenate ("Excel", " ", "Matic", " 5.2") - result will be -Excel Matic 5.2

Note: The results of Concatenate() function can also be achieved by using '&' symbol. It also performs the function of joining the text strings and numbers. Similar to Concatenate, '&' also converts the numbers into text format

Example:

= "Excel" & "Matic" - result will be -ExcelMatic
= "Excel" & " " & "Matic" - result will be -Excel Matic (note the space between words)
= "Excel" & " " & "Matic" & " 5.2" - result will be -Excel Matic 5.2

Monday, June 8, 2009

Function Rept()

Rept()

This function fills a cell with the given string repeated 'n' number of times

Syntax:

= Rept (Text , Number of Times)

Text = is the text or characters to be repeated

Number of Times = is the number of times the given text is to be repeated

Example:

= Rept ("M", 7) - result will be - MMMMMMM

= Rept ("E M", 3) - result will be - E ME ME M

Note: note the highglighted characters in the above examples. The number of characters to be removed and the number of new characters to be inserted may not be same. You can insert as many characters as you want in place of any number of characters from the string

Excel Matic Tip:

The Rept() function repeates a given character 'n' number of times and this number provided in the function can be used dynamically for other purposes, such as:

Make Graphic presentation of numeric data without using graphs:

In cell A1 to cell A10 write any numbers (try not to go beyond 50)
Now in cell B1 type this function:

= Rept("l", a1)
The character I have used is present on your keyboards above the Return key. You would need to press SHIFT and then the key above return key
Now copy this function till range B10

The output you will get will be something like this:

The image above uses Rept() to create a bar graph with "l" symbol which represents the frequency given in column A