- Excel formula to remove duplicates in 3 columns how to#
- Excel formula to remove duplicates in 3 columns free#
Select the column or cells containing the values you wish to split. To separate the contents of a cell across multiple cells in different columns:ġ. Many people resort to cutting and pasting the required data over or even retyping it, which can be cumbersome. We need to separate the name and surname into the Name and Surname columns respectively. To demonstrate this logic better, the screenshot below contains a column that has both the name and surname of each person in the same cell, down the same column. Having one cell which contains multiple pieces of data often happens when information has been copied over from a Microsoft Word document or other source and can even occur when someone inadvertently enters multiple strings of data into one cell instead of splitting it up logically. Imagine you have a column where each cell contains an entire address that needs to be split across cells in several columns or, a simpler instance, when you have a name and surname in the same cell and wish to separate them across two columns.
The convert text to column option allows for wide-ranging functionality to separate the contents of a single cell across multiple cells. If you copy the values in this column over to another area on the worksheet, a separate worksheet or document, you will have to ensure you paste the values and not the formula. NB: Remember that the column where you enter the formula will still contain the formula although it is displaying the desired value. Ensure you paste the formula and not the values. You can also use the Copy and Paste function to duplicate the formula down the column. Finally, copy the formula down through the column by double clicking the right bottom corner of the cell or dragging the copy handle down through the rest of the cells in the column. The completed formula (as per our example above) will look like this (see screenshot below):ĩ. Close the bracket and press the Enter key to finalize the formula.Ĩ. In our example this will be a cell in Column B.ħ. For the text3 argument in the formula, click on the cell containing the second value. If the values you are joining do not need a space, you can omit this step.Ħ. This is essential to insert a space between, for example, the name and surname values. For the text2 argument in the formula, insert quotation marks followed by a space and then another quotation mark. In our example this will be a cell in Column A.ĥ. For the text1 argument in the formula, click on the cell containing the first value. Excel will suggest the syntax for the formula for you as follows (see screenshot below):Ĥ. Next, type the equals sign (=) to start the formula, followed by the word Concatenate.ģ. In our screenshot example above, we would position the cursor in the Name & Surname Label column (column C).Ģ. Position your cursor in the cell in which you wish the result of the concatenation to appear. Concatenation allows you to quickly join together the contents of two or more cells.ġ. This solution may work fine for you if you only have a handful of names and surnames (or other words or numbers) to join, but imagine it is a worksheet containing hundreds or even thousands of names and surnames or other values.
Of course you can manually type the name and surname into column C above or use copy and paste. Imagine you needed to have the current values entered into the Name and Surname columns in a single cell for each person as per the example below (see Name & Surname Label column C): In the screenshot below, we have columns titled Name and Surname. It is easiest to demonstrate the usefulness of this function by using an example. In Excel, the concatenate formula can literally save you days of work and frustration. Although the word has possibly been in use since the late Middle Ages, its use in computer languages over the last decades has escalated its status and prevalence across different platforms.
Excel formula to remove duplicates in 3 columns free#
Test your Excel skills with the corresponding FREE Online Multiple ChoiceĬoncatenate Function, Convert Text to Columns & Remove Duplicates Excel TestĪccording to the Oxford Dictionary the word concatenate means to link things together in a series. Excel even has a built-in function to remove duplicate values to ensure your data is free from repeat entries. The converse function of concatenate is the Text to Columns function which allows you to separate text across different columns.
Use the concatenate function to combine words and even numbers that are contained across different columns into one cell. Sometimes you need to combine the contents of different cells into one, sometimes you need to split the data in a cell into separate cells.
Excel formula to remove duplicates in 3 columns how to#
Free Excel Tutorial – Concatenate, Convert Text to Columns & Remove Duplicates – Excel 2010 Concatenate Function, Convert Text to Columns & Remove Duplicates Excel Tutorialįree Online Microsoft Excel Tutorial * How to Use the Concatenate Function
0 kommentar(er)
