Business Tools Blog

Question:

Is there a way in Microsoft Excel to insert a decimal place 5 places from the right side (i.e. the last 5 digits are decimals and everything left of that is a whole number) of a series of numbers with different lengths?  I need to figure out how to insert the decimal so I can sum the totals for a check figure.

Yes -

I broke this out into 5 steps and am including a one formula solution in the picture below.

Step 1: Count the characters using the LEN formula and subtract 5.  This will tell you the number of characters that will be shown to the left of the decimal.  =LEN(A3)-5  In the example below, there are a total of 12 characters.  12-5=7

Step 2: Use the LEFT formula to display the 7 characters that will be on the left of the decimal =LEFT(A3,Step 1)

Step 3: Use the RIGHT formula to display the 5 characters that will display to the right of the decimal =RIGHT(A3,5)

Step 4: Concatenate the LEFT and RIGHT results of Step 2 and 3 with a decimal in between =Step2&”.”&Step3

Step 5: Change the concatenated cell to a value using VALUE formula =VALUE(Step4)

Did you ever try to use numbers in an excel spreadsheet to populate a Word document using mail merge?  If you have then you probably noticed that MS Word loads any number in general format.  Number formatting problems in a mail merge occur when the cells that contain the numbers in the Excel worksheet are formatted as Number, Currency, Date, Percentage, etc.

Example, $1,000.00 in Excel will load as 1000 into a mail merge document.

One way to make the number look like currency in your Mail Merge is to create a new column in excel that converts the number $1,000.00 into Text. Use the following formula: =TEXT(A1,”$#,##0.0″)

Before you use the mail merge, hard code the column with the =TEXT(A1,”$#,##0.00″) formula.  When you import the new column into your mail merge document it will appear as currency.

Microsoft Excel - Round numbers in the same cell without a formula

Micorsoft Excel has a neat feature called Precision As Displayed. Using this function automatically rounds all of the numbers in the spreadsheet to equal the number displayed.  You can round without using the “=Round(number,round to number of digits)” formula.

For example, the number in cell C1 is 12.5467 and the number displayed is 13

Turn on Precision as displayed by selecting Tools > Options > Calculations tab, then and checking the Precision as displayed box

Miscrosoft Excel will warn you, that by changing the Data, you will permanently loose accuracy.  If you click “OK”, all of the hard coded numbers in your spreadsheet will be rounded to the number displayed in your spreadsheet.  The formulas will not be changed.

The result, in our example, is that the 12.5467 becomes 13.