Combine Text in Cells
Here’s another little-known feature available in Excel that I bet you’ll find various uses for. It is the ability to combine text from multiple cells into one cell.
I use it frequently for joining text when I receive a file that has first and last names in separate columns. This feature allows me to easily combine the last name from one column with the first name of the other column and show the result as a full name in another column.
We have first name in column B and last name in column C
In cell D1 enter “Full Name” as your column heading.
In cell D2 enter either of the following formulas:
=B2 & ” ” & C2 (will give you “FirstName LastName”)
=C2 & “, ” & B2 (will result in “LastName, FirstName”)
nice one
Hi Raghu, i a query. I have name with more than 3 word..Such as Raja Ram Baba Thakur or 5 words. then how to get the middle word such as ram and baba.. using text function..
Hi Srinivas, sorry for the late reply. if you are still looking for the formula. check below
=MID(A1,FIND(" ",A1,1)+1,FIND("☃",SUBSTITUTE(A1," ","☃",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(" ",A1,1))