Home / Excel / office

Excel Formulas and Functions to Combine Words

Post a Comment
Although Microsoft Excel is designed as a number processing application, it turns out that the formulas, functions, operators and features found in Excel can also be applied to word processing for certain purposes which cannot be done in Microsoft Word as a word processing application.

Excel Function for Combining Text

Here's a small portion of the Formulas and Functions of Excel that Relate to Text, which BossTutorial will share to all friends:
  1. Excel formulas and functions for combining words
  2. Excel formula and function to take words from sentences
  3. Excel function to cut a word
  4. Excel formula to make the initials of a name
  5. Excel function to convert lowercase letters to capital
  6. Excel function to change capital letters to small
  7. Excel function to change each initial letter of a word to capital
  8. Excel function to count the number of characters
  9. Excel function to count only a certain number of characters
  10. Excel function to graph in cells
  11. And there are still many other Excel formulas and functions related to processing words or text.

Excel Formulas and Functions for Combining Text

If you want to combine several cells that contain text or numbers into one word or become one sentence in a cell, it can be used in three ways, namely using formulas or using functions.

Suppose you have an Excel data table like below

Excel Function for Combining Text

In the table above there is a "Place of Birth" column, which is in the "D" and "Birth Date" columns in the "E" column, for example we will make it into one column, namely "Place and Date of Birth" as in "F" column the contents of the data are a combination of the two columns.



There are several tricks and ways that can be applied to combine the two Excel cell contents.









1. Combine with AutoFlash Fill

This is the fastest and most practical way to combine cell contents with Flash Fill. However, unfortunately the Auto Flash Fill feature is only found in the Excel 2013 version and above.



The steps are as follows:



In column F of the top cell (cell F3), please write the contents of the cell that you want to join, in the example above for example Purwarejo, 29-07-2000 (plus a comma)
How to Combine Text with Flash Fill



Click on the cell below (cell F4)
Click the Fill menu in the Editing group, then select Flash Fill
Then all data in the "D" column and the "E" column will automatically be combined according to the example on the top row manually created. In the example above we added comma (,) behind the city name, then the merged data below also has commas. You can add punctuation, letters, numbers or other formats as needed. Please try with various other attributes.



2. How to Combine Text with Excel Formulas

Here is how to combine text using operators or Excel formulas that can be used in all versions of Microsoft Excel.

Click on cell F3 (we refer to the example)
In cell F3 write the following Excel operator formula: = D3 & "," & E3 (after comma don't forget to give a space spacing)
Press Enter
Please copy the formula for all the cells below
The result is the same as using the Flash Fill feature as above



Excel functions and formulas for Combining Text





3. How to Combine Text with Excel Functions

In cell F3 write the following Excel formula: = CONCATENATE (D3 & "," & E3) (after comma don't forget to give a space)
Press Enter
Please copy the formula for all the cells below
The results of the three methods above are all the same, so please choose how to combine text in Excel faster and more practically













4. How to Combine Text from Many Cells

Both Excel features, formulas and functions not only can combine text from two cells, but can be used to combine multiple words (text) from many cells. For example like the following example.



Excel function to combine words



For example, we will combine all the cell contents of each row in the Excel table data above, and we will save the results of the merge below the table or whatever you want wherever you can, even on different sheets.



Steps:

Click on cell B10 which will be the place for the combined text
Type the following formula: = B3 & "bin" & E3 & "born on" & C3 & "on" & D3 & "stay on" & F3
In the formula, we add additional words to be a good sentence to read, besides that we can also change the position of the cell that wants to take precedence, and which cell will be placed at the end.



What needs to be considered in writing additional text in the formula above, that is after the double quotation mark (") gives a space spacing, then additional text, give a new double spacing double quotation (") again.



On this occasion BossTutorial only discussed number one first, "Excel Formulas and Functions to Combine Words. To discuss Excel Formulas and Functions to Cut Text and Excel Formulas to Take words from sentences, BossTutorial will discuss in the next post.



So the complete tutorial on How to Combine Text in Microsoft Excel. Hopefully this tutorial is useful, if there are suggestions or questions, don't hesitate to write it in the comments. With pleasure BossTutorial will reply as fast as we can.



Thank you for visiting the COMPLETE TUTORIAL COLLECTION blog at https://bosstutorial.com

Location:

Post a Comment for "Excel Formulas and Functions to Combine Words"