Office

Excel / Calc: Convert a Hexadecimal number to Decimal

 in Office  tagged calc / dec / excel / hex / hex2dec / len / office / replace / right / substitute by

The following examples allow you to convert hexadecimal values of the format 0xYYYYYY to decimal using a spreadsheet editor like Calc or Excel.

The following codes will remove the first two characters (the value 0x) of the cell B2 and then convert the result to decimal using the HEX2DEC function.

Using the RIGHT function

In this example, we used the RIGHT function with the num_chars parameter to be equal to the number of characters in the cell minus 2. This used to delete the 0x value from the HEX column by removing the first two characters of the cell.
To get the number of characters in the cell we use the LEN function on the cell of interest.

=HEX2DEC(RIGHT(B2,LEN(B2)-2))

Using the SUBSTITUTE function

In the following example we used the SUBSTITUTE function to automatically find the 0x prefix of the HEX value and delete it by replacing it with an empty string.

=HEX2DEC(SUBSTITUTE(B2,"0x",""))

Using the REPLACE function

The last example uses the REPLACE function. Starting from the character in position 1 in the cell, it replaces the sub-string of size 2 with the empty string and thus deleting the prefix. Please note that this function is not zero-based so the first character is at position 1 and not at position 0.

=HEX2DEC(REPLACE(B2,1,2,""))

Functions Legend:

  • RIGHT(text,[num_chars]) – RIGHT returns the last character or characters in a text string, based on the number of characters you specify in the variable num_chars. RIGHT always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
  • LEN(text) – LEN returns the number of characters in a text string. Again, LEN always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
  • HEX2DEC(number) – HEX2DEC converts a hexadecimal number to decimal.
  • SUBSTITUTE(text, old_text, new_text, [instance_num]) – Substitutes new_text for old_text in a text string. You can use SUBSTITUTE when you want to replace specific text in a text string.
  • REPLACE(old_text, start_num, num_chars, new_text) – REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string. Use REPLACE when you want to replace any text that occurs in a specific location in a text string. REPLACE always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.

How to randomize order of rows in Excel

 in Office  tagged excel / rand / random by

In the following video we demonstrate how to randomize the rows of an Excel sheet.

Methodology:

  • We created a new column next to the data we want to randomize their order, then we typed in the first cell the following formula =rand().
    =rand() will generate a random value between 0 and 1.
  • After that we applied the same formula to the entire column.
  • To apply the formula to the whole column we used a very simple method: we double-clicked on the bottom right hand corner of the cell .

Apply formula to whole column by double clicking on the bottom right corner of the cell

  • Later, we sorted our date using the column of random values.
  • Finally, we deleted the new column.

 

Alternative way to copy the formula to the entire column:

  • Including the cell with the formula, select the cells in the new column that you want the new formula applied to (all the rows you want to be randomized) and the press Ctrl+D.

How to “group by” and sum in Excel

 in Office  tagged excel / group by / Subtotal / sum by

In the following example we used the Subtotal feature to create a spreadsheet that shows the partial sum based on another column.

Methodology:

  1. Click the Data tab in Excel’s ribbon toolbar
  2. Click the Sort button to sort our data by the user column
  3. Click the Subtotal button and fill in the dialog as appropriate, then click OK

In our example, we had only two columns (User and Lot), we wanted to produce the total sum of lots per user and so filled the dialog as follows:

Subtotal

What the above options do is the following:

  • Use the User column to group on by checking when its value changes
  • Use the Sum function on the columns that will be selected later on
  • Apply the function on the Lot column