CONCATINATE formula in Excel with examples

In Excel, the CONCATENATE function allows you to combine (join) two or more text strings into a single cell. However, in recent versions of Excel (2016 and later), you can also use the CONCAT function, which achieves the same result. Let’s explore both options:

1. Using CONCATENATE:

   • To concatenate the string in cell A1 with the string in cell B1, use either of the following formulas:

     =CONCATENATE(A1, B1)
     

     or

     =A1 & B1
     

   • This combines the contents of cells A1 and B1 without any additional characters.

2. Adding Delimiters:

   • If you want to include a delimiter (e.g., space, comma, dash) between the strings, you can modify the formula:

     =CONCATENATE(A1, " and ", B1)
     

     or

     =A1 & " and " & B1
     

   • This adds the phrase " and " between the strings.

3. Ignoring Empty Cells:

   • The TEXTJOIN function (available in Excel 2016 or later) is even more powerful. It can concatenate a range of strings while ignoring empty cells:

     =TEXTJOIN(", ", TRUE, A1:A10)
     

     • In this example, it joins the contents of cells A1 to A10 with a comma and space as the delimiter, ignoring any empty cells.

4. Formatting Numbers:

   • When using the CONCATENATE function, you can format numbers using the TEXT function. For instance:

     =CONCATENATE("Total: ", TEXT(A1, "0.00"))
     

     • This displays the value in cell A1 with two decimal places.

5. Inserting Line Breaks:

   • To insert a line break, use CHAR(10) and enable text wrapping in the cell:

     =A1 & CHAR(10) & B1
     

Remember, the choice between CONCATENATE and CONCAT depends on your Excel version and personal preference. Happy Excel-ing!

COMBIN formula in Excel with examples

The COMBIN function in Excel calculates the number of combinations for a given set of items. It’s particularly useful when you want to determine how many ways you can choose a specific number of items from a larger pool without regard to their order. Let’s dive into some examples:

1. Basic Usage:

   • To find the number of ways to choose 3 items out of a set of 10, use the formula:

     =COMBIN(10, 3)  // Returns 120
     

   • Here, 10 represents the total number of items, and 3 is the number of items chosen in each combination.

2. Practical Scenarios:

   • Suppose you have a group of 5 friends, and you want to know how many ways you can form a 2-person team:

     =COMBIN(5, 2)  // Returns 10
     

   • This tells you there are 10 different ways to create a team of 2 friends from the group of 5.

3. Visualizing Pairings:

   • Imagine you have a deck of 52 playing cards, and you want to know how many ways you can select 2 cards:

     =COMBIN(52, 2)  // Returns 1,326
     

   • There are 1,326 possible ways to choose a pair of cards from the deck.

Remember that the COMBIN function doesn’t allow repetitions (i.e., once an item is chosen, it cannot be chosen again). If you need to consider repetitions, you can explore the COMBINA function. Happy Excel-ing! 

CODE formula in Excel with examples

The CODE function in Excel returns the numeric code for a given character. Let’s explore some examples:

1. Finding the Code for a Character:

   • To get the code for the letter “a”, use:

     =CODE("a")  // Returns 97
     

   • If you have the character “a” in cell A1, the formula below gives the same result:

     =CODE(A1)  // Also returns 97
     

2. Using CODE to Find Character Codes:

   • Suppose you have data with characters like “A”, “a”, “0”, and “-11”. To find their codes, use:

     =CODE("Anand")  // Returns 65 (code for character 'A')
     =CODE("An")     // Also returns 65
     

Remember that the CODE function provides the code for the first character only in the given string. Happy Excel coding!

CLEAN formula in Excel with examples

The CLEAN function in Excel is a handy text function that removes non-printable characters or line breaks from a dataset. Let’s explore some examples of how to use it:

1. Removing Non-Printable Characters: Suppose you have email IDs with non-printable characters (e.g., CHAR(15), CHAR(12)). To get the real email IDs, use this formula:

   =CLEAN(C5)
   

   Here, C5 represents the imported email ID. Drag down the fill handle to apply the formula to other cells.

2. Removing Line Breaks: If you want to remove line breaks from student names, use the same CLEAN function:

   =CLEAN(C5)
   

   Replace C5 with the student name cell reference.

3. Removing Extra Spaces: Sometimes, you may have extra spaces (CHAR(32)) in text strings. Combine CLEAN with TRIM to remove non-printable characters and extra spaces:

   =TRIM(CLEAN(C5))
   

   Again, replace C5 with the relevant cell reference.

Remember that the CLEAN function only removes non-printable characters represented by numbers 0 to 31 in the 7-bit ASCII code. Happy cleaning! 

CHOOSE formula in Excel with examples

The CHOOSE function in Excel is quite versatile. It allows you to retrieve a value from a list based on a specified position. Let’s explore its syntax and some practical examples:

1. Syntax:

   • The CHOOSE function has the following structure:

     =CHOOSE(index_num, value1, [value2], ...)
     

   • index_num (required): Specifies the position of the value to return. It can be any number between 1 and 254, a cell reference, or another formula.
   • value1, value2, …: A list of up to 254 values from which to choose. value1 is required, while other values are optional. These can be numbers, text values, cell references, formulas, or defined names.

2. Example:

   • Let’s say we have the following list: “Mike,” “Sally,” “Amy,” and “Neal.”

     =CHOOSE(3, "Mike", "Sally", "Amy", "Neal")
     

     The formula returns “Amy” because index_num is 3, and “Amy” is the 3rd value in the list.

3. Advanced Uses:

   • Alternative to Nested IFs: Instead of using nested IF statements, the CHOOSE function can simplify your logic.
   • Random Data Generation: Combine CHOOSE with RANDBETWEEN to generate random data.
   • Left Lookup: Use VLOOKUP and CHOOSE for left lookups.
   • Get Day/Month Names from Dates: Extract day or month names using CHOOSE.

Remember, the CHOOSE function is a powerful tool for customizing your Excel calculations!

CHAR formula in Excel with examples

The CHAR function in Excel is a handy tool for translating ASCII code page numbers into actual characters. Let’s explore how it works and provide some examples:

1. Syntax:

   • The CHAR function has the following structure:

     =CHAR(number)
     

   • number: An integer between 1 and 255 representing the character code.

2. Examples:

   • To get the letter “A”, use:

     =CHAR(65)   // Result: "A"
     

   • For an exclamation mark “!”, use:

     =CHAR(33)   // Result: "!"
     

   • To create a dollar sign “$”, use:

     =CHAR(36)
     

   • And for a semicolon “;”, use:

     =CHAR(59)
     

Remember, the CHAR function lets you work with specific characters that might be awkward to type directly in your formulas! 

CELL formula in Excel with examples

The CELL function in Excel provides valuable information about cell properties, formatting, and content. Let’s explore its syntax and some practical examples:

1. Syntax:

   • The CELL function has the following structure:

     =CELL(info_type, [reference])
     

   • info_type (required): Specifies the type of information to retrieve about the cell.
   • reference (optional): Refers to the cell for which you want information. If omitted, it defaults to the last changed cell on the sheet.

2. Common info_type values:

   • "address": Returns the cell address as text (e.g., “$A$1”).
   • "col": Provides the column number of the cell.
   • "contents": Retrieves the cell’s value (including calculated results for formulas).
   • "filename": Gives the full path and filename of the workbook containing the cell.
   • "format": Corresponds to the cell’s number format (see format codes).
   • "protect": Indicates whether the cell is locked (1 for locked, 0 for unlocked).

3. Examples:

   • To get the column number for cell C10:

     =CELL("col", C10)   // Result: 3
     

   • To obtain the address of cell A1 as text:

     =CELL("address", A1)   // Result: "$A$1"
     

   • To retrieve the full path and workbook name for the current worksheet:

     =CELL("filename", A1)   // Example: "C:\MyDocs\Budget.xlsx"
     

Remember, the CELL function empowers you to extract essential details about your Excel data!