No more COMMA DRAMA! In this video I will show you how to change the default comma delimiter in Excel when making .csv (comma separated value) files. Sometimes text fields in your file have comments that you need to preserve in a flat file. This means you have to change the delimiter. Watch the video for a demonstration and keep reading for a quick reference.
I should note that as of the time of this post I am using Windows 10 Home.
Search for “Set Regional Format”
Select Additional date, time & regional settings
Select Additional Settings
Then in the List Separator box, change the comma to a pipe.
When you save your .csv file in Excel, it will now be pipe delimited instead of comma delimited.
In this video I help you solve the dual problems of selecting a random value from an Excel list and selecting a number of random rows from a range of data in Excel. At times when I am generating a data-set to use in my video tutorials, I want to select a random selection of rows. Typically, because my data values are clumped together and are too similar to the data on preceding and subsequent rows.
SELECTING A RANDOM VALUE FROM A LIST
Enter three Excel formulas to save the day for selecting a random value from a list:
The INDEX function returns a value or the reference to a value from within a table or range.
In the screenshot above notice that the ROWS() function returns the value of 20, which corresponds to the number of names listed in the cell range of A6 to A25.
The RANDBETWEEN() function generated a random number between 1 and the value returned from ROWS() (i.e., 20). In this case, RANDBETWEEN() combined with ROWS() returned a value of 3.
By combining the results from the first 2 functions, the INDEX() function searches our list and returns the value of the 3rd cell in the list (i.e., Flor McCard) because the RANDBETWEEN() function returned a value of 3.
When we put it all together it looks like the following: