Save time with these text formatting functions in Microsoft Excel

Microsoft Excel is a main application for anyone who has to work with numbers, from students to accountants. But its usefulness extends beyond a large database, it can do a lot of great things with text. The Excel functions listed below will help you analyze, edit, convert, change text and save many hours of doing boring and repetitive tasks.

Non-Destructive Editing ( Non-Destructive Editing )

One of the principles of using Excel text functions is non-destructive editing. This means that whenever a function is used to change text in a row or column, that text will remain unchanged and the new text will be placed in a new row or column. This format is very useful, especially when working with a large spreadsheet.

One way to take advantage of this feature is to save the original spreadsheet to the first sheet in the document and the later edited copies in other worksheets. That way, no matter how many edits you have made, you will always have the original data working.

Single-byte and double-byte characters

Some of the functions discussed here refer to single-byte and double-byte character sets. In some languages ​​such as Chinese, Japanese and Korean, each character (or several characters) will have two possibilities: one is coded in two bytes (double-byte character) and one is code in one byte (single byte character). You can see the difference in these characters here:

As shown in the image above, double-byte characters are larger and often easier to read. However, in some situations, one or more of these types of encryption are required. There are a number of functions below that involve single-byte and double-byte characters.

Group of string processing functions

CHAR and UNICHAR functions

The CHAR function takes some characters and returns the corresponding character. For example, if there is a list of characters, CHAR will turn them into commonly used characters. The syntax is quite simple:

= CHAR ([text])

The [text] argument can take the form of a cell reference or a character, so = CHAR (B7) and = CHAR (84) both work. Note that when using the CHAR function, it will use the encryption that the computer is set up, so = CHAR (84) may be different for users of Windows and Mac computers.

If the converted number is a Unicode character number and using Excel 2013, you will need to use the UNICHAR function. Previous versions of Excel do not have this function.

CODE and UNICODE functions

The CODE and UNICODE functions have the opposite function compared to CHAR and UNICHAR functions. These functions take a character and return the selected encryption number (or the default setting on the computer). It is important to note that if you run this function on a string that contains many characters, it will return only the character reference for the first character in the string. The syntax is very similar:

= CODE ([text])

In this case, [text] is a character or a string. And if you want to reference Unicode instead of the default of the computer, you will use the UNICODE function (in Excel 2013 or newer).

Group of text analysis functions

The functions in this section will help you get information about the text in a cell.

Function LEN

LEN is a very simple function, it will return the length of a string, used to count the number of letters in a different type of cells. This is the syntax:

= LEN ([text])

The argument [text] is the cell or cells you want to count. In the example below, use the LEN function on a cell containing the city name 'Austin', which returns the result as 6. When using the city name "South Bend", it returns the result 10. A distance is calculated as a character with the LEN function, so keep in mind if you are using it to count the number of letters in a given cell.

The LENB function performs the same function but works with double-byte characters. If you want to count a series of four double-byte characters using the LEN function, the result will be 8, with LENB being 4 (if there is a DBCS enabled as the default language).

1. How to use the LEN function in Excel

FIND function

Many people may wonder why you need to use FIND while you can use CTRL + F or Edit> Find . The answer lies in the specificity you are looking for. Instead of searching the entire document, you can select any character of each string. The following syntax will help clarify this confusing definition:

= FIND ([find_text], [within_text], [start_num])

[find_text] is the search string, [within_text] is the cell or cells in which Excel searches for that text, and [start_num] is the first character it will look at. It is important to note that this function is case sensitive.

In this example is the table of each student's ID number, including a six-digit alphanumeric string. Each ID starts with a digit, the letter M for "male", a sequence of two letters to indicate the level of student achievement (HP is high achievement, SP for average, low for LP and UP / XP is unknown), and a final string consists of two numbers. Use the FIND function to find high performing students. Here is the syntax we will use:

= FIND ("HP", A2, 3)

This function will indicate whether HP appears after the third character in the cell. Apply to all cells in the ID column, we can immediately see the result of high performance or not (note that 3 returned by this function is the character HP is found).

Like LEN and LENB functions, FINDB is used for the same purpose as FIND, except for the double-byte character set. If you are using DBCS and specifying the fourth character with FIND, the search will start in the second character but with FINDB it is possible to solve this problem.

Note, the FIND function is case sensitive, so you can search for a specific capitalization. If you don't want to distinguish case sensitive, you can use the SEARCH function, get the same arguments and return the same value.

EXACT function

If you need to compare two values ​​to see if they are the same, EXACT is the function you need. When using the EXACT function with two strings, it returns TRUE if identical, and FALSE if different. Because the EXACT function is case sensitive, it returns FALSE if the string has "Test" and "test". This is the syntax for EXACT:

= EXACT ([text1], [text2])

Both arguments are easy to understand, they are strings that you want to compare. In the example spreadsheet below, we will use them to compare both SAT and Reported points with the following syntax:

= EXACT (G2, F2)

Repeat the formula for each row in the column that gives us the result:

Group of text conversion functions

These functions take values ​​from a cell and turn them into another format, for example, from a number to a string or from a string to a number.

TEXT function

The TEXT function converts numeric data into text and allows it to be formatted in specific ways. This can be useful if you are planning to use Excel data in a Word document. Take a look at the syntax and then see how to use it:

1. How to insert a Word document into an Excel file

= TEXT ([text], [format])

The [format] argument allows you to choose how you want the number to appear in the text. There are a number of different operators that can be used to format text, but we will follow the simple rules here. The TEXT function is often used to convert currency values, so we'll start with that.

In this example, we will turn the number in the 'Tuition' column into a monetary value. Here is the syntax:

= TEXT (G2, "$ #, ###")

Using this format string will give the number preceded by the dollar symbol and include a comma after hundreds. This is what happens when applying to a spreadsheet:

Each number is now properly formatted. You can use the TEXT function to format numbers, currency values, dates, and times.

FIXED function

Similar to TEXT, the FIXED function has input and formatting as text, however, the FIXED function converts numbers into text and provides some specific options for formatting and rounding out the output. This is the syntax:

= FIXED ([number], [decimals], [no_commas])

The [number] argument contains references to cells that want to convert to text. The [decimals] argument is an optional argument that allows selecting the number of decimal places to be retained in the conversion. If this is 3, you will get some like 13,482. If using a negative number for decimal, Excel will round the number. We will consider that in the example below. The [no_commas] argument, if set to TRUE, will exclude commas from the last value.

We will use this to round the number of fees used in the previous example for the nearest thousandth.

= FIXED (G2, -3)

Function VALUE

This function is the opposite of TEXT function, it takes any cell and turns into a number. This is especially useful if you import a spreadsheet or copy and paste a large amount of data and be formatted as text. Here is the syntax:

= VALUE ([text])

Excel will recognize accepted formats in terms of numbers, times and dates, then convert them into numbers that can be used with digital functions and formulas.

DOLLAR function

Similar to TEXT, DOLLAR functions convert the value to text, but it also adds a dollar sign and selects the decimal:

= DOLLAR ([text], [decimals])

If [decimals] is left blank, it will default to 2. If a negative number is given for the [decimals] argument, this number will be rounded from the left of the decimal number.

ASC function

As we discussed before single-byte and double-byte characters, this is how you switch between them. Specifically, this function converts double-byte characters into single-byte characters. It can be used to save some space in spreadsheets. This is the syntax:

= ASC ([text])

This function is quite simple, just run the ASC function on any text you want to convert. To see if it works, we will convert the spreadsheet containing some Japanese katakana letters, which are usually expressed in double-byte characters. Change them to a single byte character.

JIS function

The JIS function performs the opposite function against the ASC function, which converts the single byte character into double byte. Like ASC, the syntax is very simple:

= JIS ([text])

Group of text editing functions

One of the most useful things you can do with the text in Excel is the programming of making modifications to it. The following functions will help you enter text and get into the most accurate format.

UPPER, LOWER and PROPER functions

These are very simple and easy to understand functions. The UPPER function turns text into uppercase, LOWER function turns text into lowercase, and PROPER function capitalizes the first letter in each word. This is the syntax:

= UPPER / LOWER / PROPER ([text])

Select the cell or range in which the text for the argument [text].

CLEAN function

Importing data into Excel is often very easy, but sometimes users encounter unexpected characters. This is most common when there are special characters in the original document that Excel cannot display. Instead of looking at all the cells containing those characters, you can use the CLEAN function with the following syntax:

= CLEAN ([text])

The [text] argument is simply the position of the text you want to clean up. In this example spreadsheet, we will add some unprintable characters to the names in Column A. Use the CLEAN function to convert text to Column G without those characters:

Now, Column G contains a name without a printable character. This command is not only useful for text but also useful for numbers in formulas.

TRIM function

Although the CLEAN function removes unprintable characters, the TRIM function removes extra spaces at the beginning or end of the text string when copying text from a plain Word or text document, just using this syntax:

= TRIM ([text])

1. Tips for copying fast and efficient paragraph formatting in Word

Functional groups instead of copies

Sometimes, users will need to replace specific strings in the text with a series of other characters. Using Excel formulas is much faster than finding and replacing, especially when working with a very large spreadsheet.

SUBSTITUTE function

If you're working with a lot of text, you'll sometimes need to make some big changes, like inserting a text string for another text string, writing the wrong date in your invoices or typing the wrong name. That's what SUBSTITUTE function is used with:

= SUBSTITUTE ([text], [old_text], [new_text], [instance])

The [text] argument contains the position of the cells to be replaced, [old_text] and [new_text] are old and new text. The [instance] argument allows to specify a specific object of the old text instead. So if you only want to replace the third object of the old text, you must enter "3" for this argument. SUBSTITUTE will copy all other values.

For example, we will correct the spelling errors in the spreadsheet below. Suppose "Honolulu" was accidentally spelled "Honululu". This is the syntax used to fix it:

= SUBSTITUTE (D28, "Honululu", "Honolulu")

After dragging the formula for the surrounding cells, you will see that all the cells in column D have been copied, except that the cells containing the misspelled word "Honululu" are replaced with the correct spelling.

REPLACE function

REPLACE functions like the SUBSTITUTE function, but instead of replacing a specific character string, it replaces the characters in a specific location. This is the syntax of the REPLACE function:

= REPLACE ([old_text], [start_num], [num_chars], [new_text])

The [old_text] argument is where the cells to replace text, [start_num] are the first characters to be replaced, and [num_chars] is the number of characters to be replaced. The [new_text] argument is the new text that will be inserted into cells, it can also be a cell reference.

For example, in the following worksheet, the Student ID has the characters HP, SP, LP, UP and XP. We want to eliminate them and change all NPs. Here is the syntax:

= REPLACE (A2, 3, 2, "NP")

All two-character sequences from Column A have been replaced with "NP" in column G.

Group of other text manipulation functions

In addition to changing strings, you can also make small changes in a string (or use smaller strings to form larger strings). Here are some of the most commonly used text functions in Excel.

CONCATENATE function

The CONCATENATE function is used when two cells are entered. This is the syntax:

= CONCATENATE ([text1], [text2], [text3] .)

What makes the CONCATENATE function useful is that the [text] arguments can be plain text like "Arizona" or cell references like "A31". You can even use both. This can save you a lot of time when you need to combine two columns of text, such as creating a column "Full Name" from "First Name" and "Last Name" column.

= CONCATENATE (A2, "", B2)

Note, the second argument is the space. Without this space, the name will be directly connected, with no spaces between the first and last names.

Now we have a column with everyone's full name. You can easily use this command to combine the area code and phone number, name and employee number, city and state, or even symbols and currency numbers.

In addition, you can shorten CONCATENATE function to a marker. To create the above formula using the "&" notation, we will type:

= A2 & "" & B2

You can also use it to combine cell references and text lines, as follows:

= E2 & "," & F2 & ", USA"

1. How to combine 2 columns Full name in Excel does not lose content

LEFT and RIGHT functions

Often, many people only want to work with the first few (or last) characters of a text string. LEFT and RIGHT let you do that by returning only certain characters starting from the left or right character in a string. This is the syntax:

= LEFT / RIGHT ([text], [num_chars])

The argument [text] is the original text and [num_chars] is the number of characters to return. Let's take an example of when to perform the above two functions. Suppose you have entered a number of addresses and each address contains both state and national abbreviations. We can use LEFT to get only abbreviations, using this syntax:

= LEFT (E2, 2)

MID function

MID functions are like LEFT and RIGHT, but allow you to drag characters out of position between a string, starting from the specified position. This is the syntax:

= MID ([text], [start_num], [num_chars])

The argument [start_num] is the first character to be returned. This means that if you want the first character in a string to be included in the result of a function, this will be "1". The [num_chars] argument is the number of characters after the start character will be returned. In the example spreadsheet, we have now added the title to the name, but will separate those from the "Mr. Martin" family to return the result as "Martin". This is the syntax:

= MID (A2, 5, 15)

Here, we will use "5" as the starting character, because the first letter of the person's name is the fifth ("Mr." occupies 4 spaces). The function will return the next 15 letters.

REPT function

If you need to retrieve a string and repeat it a number of times you should use the REPT function. Give the REPT function a string ("abc") and some (3) times you want to repeat, and Excel will give exactly what you require ("abcabcabc"). Here is a very easy syntax:

= REPT ([text], [number])

Argument [text] is the base string; [number] is the number of times you want it to repeat.

See also: Summary of expensive shortcuts in Microsoft Excel