Don’t waste any more hours in Microsoft Excel doing things manually. There are many ways to use Excel formulas to decrease the amount of time you spend in Excel and increase the accuracy of your data and your reports.
Articles Tagged: Text Formulas
How to Combine Text from Multiple Cells
Excel is heavily dependant on formulas, and it seems like there is literally a formula for everything. For example, have you ever been working in Excel and needed to combine the contents of two different cells into a new one without adding them together? While you can copy and paste the content of the different cells you can also use the concatenate formula.
Using the concatenate formula to combine cells
If, for example, you have a spreadsheet with first names in column A, last names in column B, and want to combine them into column C to display the full name you can do so by:
Clicking on cell C2 (or the row where the information you want to combine is)
Typing =concatenate(
Clicking on cell A2 and then adding a comma (,)
Clicking on cell B2 and closing the formula with a closing bracket
Hitting Enter
You should see the two cells are now combined in cell C2, with the formula for cell C2 reading:
=CONCATENATE(A2,B2).
The problem is, there will be no space inbetween the letters or numbers, so you will need to edit the formula to read:
=CONCATENATE(A2,” “,B2)
The double quotations with a space in between them tells Excel to add a space to the cell in between the contents of A2 and B2.
If you have more than two columns you would like to combine, then simply add a comma after each cell. If for example you have three columns (A1, B1, and C1) you would enter the formula:
=CONCATENATE(A1 ” “,B1 ” “,C1) in column D1.
Combining two cells without concatenate
While concatenate works well, there is actually a shortcut that you can use which involves the ampersand ‘&’:
Click on cell C2 (or the row where the information you want to combine is)
Type =
Click on cell A2 and then type & in the formula.
Click on B2 and hit Enter
You should see the contents of A2 and B2 combined together in C2. If you click on cell C2 and look at the formula, it should read: =A2&B2.
The only problem is, there won’t be a space between the content. To add a space, you can edit the formula so that it reads:
=A2&” “&B2
Note the space between the two quotation marks. This tells Excel to add a space between the contents of A2 and B2.
Once you have the base formula on one cell, you can press the small box at the bottom of the cell and drag it down the row so that the other information can be quickly compiled. This makes it much easier than having to copy and paste the content individually. And, If you would like to learn more Excel tips, contact us today. We can save you valuable time and resources.
VBA Macro to Compare Two Files to Determine if They are Identical
This function will allow you to compare one file to another. Copy code below and paste directly into your VB project. Sub CompareTextFiles() ‘********************************************************** ‘PURPOSE: Check to see if two files are identical ‘File1 and File2 = FullPaths of files to compare ‘will compare complete content of the file, including length of the document (Bit […]
Show Indian Currency Format in Excel – Learn
Learn to show Indian Currency Format in Excel (On request of Mr. Ksan Warjri) Indian numbers are grouped differently than standard English numbers. Learn to format numbers and amounts in Indian currency and number formats. English Grouping : 123,456,789.01 Indian Grouping : 12,34,56,789.01 =============================== Quick and easy fix to show numbers in Indian format ================================= […]
Combine Text in Cells (Concatenate)
Combine Text in Cells Here’s another little-known feature available in Excel that I bet you’ll find various uses for. It is the ability to combine text from multiple cells into one cell. I use it frequently for joining text when I receive a file that has first and last names in separate columns. This feature […]
Remove Extra Spaces Between Data in Excel
Remove Extra Spaces Between Data in Excel When text data is imported or copied into an Excel spreadsheet extra spaces can sometimes be included along with the text data. The TRIM function can be used to remove the extra spaces from between words or other strings in Excel. = TRIM ( Text )