Dear Friends,

I am sharing one my latest trick I have learned today, please see below

For e,g,I have a data in Excel in the following format

And I require to consolidate in single cell i.e.MY NAME IS MUKESH KUMAR AND I LIVE IN DELHI 

Please see the steps I have used to arrive this solution

STEP:1 Count the no. of words in cell. Use the formula LEN

=LEN(A1) results 2, =LEN(A2) results 4, =LEN(A3) results 2, =LEN(A4) results 6 and so on

STEP: 2 Insert space at the of each ward, use the formula LEFT

=LEFT(A1,B1)&" " results MY ,=LEFT(A1,B1)&" " results NAME ,=LEFT(A1,B1)&" " results IS ,=LEFT(A1,B1)&" " results MUKESH , and so on

STEP: 3 Consolidation of all words, use the formula =CONCATENATE and TRANSPOSE. Important & typical step

=CONCATENATE(TRANSPOSE(C1:C10))

STEP: 4 Expose transpose, Select content of transpose formula & press F9, it would appear like this

CONCATENATE(TRANSPOSE(C1:C10)), Select content of transpose formula

and press F9 it would appear like this =CONCATENATE({"MY ","NAME ","IS ","MUKESH ","KUMAR ","AND ","I ","LIVE ","IN ","DELHI "})

please don't press enter this stage, remove { and } from formula BAR, manually

=CONCATENATE({"MY ","NAME ","IS ","MUKESH ","KUMAR ","AND ","I ","LIVE ","IN ","DELHI "})

NOW FINALLY PRESS ENTER 

ENJOY IT