This is a quick reminder for myself on opening a CSV in MS Excel but without converting long numbers into exponential notations (which are incorrectly rounded anyway).

My Data:
Account ID,Name,Phone,Record ID
  1.  Account ID,Name,Phone,Record ID 
  2.  1457102000000135533,Joel,00441234567890,1457102000000165989 
  3.  1457102000000135566,Stanley,01709123456,1457102000000167543 
  4.  1457102000000135608,David,00331234567890,1457102000000166795 
My Excel:
Account ID	Name	Phone	Record ID
1.4571E+18	Joel	4.41235E+11	1.4571E+18
1.4571E+18	Stanley	1709123456	1.4571E+18
1.4571E+18	David	3.31235E+11	1.4571E+18
  1.  Account ID    Name    Phone    Record ID 
  2.  1.4571E+18    Joel    4.41235E+11    1.4571E+18 
  3.  1.4571E+18    Stanley    1709123456    1.4571E+18 
  4.  1.4571E+18    David    3.31235E+11    1.4571E+18 

Firstly, save the CSV file as text file (*.TXT).
Category: Excel :: Article: 689

This is an article to remind me how to search a column in an Excel file for values found in another column (in this example, on another worksheet in the same workbook).

So for demonstration purposes, I'm using a new Excel file with two worksheets called "Sheet1" and "Sheet2" respectively.

This article serves to explain how to split a spreadsheet consisting of multiple sheets into separate files per sheet.

The Excel file in question was about 36000 rows and had a file size of about 11Mb. In order for an import process to work, the import would only accept XLS files no greater than 1Mb. So our script has to split a single spreadsheet into multiple worksheets of 3000 rows each, and then output each sheet to a separate file that is formatted as XLS (Not *.xlsx).

In summary, we are going to move the Excel file to a folder of its own. We're going to run two VBScripts in two stages, firstly to split the specified rows into sheets, then each sheet into a file each. And we want all the files generated to be created in the same folder.

What I have:
A CSV file exported from Excel along with double-quotes
  1.  label1,label2 
  2.  item1a,item2a 
  3.  item1c,"item2c,c" 
  4.  item1b,item2b 

What I want:
To read the file (stored on the server) and convert to a JavaScript array of objects
var my_object_array = [
     { my_col1_val: 'item1a', my_col2_val: 'item2a' },
     { my_col1_val: 'item1b', my_col2_val: 'item2b' },
     { my_col1_val: 'item1c', my_col2_val: 'item2c,c' }
  1.  var my_object_array = [ 
  2.       { my_col1_val: 'item1a', my_col2_val: 'item2a' }, 
  3.       { my_col1_val: 'item1b', my_col2_val: 'item2b' }, 
  4.       { my_col1_val: 'item1c', my_col2_val: 'item2c,c' } 
  5.  ]
What I want again:
  • Read a CSV file already uploaded with JavaScript
  • Populate a JS array with each row
  • Account for strings containing double-quotes (and commas to ignore)
  • Sort the resulting object array

Category: Excel :: Article: 635

A quick article on how to get the value of 40°4′20″N 116°35′51″E into 40.079857, 116.603112.

Let's pretend all the names are in column A, in Column B I have the coordinates that I want to convert:
A                                       B
-------------------------------------   ----------------------
Beijing Capital International Airport   40°4′20″N 116°35′51″E
Beijing Shahezhen Air Base              40°8′57″N 116°19′17″E
Beijing Tongxian Air Base               39°48′40″N 116°42′30″E
  1.  A                                       B 
  2.  -------------------------------------   ---------------------- 
  3.  Beijing Capital International Airport   40°420″N 116°3551″E 
  4.  Beijing Shahezhen Air Base              40°857″N 116°1917″E 
  5.  Beijing Tongxian Air Base               39°4840″N 116°4230″E 

Note that I tend to copy the coordinates off a website which has strange apostrophes and double-quotes. You can change this but remember to put two double-quotes if you are searching on it, eg:
FIND(""",B1)   // will NOT work!
FIND("""",B1)  // will work
FIND("″",B1)   // will work
  1.  FIND(""",B1)   // will NOT work! 
  2.  FIND("""",B1)  // will work 
  3.  FIND("″",B1)   // will work 

Category: Excel :: Article: 596

A quick article on if you want to extract the link from some text you have copied off the web and into an MS Excel document.

  1. Open up a new workbook.
  2. Get into VBA (Press Alt+F11)
  3. Insert a new module (Insert > Module)
  4. Copy and Paste the Excel user defined function below
    Sub ExtractHL()
    Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
    HL.Range.Offset(0, 1).Value = HL.Address
    End Sub
    1.  Sub ExtractHL() 
    2.  Dim HL As Hyperlink 
    3.  For Each HL In ActiveSheet.Hyperlinks 
    4.  HL.Range.Offset(0, 1).Value = HL.Address 
    5.  Next 
    6.  End Sub 
  5. Press F5 and click “Run”
  6. Get out of VBA (Press Alt+Q)
Category: Excel :: Article: 595

A quick note on how to compare two columns for values that are not found in another. I have a column with old values, and now that I have a new list, I want a quick way to see what values are in the old column and which ones are new...

Consider the 3 following columns in an Excel spreadsheet:
Old       New       Exists in Old?
--------- --------- --------------
123456    234567
234567    345678
345678    456789
567890    597890
  1.  Old       New       Exists in Old? 
  2.  --------- --------- -------------- 
  3.  123456    234567 
  4.  234567    345678 
  5.  345678    456789 
  6.  567890    597890 
I want the third column to say whether this is new or not.

Category: Excel :: Article: 551

This is a quick article on how to convert some cells in Microsoft Excel to number values...

OMG. Seriously Microsoft! I have spent an hour trying to convert a column of currency values to a number using Microsoft Excel 2010. Since when did MS Excel stop understanding what a NUMBER was?

I have a column full of currency values which I want to convert, specifically Philippine pesos to British pounds (sterling). When I multiply the Philippine peso by the conversion rate, it returns #VALUE!

The problem is that I have a column which includes the currency symbol as per the following image:

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman

Related Articles

Joes Revolver Map

Joes Word Cloud

used   date   form   google   creator   uploaded   code   mysql   where   version   data   script   page   files   order   joomla   table   error   following   database   value   time   file   note   list   using   create   find   system   website   source   need   deluge   first   license   report   added   user   function   select   name   same   could   display   work   would   windows   zoho   parameter   server   JoelLipman.Com


Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Donate to Joel Lipman via PayPal

Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2022 Joel Lipman .com. All Rights Reserved.