ms-excel: extract decimal and integer part of a number and disable fields changing

excel field protection cells
Sefran2 picture Sefran2 · Sep 10, 2012 · Viewed 42.4k times · Source

I have a numeric field (say A). I have to extract its integer part in another field (say B) and its decimal part in another field (say C). I would that the user can see the values in B and C cells, when changing A cells, but at the same time I don't want that the user can change the values in B and C cells.

Is it possible?

Answer

LittleBobbyTables - Au Revoir picture LittleBobbyTables - Au Revoir · Sep 10, 2012

Let's say you have 10.6 in cell A1. From there, do this:

  • in Cell B1, enter =INT(A1). This will show 10.

  • in Cell C1, enter =A1 - INT(A1) This will show 0.6.

    Alternately, based on your examples, if you want 0.6 to show as 6, or 0.56 to show as 56, use this in cell C1:

    =(A1 - B1) * (10 ^ (LEN(ROUND(A1-B1, 2))-2))

    I ROUND to two decimal places above, because when you can get weird lengths because of floating point calculations (try LEN(8.8-8) for example).

  • Copy the formulas in B1 and C1 as far as you want.

  • Unlock all your cells by selecting them all, right-clicking the form and selecting "Format Cells...", clicking on the Protection tab, and unchecking "Locked".

  • Right-click the range you want to lock (B and c), right-click on them again, go back and select "Lock"
  • Go to Tools > Protection and Protect Worksheet

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

I'm trying to get data from an Excel file on a button click event. My connection string is: string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\source\\SiteCore65\\Individual-Data.xls;Extended Properties=Excel 8.0;"; When I click on the button, …

Read More