Text Functions in Microsoft Excel What are they, what are they for and how can I use them without fail in my documents?

when you work with files that you migrate from other programs and need to convert to text formats In order for you to find words, convert them from uppercase to lowercase or vice versa, you will need to handle Excel’s text formulas.

In this way you will be able to become an expert because you will save a lot of time and you will not make mistakes, since You will find the results in a practical and fast way.

We will show you below all the text functions that Microsoft Excel offers to users. You will be able to find an example of each one so that you can put it into practice in a simple way.

What is a text formula in Microsoft Excel and what are they used for in spreadsheets?

The text formulas are tools used in Excel to convert numbers and other types of arguments to words. In this way, a spreadsheet can be used to extract words, find certain characters, or replace one sentence with another.

They are very useful functions. that can be used in a simple way, you just have to pay attention and know them all so you can find the one that best suits your needs.

List of all text functions available in Excel and how to use them without errors

The text functions that you can use in Excel are the following:

• CHARACTER. This formula allows you to write using the code numbers that the operating system has. If you type CHAR(97) the result will be the letter a.
• CODE. When you want to get the code of the first character, you will have to use this formula. This is related to the previous function, since if you don’t know the number you will have to use this tool first.
• CONCATENATE. In case you want to have a text with several elements in a single cell, you will have to use this function. It is ideal to apply it in programs where you have different columns and you want to put all that data so that the other software can recognize it. To use it, you will have to choose all the data and then press Enter.
• DECIMAL. When you have a number and you want to convert it to text, you must first round it. For this you will have to choose the number, set the decimals and then choose true or false if there are no commas. For example, DECIMAL(145.678, 2, TRUE) = 145.68
• RIGHT. If you have a text string and you want to extract the characters that are located to the right, you will have to use this formula so that it highlights the last word. You can also include the number of characters you need to get. You would have to use it like this: RIGHT(“Internet Step by Step”, 4) = “Step” > RIGHT(“Internet Step by Step”) = “or”
• FIND. When you need to find a certain text, you can find the starting position within a text string. You can do this like this: FIND(«I», «IPAP») = 1 ; FIND(“A”, “IPAP”) = 3
• SPACES. This function is useful when you need to remove the spaces that exist within a text, with the exception of the individual spaces that words have. An example of this is: SPACES(” Internet “) = “Internet” ; SPACES(“IPAP”) = “IPAP”
• EXTRACTS. You can find the number of characters in a text string by setting the position of the first letter and then telling the program which characters to extract from the given position. For example: MID(“InternetStepbyStep”, 9, 6) = “SteptoP” ; MID(“InternetStepbyStep”, 14, 4) = “Step”
• FIND. If you need to know the position of a character within a text string, you will have to use this song. You will have to indicate the text string in which you want to find the word and indicate if you want to set the initial position where the search should start. If this data is omitted, Excel considers that it will start at 1. The correct use is: FIND(«Internet», «InternetPasoaPaso») = 1 ; SEARCH(“Step”, “InternetStepbyStep”) = 9
• SAME. It is one of the most useful features you can find in the text group. Compare if two text strings are equal, returning the value TRUE or FALSE. For this it is necessary to incorporate the two strings. Its use is: EQUAL(«InternetStepbyStep», «InternetStepbyStep») = TRUE ; EQUAL(“InternetStepbyStep”, “internetStepbyStep”) = FALSE
• LEFT. Like the RIGHT function, this tool returns character values ​​to the left of a text string. For example, it should be used like this: LEFT(“Internet Step by Step”, 8) = “Internet” ; LEFT(“Internet Step by Step”) = “I”
• LENGTH. You will be able to know the number of characters that a certain text has. You’ll have to use it like this: LONG(“Internet”) = 8 ; LONG(“IPAP”) = 4
• CLEAN UP. In case you need to remove characters that cannot be printed, you will have to use this tool. An example of this would be: CLEAN(CHAR(17) & “Internet” & CHAR(25)) = “Internet”
• UPPER This function converts a text string to uppercase letters. For example, UPPER(“Internet Step by Step”) = “INTERNET STEP BY STEP” ; UPPER(“ipap”) = “ipap”
• MINUSC. As with the previous function, with this tool you will be able to convert texts into lowercase letters. MINUSC(“INTERNET STEP BY STEP”) = “internet step by step” ; MINUSC(«Internet Step by Step») = «internet step by step»
• CURRENCY. If what you are looking for is to convert a number into text format, you will have to use this function. In addition, it will incorporate the currency sign. You will have the possibility to include the number of decimals that you want to appear in the result. The use that you must make of this function is the following: CURRENCY(1203) = «€1203.00» ; CURRENCY(24.858, 2) = “€24.86”
• PROPER. You will be able to convert a string of texts into uppercase and lowercase, being the first letter of each word in uppercase. An example for this use is the following NOMPROPER(«internet step by step») = «Internet Step By Step» ; PROPER(“microsoft spreadsheet program”) = “Microsoft Spreadsheet Program”
• REPLACE. In case you need to replace a text string with another you will have to use this function. You will have to incorporate the original text (which contains the characters that you need to change), the position that it will have within that text string and where the replacement task will have to start from, the number of characters that you want to replace and the text that will replace the original characters. REPLACE(“0123456789”, 3, 6, “IPAP”) = “01IPAP89” ; REPLACE(“Year 2019”, 6, 2, “20”) = “Year 2020”
• REPEAT. You can repeat a text as many times as you need with this tool. To do this you will have to choose the text and indicate how many times it will be repeated. For example: REPEAT(“Internet”, 3) = “InternetInternetInternet” ; REPEAT(“IPAP”, 2) = “IPAPIPAP”
• REPLACE. When you use this function you will be able to replace one text with another. You will need to establish the text that will make the change, you will have to indicate the text in which the modification will be made, the text with which it will be replaced and, if you wish, the appearance it will have each time it is replaced. An example of this usage is: SUBSTITUTE(“123456”, “56”, “5678”) = “12345678” ; SUBSTITUTE(“InternetInternetInternet”, “Internet”, “IPAP”, 2) = “InternetIPAPInternet”
• T. That function is used when you have a large amount of data and you need to know if a value is text. If it is not, return a result in quotes. For example, it should be used like this: T(«Internet_Step_by_Step») = «Internet_Step_by_Step» ; T(766.98) = “”
• TEXT. You will be able to convert a value to text using a specific number format. You will need to include the numeric value to be converted then to a text function and then to the format you want to apply that amount to. An example of this is: TEXT(446, “0.00€”) = “446.00€”
• TEXTBAHT. You will be able to convert a number into text of the Thai unit of measure. An example: BAHTTEXT(443) = สองร้อยสามสิบสามบาทถ้วน. That is, it shows number 443 in a text from that country.
• UNICAR. With this function you can find the UNICODE code that corresponds to a given number. For example, UNICAR(“82”) = “R”
• UNICODE. This function will let you know the unique code of the first letter of a text. The correct use of this formula will be UNICODE(«InternetPasoaPaso») = «73
• WORTH. When you export numbers from a table from another software or Internet page, they are converted into text, presented as numbers. In order to add or perform any arithmetic operation you will have to convert them into numbers. To do this you will have to use this formula: VALUE(«248») = 248 ; VALUE(«internet») = #VALUE!
• VALUE.NUMBER. You can use this function to convert a text to a number regardless of the regional settings you have on your computer. You must bear in mind that there are countries where the comma is used to start writing the units of a thousand and the point is used to separate the cents. Examples of the use of this tool are: VALUE.NUMBER(“1,600.77″.”.”.”,”) = 1600.77 ; VALUE.NUMBER(“7.8%”) = 0.078