Sat 12 Aug 2017

Downloads 16 - Sample CSV Files / Data Sets for Testing - Human Resources

By |Saturday, August 12th, 2017|Categories: Downloads, VBA|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

Disclaimer - The datasets are generated through random logic in VBA. These are not real human resource data and should not be used for any other purpose other than testing.

You can download sample csv files ranging from 100 records to 500000 records. These csv files contain data in various formats like Text, Numbers, Date, Time, Percentages which should satisfy your need for testing.

This data set can be categorized under "Human Resources" category.

Below are the fields which appear as part of these csv files as first line.

(more…)

Sat 11 Jul 2015

Tips & Tricks 99 – Remove Middle Name

By |Saturday, July 11th, 2015|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to remove the middle name. Hence, you want to have an answer Roberto Carlos here.

Formula Way -

The formula for the above case would be

=IF(COUNTIF(A1,"* * *"),LEFT(A1,FIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),"")

OR

=IF(COUNTIF(A1,"* * *"),REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1),""),"")

Manual Way -

A. Easy Way

1. Select your column.
2. CTRL+H
3. In Find What: box, put one space followed by one * followed by one space. Hence, you need to put " * " without quote.
4. In Replace With: box, put one space. Hence, you need to put " " without quote.
5. Press Replace All.
6. All Middle Names are removed.

B. Flash Fill (Only for Excel 2013) Way - If you data always contains 3 Words

1. Let's say that your first entry, Roberto Albert Carlos is in A1. Put Roberto Carlos in B1.
2. Same case do for A2 in B2 i.e. if A2 contains abc de fgh, put abc fgh in B2.
2. Select B3 and click Data tab > Flash Fill

Your column B will be filled in with Middle Name removed.

Sat 11 Jul 2015

Tips & Tricks 98 – Extract Middle Name

By |Saturday, July 11th, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to extract the middle name. We have already talked about extracting first name and last name in following Tips and Tricks. We also talked about extracting initial of a middle name.

Tips & Tricks 22 – Extract First Name from Full Name

Tips & Tricks 23 – Extract Last Name from Full Name

Tips & Tricks 24 - Extract the Initial of Middle Name

Formula Way -

The formula for extracting Middle Name would be -

=IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)),"")

OR

=IF(COUNTIF(A1,"* * *"),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1)),2),FIND(" ",A1)+1,LEN(A1))),"")

OR

=IF(COUNTIF(A1,"* * *"),LEFT(REPLACE(A1,1,FIND(" ",A1),""),FIND(" ",REPLACE(A1,1,FIND(" ",A1),""))-1))

Manual Way -

A. Flash Fill (Only for Excel 2013) Way - If you data always contains 3 Words

1. Let's say that your first entry, Roberto Albert Carlos is in A1. Put Albert in B1.
2. Select B2 and click Data tab > Flash Fill

Your column B will be filled in with Middle Name.

B. Text to Columns Way - If you data always contains 3 Words

If your list of names is always having 3 names i.e. middle name is always present, we can have a manual way also.

1. Select your column.
2. Data tab > Text to Columns > Delimited should be selected in Step 1 of 3 and Next
3. Check Space in Step 2 of 3 and Next
4. In Step 3 of 3 - Select first column and check "Do not import column (skip)", Select third column and check "Do not import column (skip)" and Finish
5. You have only middle name in your column.

1

1