Let's learn some Text functions in excel

1. CONCATENATE
Formula:
=CONCATENATE(A1," ",B1," ",C1)

Concatenate formula is used to combine two or more words in single cell.

Example:
Cell A1 = Vinod, B1 = Shyam, C1 = Verma
with the above formula, we will get the result as "Vinod Shyam Verma" in single cell. The " " is used for creating space between two words.

This can also be acheived by directly writing formulas as:
=A1 & " " & B1 & " " & C1
here & is used in place of comma's to directly concatenate the words.


2. FIND
Formula:
=FIND(find_text, within_text, [start_num])

The FIND function is used to locate the position of one text within a second text , and return the number of the starting position of the first text from the first character of the second text.

Example:
The formula =FIND("t","complete") will give output as 7, as t is located on 7th number in the word.
The formula =FIND("a","complete") will return error as #VALUE!, as the text to find is not present in the word.

FIND is case sensitive and does not allow wildcard characters.
If find_text is "" (empty text), FIND matches the first character in the search string (that is, the character numbered start_num or 1).
If start_num is not greater than zero, FIND returns the #VALUE! error value.
Use start_num to skip a specified number of characters. Using FIND as an example, suppose you are working with the text string "FYA2014.YouthSignsRoute". To find the number of the first "Y" in the descriptive part of the text string, set start_num equal to 8 so that the serial-number portion of the text is not searched. FIND begins with character 8, finds find_text at the next character, and returns the number 9. FIND always returns the number of characters from the start of within_text, counting the characters you skip if start_num is greater than 1.


3. MID
Formula:
=MID(text, start number, number of characters)

These formulas return the number of characters from a text string. MID gives you the specified number of characters from the middle of the word.

You can specify in the MID formula where to start with the start_number and then it counts the specified number of characters to the right of the start_number.

A1 = "How are you"
Use the MID formula to get the middle word.
=MID(A1,5,3) formula gives us the word "are" from "How are you".


4. LEFT
Formula:
=LEFT(text, number of characters)

These formulas return the number of characters from a text string. LEFT gives you the number of characters from the left.
In the below example we have used the LEFT formula to get the first word.
A1 = "How are you"
=LEFT(A1,1) formula gives  us the word "H" from "how are you".



5. RIGHT
Formula:
= RIGHT(text, number of characters)

These formulas return the number of characters from a text string. RIGHT gives you the number of characters from the right of the text string.

A1 = "How are you"
Use the RIGHT formula to get the last word.
=RIGHT(A1,3) formula gives us the word "you" from "How are you"


6. LEN
Formula:
=LEN(text)

LEN returns the number of characters in a text string, including spaces.

Example:
consider, cell A1 and A2 has below text respectively
A1 =How are you
A2 =Howareyou

=LEN(A1) will give you result as 11, as it also counts spaces between words
=LEN(A2) will give you result as 9, as there are no spaces


7. LOWER
Formula:
=LOWER(text)

Converts all uppercase letters in a text string to lowercase. LOWER does not change characters in text that are not letters.
Example:
The formula =LOWER(Earn Your Way) will give output as "earn your way"
The formula =LOWER(E3. B23 Way) will give output as "e3. b23 way"


8. UPPER
Formula:
=UPPER(text)

Converts all lowercase letters in a text string to uppercase. UPPER does not change characters in text that are not letters.

Example:
The formula =UPPER(your way) will give output as "YOUR WAY"
The formula =LOWER(neat) will give output as "NEAT"


9. PROPER
Formula:
=PROPER(text)
The PROPER formula Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

Example:
A1 = this is a HEADING
A2 = 99ReaDing
The formula =PROPER(A1) will give output as "This Is A Heading"
The formula =PROPER(A2) will give output as "99Reading"


10. REPT
Formula:
=REPT(text, number_times)

This function Repeats the text a given number of times. We can use the REPT to fill a cell with a number of instances of a text string.
Note:
If number_times is 0 (zero), REPT returns "" (empty text).
If number_times is not an integer, it is truncated.
The result of the REPT function cannot be longer than 32,767 characters, or REPT returns #VALUE!.
Example:
The formula =REPT("%",5) will give output as %%%%%
The formula =REPT("Why",2) will give output as WhyWhy


11. SUBSTITUTE
Formula:
=SUBSTITUTE(the source text, old text to replace, new text to replace old text, instance number)

Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

Here instance number is optional, it is used only when the text or number you want to replace is repeating more than once.

Example:
A1 = Exam Dashboard
A2 = Q3, 1933

The formula =SUBSTITUTE(A1,"Exam","Result") will give output as "Result Dashboard)
The formula =SUBSTITUTE(A2,"3","8",2) will give output as "Q3, 1983", here in this example the 2nd instance of 3 is replaced with 8.


12.TRIM
Formula:
=TRIM(A1)

You can use this functon to remove any space in a cell, except for single spaces between words. This formula is extremely useful because very often we run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of the data. This can create big mess if you are trying to compare using IF statements or VLOOKUP’s.
Example:
The formula =Trim( Why) will give output as "Why", the space before Why is removed.
The formula =Trim(When  ) will give output as "When", the spaces at the end of When are removed.


13. VALUE
Formula:
=VALUE(text)

This formula converts a text that represents a number to a number.

Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value.

You do not generally need to use the VALUE function in a formula because Excel automatically converts text to numbers as necessary. This function is provided for compatibility with other spreadsheet programs.
Example:
The formula =VALUE("2.999") will give output as 2.999.
The formula =VALUE("why") will give output as error, as there is no integer value to convert.


14. EXACT
Formula:
=EXACT(text1, text2)

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document.

Example:
A1 = Love and B1 = love
A2 = flower and B2 = flower

The formula =EXACT(A1,B1) will give output as FALSE.
The formula =EXACT(A2,B2) will give output as TRUE.