Concatenating with Left padded Zeros in MS Excel

You can use the formatting options in the TEXT() function. The syntax is TEXT(value, format_text), so in your example you'd use a formula like: =TEXT(A1,"00000") To join the two numeric strings together =CONCATENATE("1027", TEXT(A1,"00000")) See: http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx

OR   The right function will do what you want, if you put a bunch of zeroes before your number. If you want 5 digits total, you'd do something like this: =right("00000" & 45, 5) This would output 00045. If you are putting another number before it, you can just keep concatenating, like this: =1027 & right("00000" & 45, 5) You can, of course, replace any of those values with cell references to make it all more dynamic.

Change in Train Services

Change in Train services between VRI &ALU to accoumudate the double line track modification works on 26th to 28th March 2017.

How to generate random date quickly in Excel?

Generate random date in cells with Function

Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

---

In Excel, you can mix the Randbetween and Date function to create random date, please do as follows:

1. Select a cell that you want to insert a random date, and enter this function: =RANDBETWEEN(DATE(2013, 1, 1),DATE(2013, 3, 1)).

Note: in the function, (2013, 1, 1) is the starting date, and (2013, 3, 1) is the ending date, you can replace them as you need.

2. Then press Enter key, it displays a five-digit number in the cell, and you need to convert this format to the date format.

3. Select the cell, and right-click, choose Format Cells from the context menu, see screenshot:

4. And in the Format Cells dialog box, click Number tab, and select Date from the Category, then select a date format you need from the Type drop-down list. See screenshot:

5. Click OK, the number has been converted to the normal date format. Then click the cell and drag the fill handle over to the range that you want to fill with random date. And the date has been generated randomly. See screenshot:

Merging CSV Files without Excel

his is a trick which can save you a lot of time when working with a dataset spread across multiple CSV files. Using a simple CMD command it is possible to combine all the CSV’s into a single entity ready for all your pivot and table wizardry.

Step 1

Save all of the CSV files into a single folder. Make sure that the folder is free from any CSV’s you do not want included in the compression.

Step 2

Navigate to “Run” from the Windows Start Menu

• On XP this is located in the Start Menu itself
• In Vista / Win 7 you may have to navigate first to “Accessories” to find the Run executable.

Type “cmd” into the executable field and hit Enter.

Step 3

When the CMD window opens; you will be presented with your default document folder housing all of your personal files. From here you need to execute the “cd” command which navigates to your desired folder. Type “cd” after the chevron followed by a space then your desired folder location including the drive architecture. For example if your cmd window opens with “C:\Users\Your Name>” pre-populated, the complete command line would read: “C:\Users\Your Name>cd C:Desired Folder”.

Top Tip – if you navigate to your desired folder in Windows Explorer (e.g. clicking My Documents from the Desktop) you can see the folder location path in the top of the window. However, don’t close it – you cannot copy & paste into CMD so you will need to type the folder path out!

Step 4

Once you have entered the command line and hit Enter, the desired folder location will appear on the subsequent line. Now you need to use the “copy” function to merge all the CSV files together. In a similar fashion to the “cd” command, type “copy” after the chevron, followed by a space then “combine.csv”. This copies the data from all CSV files in that location into a single file called ‘combine.csv’.

Top Tip: Because the CSV file format cannot support multiple tabs all of your data will be copied into one worksheet within the CSV workbook. For this reason it may be worth collating all your data in a similar structure – so as to avoid large amounts of formatting work at the end.

Step 5

Once executed you will be presented with confirmation; outlining which files have been copied into a single entity. Navigate to your destination folder and enjoy the fruits of your CSV-based labour in combine.csv!

Distinct Values from Excel

Some time we may have some requirement to extract the distinct values from an excel column. There is no direct formulas to do that. Instead of that we can do this in 2 ways. one is through a simple VB code and the second one is through Advanced Filter. This is applicable for Excel 2007 and higher versions as of my knowledge.

Solution 1: VB Program:

1. Press Alt + F11 to open VB editor window

2. Go to Insert menu >> Module

3. Copy and paste the below vba code into the window

 

Option Explicit

Sub CreateUniqueList()
Dim lastrow As Long

lastrow = Cells(Rows.Count, "B").End(xlUp).Row
   
    ActiveSheet.Range("B2:B" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=ActiveSheet.Range("D2"), _
    Unique:=True
    
End Sub

 Here it takes the distinct values from Column B and writing into Column D. Change accordingly.

 

 

Solution 2: Advanced Filter:

 

1. Go to Data tab in the menu
2. In Sort and Filter box, Click Advanced button 
3. Choose "Copy to another location"
4. In "List range :" box, select a range from which unique values need to be extracted (including header)
5. In "Copy to :" box, select a range in which final output to be put
6. Check Unique records only
7. Click Ok

 

Number of rows in Excel 2007

By Default we can able to see only 65536 rows in a New Excel work book in Excel 2007. But according to MSDN, we should be able to see 1 million (10Lakh) rows in Excel 2007. To utilize the maximum, open a new work book save as Excel format (XLSX). Now important think is Close and reopen the book, you can able to see more than 1 million rows in that.