Home » Archive

Articles in the Text Functions Category

Excel Functions, Text Functions »

Function Trim()
[21 Jun 2009 | No Comment | ]
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.

Excel Functions, Text Functions »

Function Value()
[21 Jun 2009 | No Comment | ]
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) )

Excel Functions, Text Functions »

Function Substitute()
[21 Jun 2009 | No Comment | ]

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’

Excel Functions, Text Functions »

Function Search()
[21 Jun 2009 | No Comment | ]

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)

Excel Functions, Text Functions »

Function Mid()
[21 Jun 2009 | No 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_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
  • e=”font-size:small;”>= 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.

Excel Functions, Text Functions »

Function Len()
[21 Jun 2009 | No Comment | ]

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)

Excel Functions, Text Functions »

Function Find()
[12 Jun 2009 | No Comment | ]

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!

Excel Functions, Text Functions »

Function Exact()
[12 Jun 2009 | No 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 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

Text Functions »

Function Concatenate()
[12 Jun 2009 | No Comment | ]

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

Text Functions »

Function Rept()
[8 Jun 2009 | No Comment | ]
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




Text Functions »

Function Replace()
[8 Jun 2009 | No Comment | ]

Replace()

This function is used to replace a part of string, from within a Text string, with a new Text string


Syntax:

= Replace (Old Text, Start Number, Number of Characters, New Text)

Old Text = is the original text string
Start Number = is the position within the text from where the old text is to be replaced
Number of Characters = is the number of characters in the old string which are to be removed and replaced
New Text = is the new text string that will be placed at the position referred by Start Number


Example:

  • = Replace (“Mohit Khurana”, 1, 1, “R“) - result will be –

    n>Rohit Khurana

  • = Replace (“Rajeev“, 5, 2, “sh“) - result will be – Rajesh
  • = Replace (“Alpha“, 1, 5, “Beta“) - result will be – Beta
  • = Replace (“Alpha Beta Gamma”, 10, 1, “ter“) - result will be – Alpha Better Gamma
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

Next Page »