Advance Excel Formulas

1. SUM

Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)

The SUM formula does exactly what you would expect. It allows you to add 2 or more numbers together. You can use cell references as well in this formula.

The above shows you different examples. You can have numbers in there separated by commas and it will add them together for you, you can have cell references and as long as there are numbers in those cells it will add them together for you, or you can have a range of cells with a colon in between the 2 cells, and it will add the numbers in all the cells in the range.

2. COUNT

Formula: =COUNT(A1:A10)

The count formula counts the number of cells in a range that have numbers in them.

This formula only works with numbers though:

It only counts the cells where there are numbers.

3. COUNTA

Formula: =COUNTA(A1:A10)

Counts the number of non-empty cells in a range. It will count cells that have numbers and/or any other characters in them.

The COUNTA Formula works with all data types.

It counts the number of non-empty cells no matter the data type.

4. LEN

Formula: =LEN(A1)

The LEN formula counts the number of characters in a cell. Be careful though! This includes spaces.

Notice the difference in the formula results: 10 characters without spaces in between the words, 12 with spaces between the words.

5. TRIM

Formula: =TRIM(A1)

Gets rid of any space in a cell, except for single spaces between words. I’ve found this formula to be extremely useful because I’ve often run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of legitimate data. This can wreak havoc if you are trying to compare using IF statements or VLOOKUP’s.

I added in an extra space behind “I Love Excel”. The TRIM formula removes that extra space. Check out the character count difference with and without the TRIM formula.

6. RIGHT, LEFT, MID

Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters).

(Note: In all of these formulas, wherever it says “text” you can use a cell reference as well)

These formulas return the specified number of characters from a text string. RIGHT gives you the number of characters from the right of the text string, LEFT gives you the number of characters from the left, and MID gives you the specified number of characters from the middle of the word. You tell the MID formula where to start with the start_number and then it grabs the specified number of characters to the right of the start_number.

I used the LEFT formula to get the first word. I had it look in cell A1 and grab only the 1st character from the left. This gave us the word “I” from “I love Excel”

I used the MID formula to get the middle word. I had it look in cell A1, start at character 3, and grab 5 characters after that. This gives us just the word “love” from “I love Excel”

I used the RIGHT formula to get the last word. I had it look at cell A1 and grab the first 6 characters from the right. This gives us “Excel” from “I love Excel”

7. VLOOKUP

Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

By far my most used formula. The official description of what it does: “Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify…”. (See the full explanation of VLOOKUP) Basically, you define a value (the lookup_value) for the formula to look for. It looks for this value in the leftmost column of a table (the table_array).

Note: If at all possible use a number for the lookup_value. This makes it a lot easier to make sure the data you are getting back is a correct match.

If it finds a match of the “lookup_value” in the left column of the “table_array” it will return the value in the column you specify using the “index_num”. The “index_num” is relative to the left most column. So, if you have the table_index look in column A and you want what is returned to be what’s in column B the “index_num” would be 2 because the leftmost column, column A in this case, is the 1st column in the table array and column B is the 2nd column (hence the 2 for the index number). If you want what is in column C to be returned you’d put 3 for the index_num. The “range_lookup” is a TRUE or FALSE value. If you put TRUE it will give you the closest match. If you put FALSE it will only give you an exact match. I only use FALSE when using the VLOOKUP formula.

Example:

You have 2 lists: 1 with a sales person’s ID and the sales revenue for the quarter. Another with the sales person’s ID and the sales person’s name. You want to match up the sales person’s name to the sales person’s revenue numbers for the quarter. They are all jumbled around so to manually match this, even for a small number of salesmen would leave room for a high margin of error and take a lot of time.

The first list goes from A1 to B13. The 2nd list goes from D1 to E25.

In cell C1 I would put the formula =VLOOKUP(B18, $A$1:$B$13, 2, FALSE)

B18 = the lookup_value (the sales person’s ID. This is a number that appears on both lists.)

$A$1:$B$13 = the “table_array”. This is the area I want the formula to search the leftmost column (column E in this case) for the “lookup_value”. I went to F because if it finds match in column E, I want it to return what’s in column F. (The money signs are there so that the table_array will stay the same no matter where the formula is moved or copied to. This is called an absolute reference.)

2 = the index_num. This tells the formula the number of columns away from the left most column to return in case of match. So, if you find a match between the lookup_value and the leftmost column of the table array, return what’s in the same row in the 2nd column of the table (the 1st column is always the leftmost column. It starts at 1, not 0).

FALSE= tells the formula I want it to only return the value if it’s an exact match.

I would then copy and paste that formula along all the cells in column C next to the first list. This would give me a perfectly aligned list with the sales person’s ID, sales person’s revenue for the quarter, and the sales person’s name.

In order to get a nice neat list of Sales Person ID, Sales Person Name, and Sales Person Revenue all next to each other I used the VLOOKUP formula to compare 1 list to another.

This is a complicated formula, but an extremely useful one. Check out some other examples: Vlookup Example, Microsoft’s Official Example.

8. IF Statements

Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)

When you’re doing an analysis of a lot of data in Excel there are a lot of scenarios you could be trying to discover and the data has to react differently based on a different situation.

Continuing with the sales example: Let’s say a salesperson has a quota to meet. You used VLOOKUP to put the revenue next to the name. Now you can use an IF statement that says: “IF the salesperson met their quota, say “Met quota”, if not say “Did not meet quota” (Tip: saying it in a statement like this can make it a lot easier to create the formula, especially when you get to more complicated things like Nested IF Statements in Excel).

It would look like this:

In the example with the VLOOKUP we had the revenue in column B and the person’s name in column C (brought in with the VLOOKUP). We could put their quota in column D and then we’d put the following formula in cell E1:

=IF(C3>D3, “Met Quota”, “Did Not Meet Quota”)

This IF statement will tell us if the first salesperson met their quota or not. We would then copy and paste this formula along all the entries in the list. It would change for each sales person.

Having the result right there from the IF statement is a lot easier than manually figuring this out.

9. SUMIF, COUNTIF, AVERAGEIF

Formulas: =SUMIF(range, criteria, sum_range), =COUNTIF(range, criteria), =AVERAGEIF(range, criteria, average_range)

These formulas all do their respective functions (SUM, COUNT, AVERAGE) IF the criteria are met. There are also the formulas: SUMIFS, COUNTIFS, AVERAGEIFS where they will do their respective functions based on multiple criteria you give the formula.

I use these formulas in our example to see the average revenue (AVERAGEIF) if a person met their quota, Total revenue (SUMIF) for the just the sales people who met their quota, and the count of sales people who met their quota (COUNTIF)

10. CONCATENATE

A fancy word for combining data in 2 (or more) different cells into one cell. This can be done with the Concatenate excel formula or it can be done by simply putting the & symbol in between the two cells. If I have “Steve” in cell A1 and “Quatrani” in cell B1 I could put this formula: =A1&” “&B1 and it would give me “Steve Quatrani”. (The “ “ puts a space in between what you are combining with the &). I can use =concatenate(A1, “ “, B1) and it will give me the same thing: “Steve Quatrani”

Finding The Right Excel Formulas For The Job

There are 316 built in functions in Excel. You’re not going to sit there and memorize what all of them do (or at least I hope not!). Luckily Excel has a built in wizard that helps you find the correct formula for what you’re looking to do (if there is one).

Click the “fx” next to the formula bar in Excel

This brings up a menu and in there you can type in a description of what you are trying to do and it will bring up the correct excel formula:

I typed in “remove extra spaces” and it returned the TRIM formula that we went over earlier.

More Excel Formulas

There is so much more that I use on a regular basis such as Time formulas (NOW, TODAY, MONTH, YEAR, DAY, etc.), other formulas like AND and OR, along with many others.

The real power comes in combining these functions into complicated excel formulas.

Breaking Down Complicated Excel Formulas

=IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “, “”),””)), “”)

SUMIF

Let's say you want to determine the profit you generated from a list of leads who are associated with specific area codes, or calculate the sum of certain employees' salaries -- but only if they fall above the a particular amount. Doing that manually sounds a bit time-consuming, to say the least.

With the SUMIF function, it doesn't have to be -- you can easily add up the sum of cells that meet a certain criteria, like in the salary example above.

• The formula: =SUMIF(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • Sum_range: The range of cells you're going to add up.
  • Criteria_range1: The range that is being tested using Criteria1.
  • Criteria1: The criteria that determine which cells in Criteria_range1 will be added together.

In the example below, we wanted to calculate the sum of the salaries that were greater than $70,000. The SUMIF function added up the dollar amounts that exceeded that number in the cells C3 through C12, with the formula =SUMIF(C3:C12,">70,000").

TRIM

Email and file sharing are wonderful tools in today's workplace. That is, until one of your colleagues sends you a worksheet with some really funky spacing. Not only can those rogue spaces make it difficult to search for data, but they also affect the results when you try to add up columns of numbers.

Rather than painstakingly removing and adding spaces as needed, you can clean up any irregular spacing using the TRIM function, which is used to remove extra spaces from data (except for single spaces between words).

• The formula: =TRIM("Text")
  • Text: The text from which you want to remove spaces.

Here's an example of how we used the TRIM function to remove extra spaces after a name on our list. To do so, we entered =TRIM("Steve Peterson") into the Formula Bar.

LEFT, MID, and RIGHT

Let's say you have a line of text within a cell that you want to break down into a few different segments. Rather than manually retyping each piece of the code into its respective column, users can leverage a series of string functions to deconstruct the sequence as needed: LEFT, MID, or RIGHT.

LEFT:

• Purpose: Used to extract the first X numbers or characters in a cell.
• The formula: =LEFT(text, number_of_characters)
  
  • Text: The string that you wish to extract from.
  • Number_of_characters: The number of characters that you wish to extract starting from the left-most character.

In the example below, we entered =LEFT(A2,4) into cell B2, and copied it into B3:B6. That allowed us to extract the first 4 characters of the code.

MID:

• Purpose: Used to extract characters or numbers in the middle based on position.
• The formula: =MID(text, start_position, number_of_characters)
  
  • Text: The string that you wish to extract from.
  • Start_position: The position in the string that you want to begin extracting from. For example, the first position in the string is 1.
  • Number_of_characters: The number of characters that you wish to extract.

In this example, we entered =MID(A2,5,2) into cell B2, and copied it into B3:B6. That allowed us to extract the two numbers starting in the fifth position of the code.

RIGHT:

• Purpose: Used to extract the last X numbers or characters in a cell.
• The formula: =RIGHT(text, number_of_characters)
  
  • Text: The string that you wish to extract from.
  • Number_of_characters: The number of characters that you want to extract starting from the right-most character.

For the sake of this example, we entered =RIGHT(A2,2) into cell B2, and copied it into B3:B6. That allowed us to extract the last two numbers of the code.

VLOOKUP

This one is an oldie, but a goodie -- and it's a bit more in depth than some of the other formulas we've listed here. But it's especially helpful for those times when you have two sets of data on two different spreadsheets, and want to combine them into a single spreadsheet.

My colleague, Rachel Sprung -- whose "How to Use Excel" tutorial is a must-read for anyone who wants to learn -- uses a list of names, email addresses, and companies as an example. If you have a list of people's names next to their email addresses in one spreadsheet, and a list of those same people's email addresses next to their company names in the other, but you want the names, email addresses, and company names of those people to appear in one place -- that's where VLOOKUP comes in.

Note: When using this formula, you must be certain that at least one column appears identically in both spreadsheets. Scour your data sets to make sure the column of data you're using to combine your information is exactly the same, including no extra spaces.

• The formula: VLOOKUP(lookup value, table array, column number, [range lookup])
  • Lookup Value: The identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In Sprung's example that follows, this means the first email address on the list, or cell 2 (C2).
  • Table Array: The range of columns on Sheet 2 you're going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you're trying to copy to Sheet 1. In our example, this is "Sheet2!A:B." "A" means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The "B" means Column B, which contains the information that's only available in Sheet 2 that you want to translate to Sheet 1.
  • Column Number: The table array tells Excel where (which column) the new data you want to copy to Sheet 1 is located. In our example, this would be the "House" column, the second one in our table array, making it column number 2.
  • Range Lookup: Use FALSE to ensure you pull in only exact value matches.
• The formula with variables from Sprung's example below:=VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this example, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let's say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1. Here's how that would work:

IF

There are times when we want to know how many times a value appears in our spreadsheets. But there are also those times when we want to find the cells that contain those values, and input specific data next to it.

We'll go back to Sprung's example for this one. If we want to award 10 points to everyone who belongs in the Gryffindor house, instead of manually typing in 10's next to each Gryffindor student's name, we'll use the IF-THEN formula to say: If the student is in Gryffindor, then he or she should get ten points.

• The formula: IF(logical_test, value_if_true, value of false)
  • Logical_Test: The logical test is the "IF" part of the statement. In this case, the logic is D2="Gryffindor." Make sure your Logical_Test value is in quotation marks.
  • Value_if_True: If the value is true -- that is, if the student lives in Gryffindor -- this value is the one that we want to be displayed. In this case, we want it to be the number 10, to indicate that the student was awarded the 10 points. Note: Only use quotation marks if you want the result to be text instead of a number.
  • Value_if_False: If the value is false -- and the student does not live in Gryffindor -- we want the cell to show "0," for 0 points.
• Formula in below example: =IF(D2="Gryffindor","10","0")

 RANDOMIZE

There's a great article that likens Excel's RANDOMIZE formula to shuffling a deck of cards. The entire deck is a column, and each card -- 52 in a deck -- is a row. "To shuffle the deck," writes Steve McDonnell, "you can compute a new column of data, populate each cell in the column with a random number, and sort the workbook based on the random number field."

In marketing, you might use this feature when you want to assign a random number to a list of contacts -- like if you wanted to experiment with a new email campaign and had to use blind criteria to select who would receive it. By assigning numbers to said contacts, you could apply the rule, “Any contact with a figure of 6 or above will be added to the new campaign.”

• The formula: RAND()
  • Start with a single column of contacts. Then, in the column adjacent to it, type “RAND()” -- without the quotation marks -- starting with the top contact’s row.
• For the example below: RANDBETWEEN(bottom,top)
  • RANDBETWEEN allows you to dictate the range of numbers that you want to be assigned. In the case of this example, I wanted to use one through 10.
  • bottom: The lowest number in the range.
  • top: The highest number in the range,
• Formula in below example: =RANDBETWEEN(1,10)

Excel CONCATENATE function

The CONCATENATE function in Excel is designed to join different pieces of text together or combine values from several cells into one cell.

The syntax of Excel CONCATENATE is as follows:

CONCATENATE(text1, [text2], …)

Where text is a text string, cell reference or formula-driven value.

Below you will find a few examples of using the CONCATENATE function in Excel.

Concatenating the values of several cells

The simplest CONCATENATE formula to combine the values of cells A1 and B1 is as follows:

=CONCATENATE(A1, B1)

Please note that the values will be knit together without any delimiter, as in row 2 in the screenshot below.

To separate the values with a space, enter " " in the second argument, as in row 3 in the screenshot below.

=CONCATENATE(A1, " ", B1)

To separate the concatenated values with other delimiters such as a comma, space or slash, please see Excel CONCATENATE formulas with special characters.

Concatenating a text string and cell value

There is no reason for the Excel CONCATENATE function to be limited to only joining cells' values. You can also use it to concatenate various text strings to make the result more meaningful. For example:

=CONCATENATE(A1, " ", B1, " completed")

The above formula informs the user that a certain project is completed, as in row 2 in the screenshot below. Please notice that we add a space before the word " completed" to separate the concatenated text strings.

Naturally, you can add a text string in the beginning or in the middle of your Concatenate formula as well:

=CONCATENATE("See ", A1, " ", B1)

A space (" ") is added in between the combined values, so that the result displays as "Project 1" rather than "Project1".

Concatenating a text string and a formula-calculated value

To make the result returned by some formula more understandable for your users, you can concatenate it with a text string that explains what the value actually is.

For example, you can use the following formula to return the current date:

=CONCATENATE("Today is ",TEXT(TODAY(), "dd-mmm-yy"))

Using CONCATENATE in Excel - things to remember

To ensure that your CONCATENATE formulas always deliver the correct results, remember the following simple rules:

• Excel CONCATENATE function requires at least one "text" argument to work.
• In a single CONCATENATE formula, you can concatenate up to 255 strings, a total of 8,192 characters.
• The result of the CONCATENATE function is always a text string, even when all of the source values are numbers.
• Excel CONCATENATE does not recognize arrays. Each cell reference must be listed separately. For example, you should write =CONCATENATE(A1, A2, A3) instead of =CONCATENATE(A1:A3).
• If at least one of the CONCATENATE function's arguments is invalid, the formula returns a #VALUE! error.

"&" operator to concatenate strings in Excel

In Microsoft Excel, & operator is another way to concatenate cells. This method come in very handy in many scenarios because typing the ampersand sign (&) is much quicker than typing the word "concatenate" :)

Similarly to the CONCATENATE function, you can use "&" in Excel to combine different text strings, cell values and results returned by other functions.

Excel "&" formula examples

To see the concatenation operator in action, let's re-write the CONCATENATE formulas discussed above:

Concatenate the values in A1 and B1:
=A1&B1

Concatenate the values in A1 and B1 separated with a space:
=A1&" "&B1

Concatenate the values in A1, B1 and a text string:
=A1 & B1 & " completed"

Concatenate a string and the result of the TEXT / TODAY function:
="Today is " & TEXT(TODAY(), "dd-mmm-yy")

As demonstrated in the screenshot below, the CONCATENATE function and "&" operator return identical results:

Excel "&" operator vs. CONCATENATE function

Many users wonder which is a more efficient way to concatenate strings in Excel - CONCATENATE function or "&" operator.

The only essential difference between CONCATENATE and "&" operator is the 255 strings limit of the Excel CONCATENATE function and no such limitations when using the ampersand. Other than that, there is no difference between these two concatenation methods, nor is there any speed difference between the CONCATENATE and "&" formulas.

And since 255 is a really big number and in real-life tasks someone will hardly ever need to combine that many strings, the difference boils down to the comfort and ease of use. Some users find CONCATENATE formulas easier to read, I personally prefer using the "&" method. So, simply stick to the concatenation technique that you feel more comfortable with.

Concatenate cells with a space, comma and other characters

In your worksheets, you may often need to join values in a way that includes commas, spaces, various punctuation marks or other characters such as a hyphen or slash. To do this, simply include the character you want in your concatenation formula. Remember to enclose that character in quotation marks, as demonstrated in the following examples.

Concatenating two cells with a space:

=CONCATENATE(A1, " ", B1) or =A1 & " " & B1

Concatenating two cells with a comma:

=CONCATENATE(A1, ", ", B1) or =A1 & ", " & B1

Concatenating two cells with a hyphen:

=CONCATENATE(A1, "-", B1) or =A1 & "-" & B1

The following screenshot demonstrates how the results may look like:

Concatenate text strings with line breaks

Most often, you would separate the concatenated text strings with punctuation marks and spaces, as shown in the previous example. In some cases, however, may need to separate the values with a line break, or carriage return. A common example is merging mailing addresses from data in separate columns.

A problem is that you cannot simply type a line break in the formula like a usual character, and therefore a special CHAR function is needed to supply the corresponding ASCII code to the concatenation formula:

• On Windows, use CHAR(10) where 10 is the ASCII code for Line feed.
• On the Mac system, use CHAR(13) where 13 is the ASCII code for Carriage return.

In this example, we have the address pieces in columns A through F, and we are putting them together in column G by using the concatenation operator "&". The merged values are separated with a comma (", "), space (" ") and a line break CHAR(10):

=A2 & " " & B2 & CHAR(10) & C2 & CHAR(10) & D2 & ", " & E2 & " " & F2

Note. When using line breaks to separate the concatenated values, you must have the "Wrap text" option enabled for the result to display correctly. To do this, press Ctrl + 1 to open the Format Cells dialog, switch to the Alignment tab and check the Wrap text box.

In the same manner, you can separate concatenated strings with other characters such as:

• Double quotes (") - CHAR(34)
• Forward slash (/) - CHAR(47)
• Asterisk (*) - CHAR (42)
• The full list of ASCII codes is available

Though, an easier way to include printable characters in the concatenation formula is to simply type them in double quotes as we did in the previous example.

Either way, all four of the below formulas yield identical results:

=A1 & CHAR(47) & B1

=A1 & "/" & B1

=CONCATENATE(A1, CHAR(47), B1)

=CONCATENATE(A1, "/", B1)

How to concatenate columns in Excel

In order to concatenate two or more columns in Excel, you just enter a usual concatenation formula in the first cell, and then copy it down to other cells by dragging the fill handle (the small square that appears in the lower right hand corner of the selected cell).

For example, to concatenate two columns (column A and B) separating the values with a space, you enter the following formula in cell C2, and then copy it down to other cells. When you are dragging the fill handle to copy the formula, the mouse pointer changes to a cross, as shown in the screenshot below:

Tip. A quick way to copy the formula down to other cells in the column is to select the cell with the formula and double-click the fill handle.

Please note that Microsoft Excel determines how far to copy cells after the fill handle double click based on the cells referred to by your formula. If there happen to be empty cells in your table, say cell A6 and B6 were blank in this example, the formula would be copied up to row 5 only. In this case, you would need to drag the fill handle down manually to concatenate the entire columns.

An alternative way to concatenate columns in Excel is to use the corresponding option of the Merge Cells add-in.

How to concatenate a range of cells in Excel

Combining values from multiple cells might take some effort because the Excel CONCATENATE function does not accept arrays and requires a single cell reference in each argument.

To concatenate several cells, say A1 to A4, you need either of the following formulas:

=CONCATENATE(A1, A2, A3, A4)
or

=A1 & A2 & A3 & A4

When joining a fairly small range, it's no big deal to enter all the references in the formula bar. A large range would be tedious to add, typing each cell reference manually. Below you will find 3 methods of quick range concatenation in Excel.

Method 1. Press CTRL to select multiple cells to be concatenated

To quickly select several cells, you can press the CTRL key and click on each cell you want to include in the CONCATENATE formula. Here are the detailed steps:

• Select a cell where you want to enter the formula.
• Type =CONCATENATE( in that cell or in the formula bar.
• Press and hold CTRL and click on each cell you want to concatenate.
• Release the CTRL button, type the closing parenthesis in the formula bar and press Enter.

Note. When using this method you must click each individual cell. Selecting a range with the mouse would add an array to the formula, which the CONCATENATE function does not accept.

Method 2. Use the TRANSPOSE function to get the range

When you need to concatenate a huge range consisting of tens or hundreds of cells, the previous method is not fast enough because it requires clicking on each cell. In this case, a better way is to use the TRANSPOSE function to return an array, and then replace it with individual cell references in one fell swoop.

• Select the cell where you want to output the concatenated range.
• Enter the TRANSPOSE formula in that cell, =TRANSPOSE(A1:A10) in this example.
• In the formula bar, press F9 to replace the formula with calculated values.
• Delete the curly braces that turn a usual Excel formula into an array formula. As a result, you will have all the cells references to be included in your concatenation formula.
  
• Type =CONCATENATE( in front of the cell references in the formula bar, type the closing parenthesis and press Enter.
  

Note. Whichever method you use, the concatenated value in C1 is a text string (notice its left-alignment in the cell), although each of the original values is a number. This is because the CONCATENATE function always returns a text string regardless of the source data type.

Method 3. Use the Merge Cells add-in

A quick and formula-free way to concatenate any range in Excel is to use the Merge Cells add-in for Excel with the "Merge all areas in selection" option turned off, as demonstrated in Combine the values of several cells into one cell.

Concatenate numbers and dates in various formats

When you concatenate a text string with a number or date, you may want to format the result differently depending on your dataset. To do this, embed the TEXT function in your Excel concatenate formula.

The TEXT(value, format_text) function has two arguments:

• In the first argument (value), you supply a number or date to be converted to text, or a reference to the cell containing a numeric value.
• In the second argument (format_text), you enter the desired format using the codes that the TEXT function can understand.

We have already discussed one such formula in the beginning of this tutorial that concatenates text and date.

I will remind you that when combining a text string and date, you have to use the TEXT function to display the date in the desired format. For example:

=CONCATENATE("Today is ", TEXT(TODAY(), "mm/dd/yy"))

or

="Today is " & TEXT(TODAY(), "mm/dd/yy")

A few more formula examples that concatenate a text value and number follow below:

=A2 & " " & TEXT(B2, "$#,#0.00") - display the number with 2 decimal places and the $ sign.

=A2 & " " & TEXT(B2, "0.#") - does not display extra zeros and the $ sign.

=A2 & " " & TEXT(B2, "# ?/???") - display the number as a fraction.

How to split cells (opposite of CONCATENATE in Excel)

If you are looking for the opposite of CONCATENATE in Excel, i.e. you want to split one cell into several cells, a few options are available to you:

• Text to Columns feature
• Flash Fill option in Excel 2013 and 2016
• Formulas (MID, RIGHT, LEFT functions)

You can find the detailed steps illustrated with formula examples and screenshots in the How to split cells in Excel tutorial.

Merge Cells add-in - formula-free way to concatenate cells in Excel

With the Merge Cells add-in for Excel, you can efficiently do both:

• Merge several cells into one without losing data.
• Concatenate the values of several cells into a single cell and separate them with any delimiter of your choosing.

The Merge Cells tool works with all Excel versions from 2003 to 2016 and can combine all data types including text strings, numbers, dates and special symbols. Its two key advantages are simplicity and speed - any concatenation is done in a couple of clicks. And now, let me show it to you in action.

Combine the values of several cells into one cell

To combine the contents of several cells, you select the range to concatenate and configure the following settings:

• Cells into one under "What to merge";
• Select the delimiter you want under "Separate values with", it's a comma and a space in this example;
• Choose where you want to place the result, and most importantly
• Uncheck the "Merge all areas in the selection" option. It is this option that determines whether the cells are merged or the cells' values are concatenated.

Combine columns row-by-row

To concatenate two or more columns, you configure the Merge Cells' settings in a similar way, but choose Columns under "What to merge":

Join rows column-by-column

To combine data in each individual row, column-by-column, you choose to merge Rows, select the delimiter you want (line break in this example), configure other settings the way you want and hit the Merge button. The result may look similar to this:

Here are some additional examples of formulas that you can enter in a worksheet.

• =A1+A2+A3    Adds the values in cells A1, A2, and A3.
• =SUM(A1:A10)    Uses the SUM function to return sum of the values in A1 through A10.
• =TODAY()    Returns the current date.
• =UPPER("hello")     Converts the text "hello" to "HELLO" by using the UPPER function.
• =IF(A1>0)    Uses the IF function to test the cell A1 to determine if it contains a value greater than 0.
•