exceltoxl.com

Hi there,

 Share on Facebook

Again, a great tip from one of our excel lovers is that ... wait! first we will see what is the question here. The question is that how to combine or concatenate different cells or cell ranges containing text into one single cell. Most users does this by using an inbuilt Excel function called CONCATENATE however it is a lengthy and tedious process. 

To overcome the tedious process here is how we can do to combine all text cells in to one cell.

 REQUIREMENT:

• CONCATENATE
   
• TRANSPOSE

PROCEDURE TO COMBINE MULTIPLE TEXT CELLS INTO ONE CELL: 

...

STEP1: Use function TRANSPOSE in CONCATENATE function 

=CONCATENATE(TRANSPOSE(B2:B12))

 

STEP 2: Select TRANSPOSE function by editing the cell and apply F9 key

=CONCATENATE({"Combine","all","text","cells","into","one","cell","using","quick","tip",":)"})

 

STEP 3: Remove curly brackets on both sides and click ENTER

Combinealltextcellsintoonecellusingquicktip:)

 

STEP 4: To add delimiter between the words use & " "  before pushing F9 and ENTER key within TRANSPOSE function

=CONCATENATE(TRANSPOSE(B2:B12 & " "))

 

STEP 5: That's it! Magic works!!!

 

PROCEDURE TO DIVIDE ONE STATEMENT INTO MULTIPLE TEXT CELLS: 

Similarly let's see how to arrange the sentence from one cell to different cells with each word or as is:

REQUIREMENT:

• JUSTIFY feature 

Here we go: 

STEP 1: Find the smallest length of the word

STEP 2: Arrange the cell width to the smallest length of the word through naked eye or adjust the column width to 2 (a minimum width)

STEP3: GoTo HOME -->Editing --> FILL -->JUSTIFY (make sure before clicking JUSTIFY the relevant cell should be selected) [Also, check the difference between WrapText & Justify]

STEP 4: A window appears "Text will extend below selected range", Click OK

STEP 5: Arrange the cell width back by double clicking the column divider. (Click here to know more on double click tricks using mouse)

DO YOU USE THIS TRICK?

Have you used this trick in your work. Do you have any challenges, ideas, comments, suggestions to share with our readers about this topic. Feel free to share it here in the below comments section.

IF you like this post, share it and like it on your social networking sites by clicking on the icons placed around the blog.

IF you want to support me for maintaining this blog and to receive latest updates from exceltoxl, REGISTER here for free. The information shared with us are kept confidential from the public.

SOME IMPORTANT LINKS:

COUNT WORDS IN A CELL

OVERCOME PAGEBREAKS AFTER PRINT PREVIEW

TAMPERING OUR OWN EXCEL WORKBOOK

MIRACLES HIDDEN INSIDE DATA TABLES

CAMERA TOOL HAS A SHORTCUT KEY

This post is an inspiration of Grant & Chandoo

Thanks,