Microsoft Office Tips and Tricks (Word, Excel, Visio, PowerPoint)

1. Word

1.1 Convert Texts into a Table:

 

Common Used Excel Formulas in Work

一、数字处理

1、取绝对值
　　=ABS(数字)
　　2、取整
　　=INT(数字)
　　3、四舍五入
　　=ROUND(数字,小数位数)

二、判断公式

1、把公式产生的错误值显示为空
　　公式：C2
　　=IFERROR(A2/B2,"")
　　说明：如果是错误值则显示为空，否则正常显示。

Excel Formula: Search a Column of Strings to Match Another Column

Once a while, I have to work on Excel sheet manually. Today I have to search exported hundreds of ip addresses in a pre-defined excel spreadsheet to see if there is a match. It seems a easy work, but it took me almost an hour to find right formula.

Column O includes all exported ip addresses. I have to manually search if those column O's ip addresses are appearing in column B's text. If yes, which row is it?

Formula is set at Column N.

=MATCH("*"&(O6)&"*",B:B,0)

As you can see from cell N6, the number is 7, which means the text in B7 includes O6's string.
N12's number is 5, which means the test in B5 includes O12's string. 

Excel Formula : Convert a text to Number

Got a Excel file from other resource witch Column A is set as text. At the tail and start of number there are some spaces filled in, for example cell A1 is '  4 '.

My challenge is to convert whole column to numbers which can be used to do sum or other math calculation.

With some research, I constructed this formula for a new column :
=VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))

it perfectly resolved this challenge as you can see from following screen shot.

Note: trim will not work with the only space cell. Thanks reply from Hari Krishna.