Combine or Concatenate All Rows Data in One Cell !!

Combine All Rows Data in One Cell !!

Need to combine all Rows data in One Cell . Are you tired to use notepad for this for sure .

Here we have simple formula Itself in Excel to make your life easy :

Let's Begin: Below Image is the Example:




Here's the Steps for it :

Step 1: 
Put Formula :
=CONCATENATE(transpose(A1:A100)) in formula bar, but DO NOT Press ENTER key. Instead Select transpose(A1:A100) and press F9. As Shown in below image. Once you pressed F9, Remove array ({,} this sign is in the beginning and in the end of transpose function) from Transpose function. See below image for reference.





Finally :

After removing Array then click Enter ,

Here we get the result .

Thank You!!

Have a Nice Day !!

Get All File-Names from Folder in Excel Spreadsheet !!

Get All File-Names from Folder in Excel Spreadsheet !!

It's very simple way to get all file-names from folder.

Below Image Shows All the File-Names that we want to Extract in Excel sheet :

Here is the Steps:

Step 1

 Open Browser (Any). In Example Google Drive  is opened . Placed file path as shown below image and open it.

 

Step 2 :
Once File opened in Google Drive as Above Image . Select Ctrl+A and Copy Ctrl+C

Step 3 :
Then, Open Excel Spreadsheet , Right-Click and Paste Special as Text

And here we go , 

Thank You !!

Have a Nice Day !!

Formula to link all the File Names in Excel.

Formula to link all the File Names in Excel:

You have the list of file-names,  and you want to link all the files to their destined path so that you can easily explore it just by clicking file-name on Excel Sheet instead always go to destined path and check file from there.

Here is the simple way,

Example: Below Image shows the list of file-name (which is static) in excel sheet (Sheet1) and what we want is when we click the file name , it opens from the destined path ("C:\sample"):

Here is the formula ,

=HYPERLINK("C:"&"\sample"&"\"&Sheet1!A2&".txt",Sheet1!A2) , drag down formula in sheet 2 and when you click it , i will open the file like below image.

Formula Explanation: 

• HYPERLINK(link_location,[friendly_name])  >>> 

1. Link_location is link to the destined path which is  -"C:"&"\sample"&"\"&Sheet1!A2&".txt"    >>> means Concatenate Destined path (you can also use Concatenate function) .
2. [friendly_name] >>> Name which you want to give and it will display in the cell. If ignore, the it will show the file path address (Example: C:\sample\1.txt). But if you define like we define Sheet1!A2, which shows file-name as shown in image above.

Thank You!!

Have a Nice Day !!