« Excel Master School - 2014 (enrollments are started) | Count Words in a Cell - "5" » |
Combine All Text Cells into One Cell (Quick Tip)
excel_usage, How-to's, Excel TipsHi there,
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:
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,
You must be logged in to see the comments. Log in now!
If you have no account yet, you can register now! (It only takes a few seconds)
Recent comments