Excel - Joel Lipman .Com

MS Excel - Open CSV with Long Numbers

: Joel Lipman; Excel; 23 July 2019; Hits: 3483

What?
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:

copy raw

Account ID,Name,Phone,Record ID
1457102000000135533,Joel,00441234567890,1457102000000165989
1457102000000135566,Stanley,01709123456,1457102000000167543
1457102000000135608,David,00331234567890,1457102000000166795

Account ID,Name,Phone,Record ID
1457102000000135533,Joel,00441234567890,1457102000000165989
1457102000000135566,Stanley,01709123456,1457102000000167543
1457102000000135608,David,00331234567890,1457102000000166795

My Excel:

copy raw

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

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

How?
Firstly, save the CSV file as text file (*.TXT).

Category: Excel :: Article: 689

: Joel Lipman; Excel; 14 February 2019; Hits: 3149

What?
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).

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

: Joel Lipman; Excel; 11 February 2019; Hits: 3750

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

Why?
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).

How?
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.

: Joel Lipman; Excel; 05 October 2015; Hits: 3265

What I have:
A CSV file exported from Excel along with double-quotes

copy raw

label1,label2
item1a,item2a
item1c,"item2c,c"
item1b,item2b

label1,label2
item1a,item2a
item1c,"item2c,c"
item1b,item2b

What I want:
To read the file (stored on the server) and convert to a JavaScript array of objects

copy raw

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' }
];

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' }
];

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

How?

Category: Excel :: Article: 635

: Joel Lipman; Excel; 06 November 2014; Hits: 5409

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

How?
Let's pretend all the names are in column A, in Column B I have the coordinates that I want to convert:

copy raw

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

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

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:

copy raw

FIND(""",B1)   // will NOT work!
FIND("""",B1)  // will work
FIND("″",B1)   // will work

FIND(""",B1) // will NOT work!
FIND("""",B1) // will work
FIND("″",B1) // will work

Category: Excel :: Article: 596

: Joel Lipman; Excel; 06 November 2014; Hits: 3001

What?
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.

How?

Open up a new workbook.
Get into VBA (Press Alt+F11)
Insert a new module (Insert > Module)
Copy and Paste the Excel user defined function below
copyrawstyler
```
Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
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
Press F5 and click “Run”
Get out of VBA (Press Alt+Q)

Category: Excel :: Article: 595

Excel: Find values in one column that are not in another

: Joel Lipman; Excel; 06 March 2014; Hits: 19986

What?
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...

Why?
Consider the 3 following columns in an Excel spreadsheet:

copy raw

Old       New       Exists in Old?
--------- --------- --------------
123456    234567
234567    345678
345678    456789
567890    597890

Old New Exists in Old?
--------- --------- --------------
123456 234567
234567 345678
345678 456789
567890 597890

I want the third column to say whether this is new or not.

How?

Category: Excel :: Article: 551

: Joel Lipman; Excel; 15 November 2013; Hits: 6232

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

Why?
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!

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

: Joel Lipman; Excel; 12 April 2013; Hits: 6389

What?
This is a quick note to myself so that I never use parentheses in the column headings again. Basically I have a pivot table in Microsoft Excel 2010 with the projects down the left (in the first column) and the days of the week along the top.

Why?
The excel report would hit a bug where it couldn't work out that 10 (Wednesday) happened after 8 (Monday).

How?
See the following screenshot and note the dates for Monday, Tuesday, Wednesday, and Thursday:

: Joel Lipman; Excel; 13 February 2013; Hits: 7313

What?
We have an excel spreadsheet which reports against a mySQL database and reads time spent on projects by IT Service colleagues. The main report is a pivot table with staff members along the top, tasks down the first column, and time spent in the form of person days in the cross-join.

Why?
Currently the smallest bookable time by low-level tape monkeys and techies is 30 minutes (Managers it would appear can book whatever time, eg. 5mins). 30 minutes for us translates to 0.07 in person days (a person day being 7 hours and 24 minutes or 26640 seconds).

How?

: Joel Lipman; Excel; 03 July 2012; Hits: 7308

Thought I'd put a quick note here, I tried a fair few solutions that didn't work and then found this hidden away in a forum:

Quick Count

copy raw

=INT(SUMPRODUCT((A3:A1000<>"")/COUNTIF(A3:A1000,A3:A1000&"")))

=INT(SUMPRODUCT((A3:A1000<>"")/COUNTIF(A3:A1000,A3:A1000&"")))

This returns the number of unique values in the range A3 to A1000 and excludes the blank/empty cells.

Display all Unique
Found this note on one of Microsoft Help sites:

Category: Excel :: Article: 418

: Joel Lipman; Excel; 30 September 2011; Hits: 15152

The Situation
I have a Microsoft Excel 2007 file that connects to a SQL Server 2008 R2 database. The Excel file pulls data using lookup tables and displays the data in an Excel Spreadsheet.

The Problem
We can select all cells and set row height to be 30 for example, but everytime we refresh the data in the Excel spreadsheet, all the rows get re-adjusted to fit the data and lose that consistency.

A Workaround: New line inserted before and after
So this is where I am at the moment without VBCode and other suggestions. Instead I add a newline in front of and after the smallest data value (one that I know will never be two lines (or two words)) within the SQL query itself. We have a DEPT column that is an acronym of the departments so for example: